Dropping Massive objects

For most of us could happen that we will be assign to a project or responsible for maintenance of database that has Tables, Views, or stored procedures which have been created as test or any other reason and we need to clean them up.

Here is a routine that will do the job based on Criteria. My sample is for Views but can be replaces for Stored Procedures or Tables.

 

Declare @RowNo Int, @RecordCount Int, @strSQL Varchar(Max), @ViewName Varchar(128), @SchemaName Varchar(128)

 

Declare @myViewInfo Table (ObjectId Int, ViewName Varchar(128), ViewParentId Int, SchemaId Int, SchemaName Varchar(128), RN Int)

 

Insert Into @myViewInfo

Select

    sysView.object_id As ObjectId,

    sysView.name As ViewName,

    sysView.parent_object_id As ViewParentId,

    sysView.schema_id As SchemaId,

    sysSchem.name As SchemaName,

    Row_Number() Over(Order
By sysView.object_id) As RN

From

    sys.views As sysView

    Inner Join sys.schemas As SysSchem On sysView.schema_id = SysSchem.schema_id

Where

    sysSchem.name = 'dbo'

    And sysView.name Like '%Test'

 

Set @RecordCount = @@ROWCOUNT

Set @RowNo = 1

 

While @RowNo <= @RecordCount

Begin

    Select @SchemaName = SchemaName, @Viewname = ViewName From @myViewInfo Where RN = @RowNo

    Set @strSQL = 'Drop View ' + @SchemaName + '.' + @ViewName

    Execute (@strSQL)

        

    Set @RowNo = @RowNo + 1

End