/***** 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 *****/
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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment