Sometimes you need to drop a table. Sometimes that table doesn't want to be dropped. It fights you like a two-year-old who was just told no more ice cream after they polished off the entire half gallon. It's ugly.
Usually when this happens to me it's caused by foreign key references, either the table being dropped is referencing another table or another table is referencing the table to be dropped (yes, those are different things). The script below should create a script to drop all foreign keys referencing the table to be dropped as well as all foreign keys in the table to be dropped.
I've only used this a couple of times and it worked like a charm. You should note, however, that those constraints are probably there for a reason so you should seriously consider what you're doing before you arbitrarily drop them.
I got a large part of this from a StackOverflow answer here.
DECLARE @TARGET AS VARCHAR(10); -- SET to 'DROP' or 'CREATE'
DECLARE @TARGET_TABLE AS SYSNAME; -- TABLE WHOSE FOREIGN-KEY WILL BE SCRIPTED
DECLARE @TARGET_SCHEMA AS SYSNAME; -- SCHEMA OF THE TABLE
SET @TARGET = 'DROP';
SET @TARGET_SCHEMA = 'dbo';
SET @TARGET_TABLE = 'TableToDrop';
-- Other variables
DECLARE @schema_name SYSNAME;
DECLARE @table_name SYSNAME;
DECLARE @constraint_name SYSNAME;
DECLARE @constraint_object_id INT;
DECLARE @referenced_object_name SYSNAME;
DECLARE @is_disabled BIT;
DECLARE @is_not_for_replication BIT;
DECLARE @is_not_trusted BIT;
DECLARE @delete_referential_action TINYINT;
DECLARE @update_referential_action TINYINT;
DECLARE @tsql NVARCHAR(4000);
DECLARE @tsql2 NVARCHAR(4000);
DECLARE @fkCol SYSNAME;
DECLARE @pkCol SYSNAME;
DECLARE @col1 BIT;
DECLARE fkcursor CURSOR FOR
(SELECT Object_schema_name(parent_object_id),
Object_name(parent_object_id),
name,
Object_name(referenced_object_id),
object_id,
is_disabled,
is_not_for_replication,
is_not_trusted,
delete_referential_action,
update_referential_action
FROM sys.foreign_keys
WHERE Object_name(parent_object_id) = @TARGET_TABLE
AND Object_schema_name(parent_object_id) = @TARGET_SCHEMA)
UNION ALL
(SELECT Object_schema_name(parent_object_id),
Object_name(parent_object_id),
name,
Object_name(referenced_object_id),
object_id,
is_disabled,
is_not_for_replication,
is_not_trusted,
delete_referential_action,
update_referential_action
FROM sys.foreign_keys
WHERE Object_name(referenced_object_id) = @TARGET_TABLE
AND Object_schema_name(parent_object_id) = @TARGET_SCHEMA)
ORDER BY 1,
2;
OPEN fkcursor;
FETCH next FROM fkcursor INTO @schema_name, @table_name, @constraint_name,
@referenced_object_name, @constraint_object_id, @is_disabled,
@is_not_for_replication, @is_not_trusted, @delete_referential_action,
@update_referential_action;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @TARGET <> 'CREATE'
SET @tsql = 'ALTER TABLE ' + Quotename(@schema_name) + '.'
+ Quotename(@table_name)
+ ' DROP CONSTRAINT '
+ Quotename(@constraint_name) + ';';
ELSE
BEGIN
SET @tsql = 'ALTER TABLE ' + Quotename(@schema_name) + '.'
+ Quotename(@table_name) + CASE @is_not_trusted WHEN 0
THEN
' WITH CHECK ' ELSE ' WITH NOCHECK ' END
+ ' ADD CONSTRAINT '
+ Quotename(@constraint_name)
+ ' FOREIGN KEY ('
SET @tsql2 = '';
DECLARE columncursor CURSOR FOR
SELECT Col_name(fk.parent_object_id, fkc.parent_column_id),
Col_name(fk.referenced_object_id, fkc.referenced_column_id)
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
WHERE fkc.constraint_object_id = @constraint_object_id
ORDER BY fkc.constraint_column_id;
OPEN columncursor;
SET @col1 = 1;
FETCH next FROM columncursor INTO @fkCol, @pkCol;
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( @col1 = 1 )
SET @col1 = 0
ELSE
BEGIN
SET @tsql = @tsql + ',';
SET @tsql2 = @tsql2 + ',';
END;
SET @tsql = @tsql + Quotename(@fkCol);
SET @tsql2 = @tsql2 + Quotename(@pkCol);
FETCH next FROM columncursor INTO @fkCol, @pkCol;
END;
CLOSE columncursor;
DEALLOCATE columncursor;
SET @tsql = @tsql + ' ) REFERENCES '
+ Quotename(@schema_name) + '.'
+ Quotename(@referenced_object_name) + ' ('
+ @tsql2 + ')';
SET @tsql = @tsql + ' ON UPDATE ' + CASE @update_referential_action
WHEN 0
THEN 'NO ACTION '
WHEN 1 THEN 'CASCADE ' WHEN 2 THEN 'SET NULL ' ELSE
'SET DEFAULT '
END + ' ON DELETE ' + CASE @delete_referential_action
WHEN
0
THEN
'NO ACTION ' WHEN 1 THEN 'CASCADE ' WHEN 2 THEN
'SET NULL '
ELSE
'SET DEFAULT ' END + CASE @is_not_for_replication WHEN 1
THEN
' NOT FOR REPLICATION ' ELSE '' END
+ ';';
END;
PRINT @tsql;
IF @TARGET = 'CREATE'
BEGIN
SET @tsql = 'ALTER TABLE ' + Quotename(@schema_name) + '.'
+ Quotename(@table_name) + CASE @is_disabled WHEN 0 THEN
' CHECK '
ELSE ' NOCHECK ' END + 'CONSTRAINT '
+ Quotename(@constraint_name) + ';';
PRINT @tsql;
END;
FETCH next FROM fkcursor INTO @schema_name, @table_name, @constraint_name,
@referenced_object_name, @constraint_object_id, @is_disabled,
@is_not_for_replication, @is_not_trusted, @delete_referential_action,
@update_referential_action;
END;
CLOSE fkcursor;
DEALLOCATE fkcursor;
Resolutions to software engineering problems that I couldn't find anywhere else on the Internet. This is mostly here so I can find all my past solutions in one place.
Monday, April 28, 2014
Tuesday, March 25, 2014
Dynamic Temp Tables in SQL Server
I spent a good deal of time today trying to figure out how to do this so I wanted to make sure to post this as soon as I had it sorted out. Hopefully this helps future-me and present-you.
What do you do if you have a stored procedure that has a very "wide" result set and you want to store the results in a temp table so you can debug/manipulate/have a look at them? There are several options, some of which involve OPENROWSET or OPENQUERY, but those commands both require remote ad-hoc queries to be enabled on the server and didn't really meet my needs today. You can create the temp table before you execute the stored procedure, but that can be cumbersome if the stored procedure returns 350 columns. Below is a different solution where we dynamically create the table using some system tables and cursors.
IMPORTANT NOTE: This solution is not recommended for use in a stored procedure or as any kind of regular processing. I wrote this for the sole purpose of looking at the data that would be returned from a stored procedure so I could sort and manipulate those results for debugging purposes.
-- declare your variables
DECLARE @procedureName VARCHAR(100),
@sql VARCHAR(max),
@text VARCHAR(8000),
@alterSql VARCHAR(8000),
@index INT,
@slice VARCHAR(8000),
@Delimiter CHAR(1)
-- drop the table if it exists
IF Object_id(N'tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
-- create the temp table
CREATE TABLE #temp
(
placeholder CHAR(1)
)
-- initialize the variables
SET @procedureName = 'SampleTable'
SET @sql = ''
SET @Delimiter = ','
SET @index = 1
-- each stored procedure will populate a variable number of rows in syscomments to contain the entire text of
-- the stored procedure, broken into 8,000 character chunks
-- open a cursor for all the syscomments records
DECLARE procedurecursor CURSOR FOR
SELECT sc.[text]
FROM syscomments sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.[name] = @procedureName
OPEN procedurecursor
FETCH next FROM procedurecursor INTO @text
WHILE @@FETCH_STATUS = 0
BEGIN
-- append the text to the larger @sql variable
-- replace all line breaks with a ~! combination (you may want to use a different combination)
SET @sql = @sql
+ Replace(Replace(@text, Char(10), '~!'), Char(13), '~!')
FETCH next FROM procedurecursor INTO @text
END
CLOSE procedurecursor
DEALLOCATE procedurecursor
-- trim the sql script down to remove everything after (and including) the last occurrence of "FROM"
SELECT @sql = Substring(@sql, 1, Len(@sql) - Charindex('MORF', Reverse(@sql))
- 3)
-- replace the ~! with spaces
SELECT @sql = Replace(@sql, '~!', ' ')
-- trim the sql script down to remove everything before (and including) the last occurrence of "SELECT"
SELECT @sql = Substring(@sql, Len(@sql) - Charindex('TCELES', Reverse(@sql)) + 3
, Len(
@sql))
-- replace spaces with nothing (this assumes spaces are not in the column names)
SELECT @sql = Replace(@sql, ' ', '')
-- only proceed if the sql script is populated
IF @sql IS NOT NULL
AND Len(@sql) > 1
BEGIN
-- iterate through the sql script and alter the temp table to add the column
WHILE @index != 0
BEGIN
SET @index = Charindex(@Delimiter, @sql)
IF @index != 0
BEGIN
SET @slice = LEFT(@sql, @index - 1)
END
ELSE
BEGIN
SET @slice = @sql
END
IF( Len(@slice) > 0 )
BEGIN
IF CHARINDEX('.', @slice) > 0
BEGIN
SET @slice = SUBSTRING(@slice, CHARINDEX('.', @slice) + 1, LEN(@slice))
END
SET @alterSql = 'ALTER TABLE #temp ADD ' + @slice
+ ' VARCHAR(8000)'
EXEC (@alterSql)
END
SET @sql = RIGHT(@sql, Len(@sql) - @index)
IF Len(@sql) = 0
BEGIN
BREAK
END
END
END
-- drop the placeholder column from the temp table
ALTER TABLE #temp
DROP COLUMN placeholder
-- execute the stored procedure with whatever parameters you're using to debu
INSERT INTO #temp
EXEC Sampletable_get
@UserID = 12345
-- view the results of the stored procedure call
SELECT *
FROM #temp
Just set your variable names to be what you need and hit Execute. You'll end up with a temp table called #temp populated with the results of the stored procedure. Again, you don't want to use this in a stored procedure or anything, but it can be helpful to debug stuff.
A couple of obvious issues:
What do you do if you have a stored procedure that has a very "wide" result set and you want to store the results in a temp table so you can debug/manipulate/have a look at them? There are several options, some of which involve OPENROWSET or OPENQUERY, but those commands both require remote ad-hoc queries to be enabled on the server and didn't really meet my needs today. You can create the temp table before you execute the stored procedure, but that can be cumbersome if the stored procedure returns 350 columns. Below is a different solution where we dynamically create the table using some system tables and cursors.
IMPORTANT NOTE: This solution is not recommended for use in a stored procedure or as any kind of regular processing. I wrote this for the sole purpose of looking at the data that would be returned from a stored procedure so I could sort and manipulate those results for debugging purposes.
-- declare your variables
DECLARE @procedureName VARCHAR(100),
@sql VARCHAR(max),
@text VARCHAR(8000),
@alterSql VARCHAR(8000),
@index INT,
@slice VARCHAR(8000),
@Delimiter CHAR(1)
-- drop the table if it exists
IF Object_id(N'tempdb..#temp') IS NOT NULL
BEGIN
DROP TABLE #temp
END
-- create the temp table
CREATE TABLE #temp
(
placeholder CHAR(1)
)
-- initialize the variables
SET @procedureName = 'SampleTable'
SET @sql = ''
SET @Delimiter = ','
SET @index = 1
-- each stored procedure will populate a variable number of rows in syscomments to contain the entire text of
-- the stored procedure, broken into 8,000 character chunks
-- open a cursor for all the syscomments records
DECLARE procedurecursor CURSOR FOR
SELECT sc.[text]
FROM syscomments sc
INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.[name] = @procedureName
OPEN procedurecursor
FETCH next FROM procedurecursor INTO @text
WHILE @@FETCH_STATUS = 0
BEGIN
-- append the text to the larger @sql variable
-- replace all line breaks with a ~! combination (you may want to use a different combination)
SET @sql = @sql
+ Replace(Replace(@text, Char(10), '~!'), Char(13), '~!')
FETCH next FROM procedurecursor INTO @text
END
CLOSE procedurecursor
DEALLOCATE procedurecursor
-- trim the sql script down to remove everything after (and including) the last occurrence of "FROM"
SELECT @sql = Substring(@sql, 1, Len(@sql) - Charindex('MORF', Reverse(@sql))
- 3)
-- replace the ~! with spaces
SELECT @sql = Replace(@sql, '~!', ' ')
-- trim the sql script down to remove everything before (and including) the last occurrence of "SELECT"
SELECT @sql = Substring(@sql, Len(@sql) - Charindex('TCELES', Reverse(@sql)) + 3
, Len(
@sql))
-- replace spaces with nothing (this assumes spaces are not in the column names)
SELECT @sql = Replace(@sql, ' ', '')
-- only proceed if the sql script is populated
IF @sql IS NOT NULL
AND Len(@sql) > 1
BEGIN
-- iterate through the sql script and alter the temp table to add the column
WHILE @index != 0
BEGIN
SET @index = Charindex(@Delimiter, @sql)
IF @index != 0
BEGIN
SET @slice = LEFT(@sql, @index - 1)
END
ELSE
BEGIN
SET @slice = @sql
END
IF( Len(@slice) > 0 )
BEGIN
IF CHARINDEX('.', @slice) > 0
BEGIN
SET @slice = SUBSTRING(@slice, CHARINDEX('.', @slice) + 1, LEN(@slice))
END
SET @alterSql = 'ALTER TABLE #temp ADD ' + @slice
+ ' VARCHAR(8000)'
EXEC (@alterSql)
END
SET @sql = RIGHT(@sql, Len(@sql) - @index)
IF Len(@sql) = 0
BEGIN
BREAK
END
END
END
-- drop the placeholder column from the temp table
ALTER TABLE #temp
DROP COLUMN placeholder
-- execute the stored procedure with whatever parameters you're using to debu
INSERT INTO #temp
EXEC Sampletable_get
@UserID = 12345
-- view the results of the stored procedure call
SELECT *
FROM #temp
Just set your variable names to be what you need and hit Execute. You'll end up with a temp table called #temp populated with the results of the stored procedure. Again, you don't want to use this in a stored procedure or anything, but it can be helpful to debug stuff.
A couple of obvious issues:
- This hasn't really been tested other than the couple of times I used it so it's possible it won't work for you "out of the box" so be prepared to tweak it
- All of the columns in the temp table will be VARCHAR(8000) so you won't be able to perform certain functions (such as ORDER BY) without converting the data
- I know that's a bummer, but I couldn't come up with a way around it
Quickie Links
Here are some helpful links I use to do/find various things while I'm coding and/or blogging:
Format SQL to post it in the blog: http://www.dpriver.com/pp/sqlformat.htm
Format javascript to post it in the blog (until I find something better): http://formatmysourcecode.blogspot.com/
Format C# to post it in the blog: http://www.manoli.net/csharpformat/
Format SQL to post it in the blog: http://www.dpriver.com/pp/sqlformat.htm
Format javascript to post it in the blog (until I find something better): http://formatmysourcecode.blogspot.com/
Format C# to post it in the blog: http://www.manoli.net/csharpformat/
Thursday, March 20, 2014
SQL Tips and Tricks - Part 2
Today has been a flurry of posts. I've had a ton on my mind and no time to put it up here so I'm kinda making up for lost time. Going forward I think I'm going to try to stick to small posts, but do them very often.
So for this installment of SQL tips and tricks, I have one of my favorites. It seems like I can never find a column in a table when I need it. I know it's there, and I remember that maybe it starts with an "h" or a "c", but that's as far as I can get. It gets worse when somebody misspelled the column name in the first place.
Well, I came up with a solution for this "problem" of mine.
So for this installment of SQL tips and tricks, I have one of my favorites. It seems like I can never find a column in a table when I need it. I know it's there, and I remember that maybe it starts with an "h" or a "c", but that's as far as I can get. It gets worse when somebody misspelled the column name in the first place.
Well, I came up with a solution for this "problem" of mine.
DECLARE @columnName VARCHAR(100), @sql VARCHAR(8000), @tableName VARCHAR(100)
SET @sql = 'SELECT '
SET @tableName = 'SampleTable'
DECLARE columnCursor CURSOR FOR
SELECT sc.[name]
FROM sys.tables st WITH (NOLOCK)
INNER JOIN sys.columns sc WITH (NOLOCK) ON st.object_id = sc.object_id
WHERE st.[name] = @tableName
ORDER BY sc.[name]
OPEN columnCursor
FETCH NEXT FROM columnCursor INTO @columnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + @columnName + ', '
FETCH NEXT FROM columnCursor INTO @columnName
END
CLOSE columnCursor
DEALLOCATE columnCursor
SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 1)
SET @sql = @sql + ' FROM ' + @tableName
SELECT @sql
POSTing Multiple Parameters with Web API
So you've decided to use Web API to build some RESTful services (or at least some RESTful methods) for your application and you now realize that when you POST to those services you have a need to send two parameters. For the sake of argument, let's say you want to send first name and last name to a method called SaveName, like this:
Yeah, good luck with that. See, right out of the box Web API doesn't allow this. I don't remember what happens, but it doesn't work the way you think it will. Rick Strahl wrote a really in-depth and quite good post about this some time ago and since I can't possibly say it any better than him, I'm going to direct you there.
While you're there you should also check out these two other posts about the exact same topic. Seriously, that guy knows what he's talking about.
1: [HttpPost]
2: public HttpResponseMessage SaveName(string firstName, string lastName)
3: {
4:
5: }
Yeah, good luck with that. See, right out of the box Web API doesn't allow this. I don't remember what happens, but it doesn't work the way you think it will. Rick Strahl wrote a really in-depth and quite good post about this some time ago and since I can't possibly say it any better than him, I'm going to direct you there.
While you're there you should also check out these two other posts about the exact same topic. Seriously, that guy knows what he's talking about.
Output Caching with Web API
One of the nice features of Microsoft's MVC template was the ability to just tag a method with the OutputCache tag, specify a few properties and be done with it. This would cache the response (providing a 304 HTTP status code instead of a 200) for a given period of time (among other factors).
Web API is the new RESTful project type that is available in VS 2012 (you actually create an MVC 4 project and have the ability to create controllers that inherit from ApiController instead of the plain Controller). Unfortunately, Web API doesn't have the option to use the OutputCache tag. You can technically put it on a method, but it won't actually do anything. Fortunately, fortune smiles upon us in the form of Filip W.'s CacheOutput package available from Nuget. You can check out Filip's blog post about it here and you can find it in Nuget by searching for Strathweb. I stuck with the 1.0 version as that was more suitable with the project I was working on, but I trust that version 2 would work just as well.
Utilizing the output cache, especially with large result sets, database-intensive queries, and in the RESTful architecture, can greatly improve the performance of your application. Just as an example, we took a response consisting of 37,000+ rows of somewhat complex data in JSON format down from 1500 milliseconds to 10 milliseconds. Yes, 1500 down to 10. That powerful.
Web API is the new RESTful project type that is available in VS 2012 (you actually create an MVC 4 project and have the ability to create controllers that inherit from ApiController instead of the plain Controller). Unfortunately, Web API doesn't have the option to use the OutputCache tag. You can technically put it on a method, but it won't actually do anything. Fortunately, fortune smiles upon us in the form of Filip W.'s CacheOutput package available from Nuget. You can check out Filip's blog post about it here and you can find it in Nuget by searching for Strathweb. I stuck with the 1.0 version as that was more suitable with the project I was working on, but I trust that version 2 would work just as well.
Utilizing the output cache, especially with large result sets, database-intensive queries, and in the RESTful architecture, can greatly improve the performance of your application. Just as an example, we took a response consisting of 37,000+ rows of somewhat complex data in JSON format down from 1500 milliseconds to 10 milliseconds. Yes, 1500 down to 10. That powerful.
Subscribe to:
Posts (Atom)