Script for a table dropping utility.

This a script that I've created to power drop a table. It kills all constraints, (Foreign Keys, Uniqueness, Primary, etc.) attached to the table, then drops said table. This is specific to SQL Server Express, I'm not sure how well it translates to other versions. Much thanks to http://drsql.spaces.live.com/Blog/cns!80677FB08B3162E4!1191.entry for the portion that kills all the relationships.

To Create the Stored Procedure:

GO
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[utility$powerDropTable]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE dbo.utility$powerDropTable
END
GO
create procedure utility$powerDropTable
(
@table_schema sysname = 'dbo', --does not do a like comparison
@parent_table_name sysname = '%', --it is the parent when it is being referred to
@child_table_name sysname = '%', --it is the child table when it is the table referring
--to another
@constraint_name sysname = '%' --can be used to drop only a single constraint
) as
-- ----------------------------------------------------------------
-- Drop all of the foreign key contraints on and or to a table
-- ----------------------------------------------------------------
begin
set nocount on
declare @statements cursor
set @statements = cursor static for
select 'alter table ' + quotename(ctu.table_schema) + '.' + quotename(ctu.table_name) +
' drop constraint ' + quotename(cc.constraint_name)
from information_schema.referential_constraints as cc
join information_schema.constraint_table_usage as ctu
on cc.constraint_catalog = ctu.constraint_catalog
and cc.constraint_schema = ctu.constraint_schema
and cc.constraint_name = ctu.constraint_name
where ctu.table_schema = @table_schema
and ctu.table_name like @child_table_name
and cc.constraint_name like @constraint_name
and exists (select *
from information_schema.constraint_table_usage ctu2
where cc.unique_constraint_catalog = ctu2.constraint_catalog
and cc.unique_constraint_schema = ctu2.constraint_schema
and cc.unique_constraint_name = ctu2.constraint_name
and ctu2.table_schema = @table_schema
and ctu2.table_name like @parent_table_name)
open @statements
declare @statement nvarchar(1000)
While (1=1)
begin
fetch from @statements into @statement
if @@fetch_status <> 0
break
exec (@statement)
end
declare @dropStatement nvarchar(1000)
set @dropStatement = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @parent_table_name + ']'') AND type in (N''U'')) DROP TABLE ' + quotename(@parent_table_name)
exec (@dropStatement)
end

To use said said stored procedure:

exec utility$powerDropTable @parent_table_name = 'YourTableName';

This was created as we have a source control script that rebuilds the entire database for us to use on our local sql server. The script would create the table if it was missing, but didn't clear the table and recreate it. Now it kills it, then recreates it, so column additions, renames, etc get picked up when the script is run.

Switching Modes

Hello All,
(And by that, I mean, hello me, as I'm the only one who has ever read this, lol.) While I will still post the occasional rant, I'm going to dual mode this blog. I'm a programmer, and I'm learning lots of new and useful things. Anything that I have a sever issue with searching out on the web, I'm going to post here.
- The Slow Kid