Monday, May 17, 2010

Drop All Schemas in a SQL Server Database

This post extends my previous post Drop All Objects in a SQL Server Database and drops all non-system related Schemas in a database.
/***** BEGIN - DROP SCHEMAs *****/
DECLARE @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), ObjectName VARCHAR(128));

INSERT INTO @t(ObjectName)
SELECT s.[SCHEMA_NAME] FROM INFORMATION_SCHEMA.SCHEMATA s
WHERE s.[SCHEMA_NAME] NOT IN('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter')

SELECT @Rows = (SELECT COUNT(RowID) FROM @t), @i = 1;

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'DROP SCHEMA [' + t.ObjectName + '];', @name = t.ObjectName FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped SCHEMA: [' + @name + ']';    
    SET @i = @i + 1;
END
GO
/***** END - DROP SCHEMAs *****/

No comments: