Dropping all Tables

Broken Bokken

I am working on a database create script for SQL server 2005. It's getting to be pretty large (20 tables so far) and the gist of the script is to dropall the tables, create the tables, and then add in any default data. I have the last two pieces working correctly, and previously I was dropping each table individually. I found some code to help drop all tables and modified it.

DECLARE @Count int

--############################################################################################
-- Drop Entities
--############################################################################################

SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'

WHILE @Count > 0
BEGIN
EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";

	SELECT @Count = COUNT(\*) FROM sys.Tables WHERE name NOT LIKE 'sys%'

END

IF @Count = 0
BEGIN
SET @Message = '--- Tables dropped successfully ---'
END
ELSE
BEGIN
SET @Message = '--- Error dropping tables ---'
END

PRINT @Message

The problem is that because I have foreign keys, the scrip throws errors. I tried a try-catch but then the query hangs. Ideally, I want the while loop to ignore errors, or at least not display them so that any errors displayed are errors with the create script. I do not want the exec within the while to rollback, which is what I believe the try-catch is doing. Any ideas how to do this?

Broken Bokken You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod http://www.brokenbokken.com

Harini N K

Try the following SQL statement: First drop all the child tables and then go for parent tables: USE NORTHWIND SELECT OBJECT_NAME(c.parent_object_id) ParentTableName, OBJECT_NAME(c.referenced_object_id) ChildTableName FROM sys.foreign_key_columns c INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id AND a.column_id = c.referenced_column_id Hope this helps!!

Harini

Broken Bokken

I used your solution, however, you must drop the parent tables first. The Child tables are the ones that error when you try to drop them. Here is the code in case anyone else wants to know how to do this. Just change {DatabaseName} to the name of the database you want to drop all the tables in.

USE {DatabaseName}
SET NOCOUNT ON

DECLARE @Message varchar (1000)
DECLARE @TableName varchar (100)
DECLARE @Count int

--############################################################################################
-- Drop Entities
--############################################################################################

---############### drop related entities first ###############---
SELECT @Count = Count(c.parent_object_id)
FROM sys.foreign_key_columns c
INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id
AND a.column_id = c.referenced_column_id

---loop for tertiary or greater relationships
WHILE @Count > 0
BEGIN

DECLARE tableCursor cursor FOR 
	SELECT OBJECT\_NAME(c.parent\_object\_id) 
	FROM sys.foreign\_key\_columns c
	INNER JOIN sys.columns a ON a.object\_id = c.referenced\_object\_id 
	AND a.column\_id = c.referenced\_column\_id

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @TableName

WHILE @@FETCH\_STATUS = 0
BEGIN
	BEGIN TRY
		EXEC ('DROP TABLE ' + @TableName)
		PRINT '--- Dropped Table ' + @TableName + ' ---'
	END TRY
	BEGIN CATCH
	--eat any errors
	END CATCH

	FETCH NEXT FROM tableCursor INTO @TableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

SELECT @Count = Count(c.parent_object_id)
FROM sys.foreign_key_columns c
INNER JOIN sys.columns a ON a.object_id = c.referenced_object_id
AND a.column_id = c.referenced_column_id
END

---############### drop the rest of the tables once the relationships have been dropped ###############---
SELECT @Count = COUNT(*) FROM sys.Tables WHERE name NOT LIKE 'sys%'

WHILE @Count > 0
BEGIN
--only parents left, so drop using msforeachtable
EXEC sp_MSforeachtable "DROP TABLE ? PRINT '--- Dropped Table ? ---' ";

	SELECT @Count = COUNT(\*) FROM sys.Tables WHERE name NOT LIKE 'sys%'

END

IF @Count = 0
BEGIN
SET @Message = '--- Tables dropped successfully ---'
END
ELSE
BEGIN
SET @Message = '--- Error dropping tables ---'
END

PRINT @Message
GO

Broken Bokken You can't carry out a ninja-style assasination dressed as an astronaut. It's the luminous fabric; too visible. - Tripod http://ww