The amazing adventures of Doug Hughes

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

Comments on: "Delete Almost Everything From Your MSSQL Database" (7)

  1. 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.

    Like

  2. 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.

    Like

  3. @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. πŸ™‚

    Like

  4. 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

    Like

  5. David Epler said:

    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

    Like

  6. is there a typo at “WHEN so.type = ‘F’ then 3”? Shouldn’t this be ‘FN’ instead of ‘F’?

    Scott

    Like

  7. 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

    Like

Comments are closed.

Tag Cloud

%d bloggers like this: