About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Tuesday, November 12, 2013

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







No comments:

Post a Comment