Monday, June 20, 2011

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


    sysView.object_id As ObjectId, As ViewName,

    sysView.parent_object_id As ViewParentId,

    sysView.schema_id As SchemaId, As SchemaName,

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


    sys.views As sysView

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

Where = 'dbo'

    And Like '%Test'


Set @RecordCount = @@ROWCOUNT

Set @RowNo = 1


While @RowNo <= @RecordCount


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

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

    Execute (@strSQL)


    Set @RowNo = @RowNo + 1



No comments:

Post a Comment