DropObjectsInSchema

This proc comes in handy when you have to purge all the objects in your database, so you can then run a creation script.  It will tell you how many objects it dropped.

Use Admin

GO

IF Object_ID('dbo.DropObjectsInSchema') Is Not Null

      DROP PROC dbo.DropObjectsInSchema

go

CREATE PROC dbo.DropObjectsInSchema

      @SchemaName varchar(50),

      @ObjectType varchar(50),

      @Debug bit = 0

AS

/*    DATE              AUTHOR            REMARKS

      11/11/13          PPaiva            Initial creation.

      DESCRIPTION

            Drops all objects of type @ObjectType in a given schema.

      USAGE

            Exec DropObjectsInSchema 'dbo', 'FK', 1

            Exec DropObjectsInSchema 'dbo', 'Table', 1

            Exec DropObjectsInSchema 'dbo', 'View', 1

            Exec DropObjectsInSchema 'dbo', 'Proc', 1

            Exec DropObjectsInSchema 'dbo', 'Function', 1

            DropObjectsInSchema 'dbo', 'FK', 1

            DropObjectsInSchema 'dbo', 'FK'

            DropObjectsInSchema 'dbo', 'Table', 1

            DropObjectsInSchema 'dbo', 'Table'

            DropObjectsInSchema 'dbo', 'View', 1

            DropObjectsInSchema 'dbo', 'View'

            DropObjectsInSchema 'dbo', 'Proc', 1

            DropObjectsInSchema 'dbo', 'Proc'

            DropObjectsInSchema 'dbo', 'Function', 1

            DropObjectsInSchema 'dbo', 'Function'

      DEBUG

            SELECT *

            FROM sys.schemas

            WHERE Name Not Like 'db_%'

            SELECT type_desc, Count(*) Qty

            FROM sys.objects

            GROUP BY type_desc

            ORDER BY 1

            SELECT *

            FROM sys.objects

            WHERE type_desc <> 'system_table'

            SELECT *

            FROM vwDictCol

*/

SET NOCOUNT ON

DECLARE @Name varchar(100),

            @MaxName varchar(100),

            @Sql varchar(1000),

            @Num int,

            @Msg varchar(100)

IF @ObjectType Not In ( 'FK', 'Table', 'View', 'Proc', 'Function')

      BEGIN

            SET @Msg = '@ObjectType must be one of these:  FK, Table, View, Proc, Function'

            Print @Msg

            RaisError (@Msg, 16, 1)

            RETURN

      END

CREATE TABLE #t(

      ID int NOT NULL IDENTITY(1, 1),

      Name sysname,

      Sql varchar(2000)

      )

IF @ObjectType = 'FK'

      INSERT INTO #t (Name, Sql)

            SELECT  f.Name,

                        'ALTER TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + object_Name(f.parent_object_id) + ']'

                              + ' DROP CONSTRAINT ['

                              + f.name + ']' Sql                       

            FROM sys.foreign_keys f

            JOIN sys.objects o     

                  ON  o.object_id = f.parent_object_id

            WHERE SCHEMA_NAME(o.schema_id) = @SchemaName

            ORDER BY o.Name

ELSE IF @ObjectType = 'Table'

      INSERT INTO #t (Name, Sql)

            SELECT  o.Name,

                        'DROP TABLE [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql                       

            FROM sys.objects o     

            WHERE type_desc = 'user_table'

              AND SCHEMA_NAME(o.schema_id) = @SchemaName

              AND o.Name Not In ('sysdiagrams', 'Flag', 'logEvent', 'History')

              AND o.Name Not Like 'rsa%'

            ORDER BY o.Name

ELSE IF @ObjectType = 'View'

      INSERT INTO #t (Name, Sql)

            SELECT  o.Name,

                        'DROP VIEW [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql                       

            FROM sys.objects o     

            WHERE type_desc = 'View'

              AND SCHEMA_NAME(o.schema_id) = @SchemaName

              AND o.Name Not In ('vwDictCol', 'vwIndexRaw', 'vwRowCount', 'vwTrigger')

            ORDER BY o.Name

ELSE IF @ObjectType = 'Proc'

      INSERT INTO #t (Name, Sql)

            SELECT  o.Name,

                        'DROP PROC [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql                       

            FROM sys.objects o     

            WHERE type_desc Like '%procedure%'

              AND SCHEMA_NAME(o.schema_id) = @SchemaName

              AND o.Name Not Like 'sp%diagram%'

              AND o.name Not In ( 'DropObjectsInSchema', 'WriteLogEvent')

            ORDER BY o.Name

ELSE IF @ObjectType = 'Function'

      INSERT INTO #t (Name, Sql)

            SELECT  o.Name,

                        'DROP FUNCTION [' + SCHEMA_NAME(o.schema_id) + '].[' + o.name + ']' Sql                    

            FROM sys.objects o     

            WHERE type_desc Like '%function%'

              AND SCHEMA_NAME(o.schema_id) = @SchemaName

              AND o.Name Not In ('fn_diagramobjects', 'udfFormatInteger', 'udfGetColumnList', 'udfGetEntityRoleId', 'udfFormatTimeWithColons')

            ORDER BY o.Name

SELECT  @MaxName = Max(Name),

            @Name = '',

            @Num = Count(*)

FROM #t

SET @Msg = @ObjectType + 's found in schema ' + @SchemaName + ':  ' + Convert(varchar, @Num)

Print ''

Print @Msg

WHILE @Name < @MaxName

      BEGIN

            -- Get next Name

            SELECT @Name = Min(Name)

            FROM #t

            WHERE Name > @Name

            -- Get other attibutes

            SELECT @Sql = Sql

            FROM #t

            WHERE Name = @Name

            IF @Debug = 1

                  Print @Sql

            ELSE

                  Exec (@Sql)

      END