Database

<br />
SELECT <br />
OBJECT_NAME(c.parent_object_id) ParentTableName,<br />
OBJECT_NAME(c.referenced_object_id) ChildTableName<br />
FROM   <br />
sys.foreign_key_columns c  <br />
INNER JOIN   <br />
sys.columns a ON a.object_id = c.referenced_object_id AND a.column_id = c.referenced_column_id  <br />

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

Maira K wrote:

whereas it was normal before