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 *****/

Drop All Objects in a SQL Server Database

The following SQL script is similar to Paige Cook's Drop All Objects in a SQL Server Database; however, is if fully INFORMATION_SCHEMA based and supports Schema-based objects (e.g. SchemaName.TableName).

/***** BEGIN - DROP non-sys PROCs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName)
SELECT r.ROUTINE_SCHEMA, r.SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE r.ROUTINE_TYPE = 'PROCEDURE'
ORDER BY r.ROUTINE_SCHEMA, r.ROUTINE_NAME

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

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

/***** BEGIN - DROP FUNCTIONs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName)
SELECT r.ROUTINE_SCHEMA, r.SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES r
WHERE r.ROUTINE_TYPE = 'FUNCTION'
ORDER BY r.ROUTINE_SCHEMA, r.ROUTINE_NAME

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

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

/***** BEGIN - DROP VIEWs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE = 'VIEW'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME

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

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

/***** BEGIN - DROP FOREIGN KEY CONSTRAINTs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @tableSchema VARCHAR(128), @tableName VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128), TableSchema VARCHAR(128), TableName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName, TableSchema, TableName)
SELECT tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME, tc.TABLE_SCHEMA, tc.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE (tc.CONSTRAINT_CATALOG = DB_NAME()) AND (tc.CONSTRAINT_TYPE = 'FOREIGN KEY')
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME

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

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'ALTER TABLE [' + t.TableSchema + '].[' + t.TableName + '] DROP CONSTRAINT [' + t.ObjectName + '];', 
        @schema = t.SchemaName, @name = t.ObjectName, @tableSchema = t.TableSchema, @tableName = t.TableName
    FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped FOREIGN KEY CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';
    SET @i = @i + 1;
END
GO
/***** END - DROP FOREIGN KEY CONSTRAINTs *****/

/***** BEGIN - DROP PRIMARY KEY CONSTRAINTs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @tableSchema VARCHAR(128), @tableName VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128), TableSchema VARCHAR(128), TableName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName, TableSchema, TableName)
SELECT tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME, tc.TABLE_SCHEMA, tc.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE (tc.CONSTRAINT_CATALOG = DB_NAME()) AND (tc.CONSTRAINT_TYPE = 'PRIMARY KEY')
ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME

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

WHILE (@i <= @Rows) 
BEGIN
    SELECT @sqlCommand = 'ALTER TABLE [' + t.TableSchema + '].[' + t.TableName + '] DROP CONSTRAINT [' + t.ObjectName + '];', 
        @schema = t.SchemaName, @name = t.ObjectName, @tableSchema = t.TableSchema, @tableName = t.TableName
    FROM @t t WHERE RowID = @i;
    EXEC sp_executesql @sqlCommand;        
    PRINT 'Dropped PRIMARY KEY CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';
    SET @i = @i + 1;
END
GO
/***** END - DROP PRIMARY KEY CONSTRAINTs *****/

/***** BEGIN - DROP TABLEs *****/
DECLARE @schema VARCHAR(128), @name VARCHAR(128), @sqlCommand NVARCHAR(1000), @Rows INT = 0, @i INT = 1;
DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

INSERT INTO @t(SchemaName, ObjectName)
SELECT t.TABLE_SCHEMA, t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME

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

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

SyntaxHighlighter 2.0 integration with Blogger

Link to a clear, concise, and complete tutorial on how to integrate the SyntaxHighlighter 2.0 library with Blogger: Syntax Highlighting with Blogger Engine