I want to find a sql command or something that can do this where I have a table named tblFoo and I want to name it tblFooBar. However, I want the primary key to also be change, for example, currently it is: CONSTRAINT [PK_tblFoo] PRIMARY KEY CLUSTERED And I want a name change to change it to: CONSTRAINT [PK_tblFooBar] PRIMARY KEY CLU...
I am selecting from a table that has an XML column using T-SQL. I would like to select a certain type of node and have a row created for each one. For instance, suppose I am selecting from a people table. This table has an XML column for addresses. The XML is formated similar to the following: <address> <street>Street 1</street> <c...
Is there any regular expression library written in T-SQL (no CLR, no extended sp, pure t-sql) for SQL Server? (should work with shared hosting) Edit: thanks I know about PATINDEX, LIKE, xp_ sps and CLR solutions I also know it is not the best place for regex, the question is theoretical:) reduced functionality is also accepted ...
Say you have a table Names that has three rows: Peter Paul Mary Is there an easy way to turn this into a single string of "Peter, Paul, Mary"? ...
I have a table with two fields of interest for this particular exercise: a CHAR(3) ID and a DATETIME. The ID identifies the submitter of the data - several thousand rows. The DATETIME is not necessarily unique, either. (The primary keys are other fields of the table.) Data for this table is submitted every six months. In December, w...
I'm trying to select data from a table defined similar to the following : Column | Data Type ------------------------- Id | Int DataType | Int LoggedData | XML but I only want to select those rows with a specific DataType value, and that contain a string (or evaluates a piece of XPath) in the LoggedData colum...
We're working with a fixed transaction log size on our databases, and I'd like to put together an application to monitor the log sizes so we can see when things are getting too tight and we need to grow the fixed trn log. Is there any TSQL command that I can run which will tell me the current size of the transaction log, and the fixed ...
What is the equivalent of the extended procedure 'xp_dirscan' in SQL Server 2005? ...
If a table has a rowversion column in it and you make an update to that row, the value of the rowversion column increases. I know that this is by design and the purpose of rowversion columns, however is there any way to make it such that an update operation will have no effect on rowversion value? Edit: Tracking of updates on all co...
My SQL is a bit rusty -- is there a SQL way to project an input table that looks something like this: Name SlotValue Slots ---- --------- ----- ABC 3 1 ABC 4 2 ABC 6 5 I...
I've created a stored procedure similar to the one below (I'm using this cut down version to try and figure our the problem). CREATE PROCEDURE bsp_testStoredProc AS BEGIN CREATE TABLE #tmpFiles ( AuthorName NVARCHAR(50), PercentageHigh INT ) -- Insert data into temp table SELECT AuthorName, PercentageHigh FROM #tmpFiles ORDER ...
Can someone tell me what the statuses mean in SQL Server's sp_who command? Why might a spid be suspended? What does it mean to be "runnable"? Thanks! ...
I want to convert a string like this: '10/15/2008 10:06:32 PM' into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM') This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. Th...
I need to make DISTRIBUTED TRANSACTION between two MS SQL Server 2005, it drops an error that NO ACTIVE TRANSACTION IS PRESENT or smth like this. DTCTESTER programs drops an error that transaction could not be done. btw 135 port is open on the firewall. Maybe anyone knows what a problem, or have a suggestion? ...
There is a store procedure that uses FREETEXTTABLE twice on two tables and then merges the results and returns the top 50. The problem is if I do a search on "Women of Brewster", the results returns "Confession of an ex doofus motha" with a rank of 143 from table A and second "Women of Brewster Place" with a rank of 102 from table B. ...
I want to check in Transact SQL if a specific column in a table has statistics and if so to get them all. ...
A while ago I had a query that I ran quite a lot for one of my users. It was still being evolved and tweaked but eventually it stablised and ran quite quickly, so we created a stored procedure from it. So far, so normal. The stored procedure, though, was dog slow. No material difference between the query and the proc, but the speed c...
Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this SELECT * FROM a_table WHERE a_field LIKE '%а_pattern%' until I change pattern to N'%а_pattern%'. I never had to add this prefix in the past, so I am curio...
I was asked for a comprehensive breakdown on space used within a specific database. I know I can use *sys.dm_db_partition_stats* in SQL Server 2005 to figure out how much space each table in a database is using, but is there any way to determine the individual and total size of the stored procedures in a database? (Short of opening each...
I'm looking to add some lookup lists in the database, but I want them to be easy localizable (SQL 2005, ADO.NET) This would include: Easy Management of multiple languages at the same time Easy Retrieval of values from the database Fallback language (in case the selected language is missing) I was thinking about having a table that w...