Ok, this script is dangerous. Use it only when you intend to. If you delete stuff you don’t want to, it’s your problem!
Anyhow, now that we’re past the disclaimer, I’m posting this blog entry as a follow up to one Layne Vincent posted earlier this week on how to truncate most of your tables. As it were, we are currently working on a project where pretty much everything related to the database is scripted. We have a script we generate that will completely rebuild our database including all of it’s tables, stored procedures, functions, basic lookup data etc.
The problem is, although the script has a chunk of code at the beginning to drop tables, the script takes no account of foreign key constraints and these drops simply don’t work. Besides, we also have a lot more than just tables we may want to drop. So, we were talking about this problem the other day and it got me thinking about a way to drop virtually everything from your database without actually dropping and recreating your database. (Why? Well, what if you don’t have rights to drop the database or create a new database?)
What I came up with was a simple script that will select most types of objects from the sys.objects view into a cursor. I then loop over the cursor and call the correct drop statement to drop the object. The query is sorted so that I can first drop foreign keys, then tables, then other objects. Without further ado, here’s the script:
/*** drop (pretty much) everything before rebuilding the database ***/
DECLARE
OBJECTS CURSOR FOR SELECT
so.name,
so.type,
so.type_desc,
p.name AS parentName
FROM
sys.objects AS so
LEFT JOIN sys.objects AS p ON so.parent_object_id = p.object_id
WHERE
so.schema_id = 1
ORDER BY
CASE
WHEN so.type = 'F' THEN
0
WHEN so.type = 'TR' THEN
1
WHEN so.type = 'U' THEN
2
WHEN so.type = 'F' THEN
3
ELSE
4
END OPEN OBJECTS DECLARE
@name AS nvarchar (MAX) DECLARE
@type AS nvarchar (2) DECLARE
@type_desc AS nvarchar DECLARE
@parentName AS nvarchar (MAX) DECLARE
@statement AS nvarchar (MAX) FETCH NEXT
FROM
OBJECTS INTO @name,
@type,
@type_desc,
@parentName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = ' IF(@type = ' F ')
BEGIN
PRINT ' DROPING FK : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' ALTER TABLE ' + @parentName + ' DROP CONSTRAINT ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' TR ')
BEGIN
PRINT ' DROPING TRIGGER : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP TRIGGER ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' U ')
BEGIN
PRINT ' DROPING TABLE : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP TABLE ' + @name
EXECUTE(@statement)
END
ELSE IF (@type = ' FN ')
BEGIN
PRINT ' DROPING FUNCTION : ' + @name + ' OF type ' + @type + ' (' + @type_desc + ') '
SET @statement = ' DROP FUNCTION ' + @name
EXECUTE(@statement)
END
ELSE
PRINT ' Didn 't drop object ' + @name + ' of type ' + @type + ' (' + @type_desc + ')' FETCH NEXT
FROM
OBJECTS INTO @name,
@type,
@type_desc,
@parentName
END CLOSE OBJECTS DEALLOCATE OBJECTS
DougHughes.net 
Comments on: "Delete Almost Everything From Your MSSQL Database" (7)
That’s interesting… I’ve never had any problems getting tables to drop in SQL Server with foreign keys on them. My experience has been that a drop will drop the foreign key constraint as well. Though I always start by dropping procedures and views before dropping tables. And I generally will specify an order for the tables so that I drop those with the least number of dependencies first. Although I do generally execute my drops via an XML declaration.
If I needed an automated way to do it I think my first instinct would be to go to my JDBC metadata tools (or CFDBInfo) first rather than sysobjects, but that’s probably habit merely because I don’t standardize on SQL Server. Though I’ve worked for companies that do, where a sysobjects or information_schema based solution made perfect sense. When I worked for SiteManageware in Ft Lauderdale a few years ago actually I was in charge of managing migration scripts (stored procedures) for synching datasource changes from our dev db up to our customer databases and since it was all SQL Server, information_schema was really the tool of choice for those. We never dropped anything tho.
LikeLike
Yeah, I just don’t recall ever having that problem with dropping tables. Maybe I’m misremembering because the DataFaucet tool does it automatically and I haven’t dropped a table in a long time. I’d have to go back and double-check the code for drops to see what they do.
LikeLike
@Brad – using the fk metadata is actually how the cascade deletes in DataFaucet work. Just thought it was interesting because you said “kind of like cascading deletes”, but that’s actually the method my tool uses. 🙂
LikeLike
Hey thanks for putting this up… not sure if anyone else does the same sort of stuff as I do, but when I’m testing database modifications and replication I will duplicate a DB but will sometimes want to be able to trash specific types of objects. It’s handy to allow people to do this in development environments as well where they don’t have perms to create a new DB, but can do whatever they want in that DB. So anyway… I took what you had and just added a section at the top that would allow you to quickly control what types of objects you were looking to drop.
If you set the remove value in the temp table to 1 then it will get dropped in the order listed. I saw was folks were saying on here about the constraints, but I figure doing it this way with an order that won’t allow for conflicts should be just as failsafe. I think. 🙂 So anyways thanks. Maybe my small changes will help someone else too. (I’m not sure if this will fit in the comment size, or if it will look OK even if it does fit. 😉 )
SET NOCOUNT ON
DECLARE @ObjectTypes TABLE
(
vType char(2)
,vDescription varchar(50)
,iDropOrder integer
,Remove bit
)
–Items you may want to remove
INSERT @ObjectTypes VALUES (‘C’ ,’CHECK constraint’ ,1 ,1)
INSERT @ObjectTypes VALUES (‘D’ ,’Default or DEFAULT constraint’ ,2 ,1)
INSERT @ObjectTypes VALUES (‘F’ ,’FOREIGN KEY constraint’ ,3 ,1)
INSERT @ObjectTypes VALUES (‘K’ ,’PRIMARY KEY or UNIQUE constraint’ ,4 ,1)
INSERT @ObjectTypes VALUES (‘FN’,’Scalar function’ ,5 ,1)
INSERT @ObjectTypes VALUES (‘IF’,’Inlined table-function’ ,6 ,1)
INSERT @ObjectTypes VALUES (‘P’ ,’Stored procedure’ ,7 ,1)
INSERT @ObjectTypes VALUES (‘R’ ,’Rule’ ,8 ,1) –These are old probably not used…
INSERT @ObjectTypes VALUES (‘RF’,’Replication filter stored procedure’,9 ,1)
INSERT @ObjectTypes VALUES (‘TF’,’Table function’ ,10,1)
INSERT @ObjectTypes VALUES (‘TR’,’Trigger’ ,11,1)
INSERT @ObjectTypes VALUES (‘V’ ,’View’ ,12,1)
INSERT @ObjectTypes VALUES (‘U’ ,’User table’ ,13,1)
–Items you should NEVER want to remove
INSERT @ObjectTypes VALUES (‘L’ ,’Log’ ,14,0)
INSERT @ObjectTypes VALUES (‘X’ ,’Extended stored procedure’ ,15,0)
INSERT @ObjectTypes VALUES (‘S’ ,’System table’ ,16,0)
SET NOCOUNT OFF
/*** drop (pretty much) everything before rebuilding the database ***/
DECLARE OBJECTS CURSOR FOR
SELECT
Name,Type,vDescription,(select name from sysobjects where a.parent_obj = id)
FROM
SysObjects a
JOIN
@ObjectTypes
ON
Type=vType
and
Remove=1
ORDER BY
iDropOrder ASC
OPEN OBJECTS
DECLARE @name as nvarchar(max)
DECLARE @type as nvarchar(2)
DECLARE @type_desc as nvarchar
DECLARE @parentName as nvarchar(max)
DECLARE @statement as nvarchar(max)
FETCH NEXT FROM OBJECTS INTO @name, @type, @type_desc, @parentName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = ‘
IF(@type = ‘F’) or (@type = ‘C’) or (@type = ‘D’) or (@type=’F’) or (@type=’K’)
BEGIN
PRINT ‘DROPPING FK: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘ALTER TABLE ‘ + @parentName + ‘ DROP CONSTRAINT ‘ + @name
EXECUTE(@statement)
END
ELSE IF (@type = ‘TR’)
BEGIN
PRINT ‘DROPPING TRIGGER: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP TRIGGER ‘ + @name
EXECUTE(@statement)
END
ELSE IF (@type = ‘U’)
BEGIN
PRINT ‘DROPPING TABLE: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP TABLE ‘ + @name
EXECUTE(@statement)
END
ELSE IF (@type = ‘FN’) or (@type=’IF’) or (@type=’TF’)
BEGIN
PRINT ‘DROPPING FUNCTION: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP FUNCTION ‘ + @name
EXECUTE(@statement)
END
ELSE IF (@type = ‘P’) or (@type=’RF’)
BEGIN
PRINT ‘DROPPING Stored Procedure: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP PROCEDURE ‘ + @name
EXECUTE(@statement)
END
ELSE IF (@type = ‘V’)
BEGIN
PRINT ‘DROPPING View: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP VIEW ‘ + @name
EXECUTE(@statement)
END
ELSE
PRINT ‘Didn’t drop object ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
FETCH NEXT FROM OBJECTS INTO @name, @type, @type_desc, @parentName
END
CLOSE OBJECTS
DEALLOCATE OBJECTS
LikeLike
This is extremely useful. I used the extended version that Blain posted, but had to modify it to deal with schema names that I was using in the SQL Server 2005 database I was working on.
Here are the the changes (everything above the comment is the same):
/*** drop (pretty much) everything before rebuilding the database ***/
DECLARE OBJECTS CURSOR FOR
SELECT
Name,(select name from sys.schemas where a.uid=schema_id) as schema_name,Type,vDescription,(select name from sysobjects where a.parent_obj = id)
FROM
SysObjects a
JOIN
@ObjectTypes
ON
Type=vType
and
Remove=1
ORDER BY
iDropOrder ASC
OPEN OBJECTS
DECLARE @name as nvarchar(max)
DECLARE @schema_name as nvarchar(max)
DECLARE @type as nvarchar(2)
DECLARE @type_desc as nvarchar
DECLARE @parentName as nvarchar(max)
DECLARE @statement as nvarchar(max)
FETCH NEXT FROM OBJECTS INTO @name, @schema_name, @type, @type_desc, @parentName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = ‘
IF(@type = ‘F’) or (@type = ‘C’) or (@type = ‘D’) or (@type=’F’) or (@type=’K’)
BEGIN
PRINT ‘Dropping FK: ‘ + @name + ‘] of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’ + ‘ from [‘ + @schema_name + ‘].[‘ + @parentName + ‘]’
SET @statement = ‘ALTER TABLE [‘ + @schema_name + ‘].[‘ + @parentName + ‘] DROP CONSTRAINT ‘ + @name
EXECUTE(@statement)
END
ELSE IF (@type = ‘TR’)
BEGIN
PRINT ‘Dropping Trigger: [‘ + @schema_name + ‘].[‘ + @name + ‘] of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP TRIGGER [‘ + @schema_name + ‘].[‘+ @name + ‘]’
EXECUTE(@statement)
END
ELSE IF (@type = ‘U’)
BEGIN
PRINT ‘Dropping Table: [‘ + @schema_name + ‘].[‘ + @name + ‘] of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP TABLE [‘ + @schema_name + ‘].[‘ + @name + ‘]’
EXECUTE(@statement)
END
ELSE IF (@type = ‘FN’) or (@type=’IF’) or (@type=’TF’)
BEGIN
PRINT ‘Dropping Function: [‘ + @schema_name + ‘].[‘ + @name + ‘] of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP FUNCTION [‘ + @schema_name + ‘].[‘ + @name + ‘]’
EXECUTE(@statement)
END
ELSE IF (@type = ‘P’) or (@type=’RF’)
BEGIN
PRINT ‘Dropping Stored Procedure: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP PROCEDURE ‘ + @name
EXECUTE(@statement)
END
ELSE IF (@type = ‘V’)
BEGIN
PRINT ‘Dropping View: [‘ + @schema_name + ‘].[‘ + @name + ‘] of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP VIEW [‘ + @schema_name + ‘].[‘ + @name + ‘]’
EXECUTE(@statement)
END
ELSE
PRINT ‘Didn’t drop object ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘) in schema ‘ + @schema_name
FETCH NEXT FROM OBJECTS INTO @name, @schema_name, @type, @type_desc, @parentName
END
CLOSE OBJECTS
DEALLOCATE OBJECTS
LikeLike
is there a typo at “WHEN so.type = ‘F’ then 3”? Shouldn’t this be ‘FN’ instead of ‘F’?
Scott
LikeLike
Thanks really appreciate.
added escaping for names ‘[” @name ‘]’
SET NOCOUNT ON
DECLARE @ObjectTypes TABLE
(
vType char(2)
,vDescription varchar(50)
,iDropOrder integer
,Remove bit
)
INSERT @ObjectTypes VALUES (‘C’ ,’CHECK constraint’ ,1 ,1)
INSERT @ObjectTypes VALUES (‘D’ ,’Default or DEFAULT constraint’ ,2 ,1)
INSERT @ObjectTypes VALUES (‘F’ ,’FOREIGN KEY constraint’ ,3 ,1)
INSERT @ObjectTypes VALUES (‘K’ ,’PRIMARY KEY or UNIQUE constraint’ ,4 ,1)
INSERT @ObjectTypes VALUES (‘FN’,’Scalar function’ ,5 ,1)
INSERT @ObjectTypes VALUES (‘IF’,’Inlined table-function’ ,6 ,1)
INSERT @ObjectTypes VALUES (‘P’ ,’Stored procedure’ ,7 ,1)
INSERT @ObjectTypes VALUES (‘R’ ,’Rule’ ,8 ,1)
INSERT @ObjectTypes VALUES (‘RF’,’Replication filter stored procedure’,9 ,1)
INSERT @ObjectTypes VALUES (‘TF’,’Table function’ ,10,1)
INSERT @ObjectTypes VALUES (‘TR’,’Trigger’ ,11,1)
INSERT @ObjectTypes VALUES (‘V’ ,’View’ ,12,1)
INSERT @ObjectTypes VALUES (‘U’ ,’User table’ ,13,1)
INSERT @ObjectTypes VALUES (‘L’ ,’Log’ ,14,0)
INSERT @ObjectTypes VALUES (‘X’ ,’Extended stored procedure’ ,15,0)
INSERT @ObjectTypes VALUES (‘S’ ,’System table’ ,16,0)
SET NOCOUNT OFF
/*** drop (pretty much) everything before rebuilding the database ***/
DECLARE OBJECTS CURSOR FOR
SELECT
Name,Type,vDescription,(select name from sysobjects where a.parent_obj = id)
FROM
SysObjects a
JOIN
@ObjectTypes
ON
Type=vType
and
Remove=1
ORDER BY
iDropOrder ASC
OPEN OBJECTS
DECLARE @name as nvarchar(max)
DECLARE @type as nvarchar(2)
DECLARE @type_desc as nvarchar
DECLARE @parentName as nvarchar(max)
DECLARE @statement as nvarchar(max)
FETCH NEXT FROM OBJECTS INTO @name, @type, @type_desc, @parentName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @statement = ”
IF(@type = ‘F’) or (@type = ‘C’) or (@type = ‘D’) or (@type=’F’) or (@type=’K’)
BEGIN
PRINT ‘DROPPING FK: ‘ + ‘[‘ + @name + ‘]’ + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘ALTER TABLE ‘ + ‘ [‘ + @parentName + ‘]’ + ‘ DROP CONSTRAINT ‘ + ‘ [‘ + @name + ‘] ‘
PRINT @statement
EXECUTE(@statement)
END
ELSE IF (@type = ‘TR’)
BEGIN
PRINT ‘DROPPING TRIGGER: ‘ + ‘[‘ + @name + ‘]’ +’ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP TRIGGER ‘ + ‘[‘ + @name + ‘]’
PRINT @statement
EXECUTE(@statement)
END
ELSE IF (@type = ‘U’)
BEGIN
PRINT ‘DROPPING TABLE: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP TABLE ‘ + ‘[‘ + @name + ‘]’
PRINT @statement
EXECUTE(@statement)
END
ELSE IF (@type = ‘FN’) or (@type=’IF’) or (@type=’TF’)
BEGIN
PRINT ‘DROPPING FUNCTION: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP FUNCTION ‘ + ‘[‘ + @name + ‘]’
PRINT @statement
EXECUTE(@statement)
END
ELSE IF (@type = ‘P’) or (@type=’RF’)
BEGIN
PRINT ‘DROPPING Stored Procedure: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP PROCEDURE ‘ + ‘[‘ + @name + ‘]’
PRINT @statement
EXECUTE(@statement)
END
ELSE IF (@type = ‘V’)
BEGIN
PRINT ‘DROPPING View: ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
SET @statement = ‘DROP VIEW ‘ + ‘[‘ + @name + ‘]’
PRINT @statement
EXECUTE(@statement)
END
ELSE
PRINT ‘Didnt drop object ‘ + @name + ‘ of type ‘ + @type + ‘ (‘ + @type_desc + ‘)’
FETCH NEXT FROM OBJECTS INTO @name, @type, @type_desc, @parentName
END
CLOSE OBJECTS
DEALLOCATE OBJECTS
LikeLike