SQL Server – keeping a few while discarding most Objects

Background

Time to look for help again.

The specific database has too many objects, but I only need to keep about twenty tables or so.

And, remove the other ones.

Script

And, so I wrote a little script.

Outline

There are two parts:

  1. dbo.udtt_Object ( Table Type )
  2. dbo.sp_ScriptDropUnTaggedObjects ( Stored Procedure )

Objects

Table Type

dbo.udtt_Object


if not exists
        (
            select *
            from  sys.types tblSST
            where tblSST.[name] = 'udtt_Object'
        ) 
begin

    CREATE TYPE [dbo].[udtt_Object] AS TABLE
    (

          [id]		bigint	not null identity(1,1) 
        , [schema]  sysname not null 
        , [object]  sysname not null

        , primary key
            (
                  [schema]
                , [object]
            )

    )

end

 

Stored procedure

dbo.sp_ScriptDropUnTaggedObjects


if object_id('dbo.sp_ScriptDropUnTaggedObjects') is null
begin

    exec('create procedure [dbo].[sp_ScriptDropUnTaggedObjects] as ')

end
go

alter procedure [dbo].[sp_ScriptDropUnTaggedObjects]
(
      @tblTable [dbo].[udtt_Object] READONLY
    , @errorIfObjectsToKeeepListIsEmpty bit = 1
)
as
begin

    set nocount on

    
    declare @tblTableCited TABLE
    (
          [id]	   int not null identity(1,1)
        , [schema] sysname not null
        , [object]  sysname not null
        , [type]    char(2) not null
        , [dropClause] 
                as case
                    when [type] = 'U' then ' Table '
                  end
    )

    declare @id smallint
    declare @table sysname 
    declare @iCount int
    declare @idMax smallint

    declare @idCited bigint
    declare @idCitedMax bigint

    declare @citedSchema	  sysname
    declare @citedObject	  sysname
    declare @citedObjectType  char(2)

    declare @citedObjectDropClause	varchar(30)

    declare @strLog nvarchar(600)
    declare @sql    nvarchar(255)

    declare @iNumberofTablesCited int


    declare @iNumberofAttempts int
    declare @iNumberofAttemptsMax int

    declare @CHAR_TAB char(1)

    set @CHAR_TAB = char(9);

    set @id = 1

    set @iCount = ( select count(*) from  @tblTable)
    set @idMax = ( select max( [id]) from  @tblTable)

    --print '@idMax ' + cast(@idMax as varchar(10))

    set @iNumberofTablesCited = -1
    set @idCited = 1

    set @iNumberofAttempts = 1
    set @iNUmberofAttemptsMax = 50


    set @strLog = 'Number of tables tagged ' 
                    + cast(isNull(@idMax, -1) as varchar(10))

    print @strLog


    if (
            (
                   ( @iCount = 0)
                or ( @idMax is null)
            )
            and 
            (
                (@errorIfObjectsToKeeepListIsEmpty = 1)
            )
       )
       begin

        set @strLog = 'Object to keep list is empty'

        raiserror(@strLog, 16,1)

        return -1

       end

    while (
                    ( @iNumberofTablesCited != 0)
                and ( @iNumberofAttempts <= @iNumberofAttemptsMax) ) begin set @strLog = @CHAR_TAB + 'Attempt ' + cast(@iNumberofAttempts as varchar(3)) print @strLog delete from @tblTableCited insert into @tblTableCited ( [schema] , [object] , [type] ) select tblSS.[name] , tblSO.[name] , tblSO.[type] from sys.objects tblSO inner join sys.schemas tblSS on tblSO.[schema_id] = tblSS.[schema_id] where tblSO.[type] = 'U' and not exists ( select tblT.[object] from @tblTable tblT where tblT.[object] = tblSO.[name] ) and not exists ( select * from sys.foreign_keys tblSFK where tblSO.[object_id] = tblSFK.[referenced_object_id] ) set @idCitedMax = ( select max( [id]) from @tblTableCited) set @iNumberofTablesCited = (select count(*) from @tblTableCited ) set @strLog = @CHAR_TAB + cast(@iNumberofTablesCited as varchar(10)) + ' objects cited' print @strLog if (@iNumberofTablesCited > 0)
        begin

            while (@idCited <= @idCitedMax)

            begin

                select
                          @citedSchema = tblTC.[schema]
                        , @citedObject = tblTC.[object]
                        , @citedObjectType = tblTC.[type]
                        , @citedObjectDropClause = tblTC.[dropClause]

                from    @tblTableCited tblTC
        
                where  tblTC.[id] = @idCited

                if (
                            ( @citedSchema is not null )
                        and ( @citedObject is not null )
                        and (@citedObjectDropClause is not null)
                    )
                begin
                
                    set @strLog =
                                    @CHAR_TAB + @CHAR_TAB + 
                                    + cast(@idCited as varchar(10))
                                    + @citedSchema
                                    + '.'
                                    + @citedObject


                    set @sql = 'DROP '
                                    + @citedObjectDropClause
                                    + quotename(@citedSchema)
                                    + '.'
                                    + QUOTENAME(@citedObject)

                    --print @sql

                    set @strLog = @CHAR_TAB + @CHAR_TAB + @sql
                
                    print @strLog

                    exec(@sql)

                end

                set @idCited = @idCited + 1

            end

        end -- @iNumberofTablesCited

        
        set @iNumberofAttempts = @iNumberofAttempts + 1

    end -- Number of retries

end
go


exec sp_MS_marksystemobject '[dbo].[sp_ScriptDropUnTaggedObjects]'
go

 

Invoke


set XACT_ABORT on
go

set nocount on
go

declare @tblTable as [dbo].[udtt_Object]

declare @commit bit

set @commit = 0

insert into @tblTable
select 'dbo', 'Users'
union
select 'dbo', 'Roles'

begin tran


    exec [dbo].[sp_ScriptDropUnTaggedObjects]
          @tblTable = @tblTable
        , @errorIfObjectsToKeeepListIsEmpty = 1

while (@@trancount > 0)
begin

    if (@commit=1)
    begin
    
        print 'commit tran';
        commit tran;
    
    end
    else
    begin
    
        print 'rollback tran';
        rollback tran;
    
    end
end

GitHub

DanielAdeniji/SQLServerScriptDropUntaggedObjects
Link

 

One thought on “SQL Server – keeping a few while discarding most Objects

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s