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

 

Identity

Most of the time IDENTITY property in tables is being used when we do not have specific candidate for our primary key. After this some problems could rise and one of them is "What If I want to rearrange my field, how I am going to do that?"
There could be three different approach take care of this issue:

1. Create a new table and transfer the table to new table. Next, rename the old table to another name. Finally, rename the new table to the old table's original name.
2. Alter the table and drop existing IDENTITY column. Next, add a new column as IDENTITY.
3. Update the current IDENTITY column with appropriate values.

Thus, I am not going to discuss on how to do the first two choices and will try to brainstorm my idea on the third choice.
First, I will create a table with two columns which one of them would be IDENTITY column.

Create Table testIdentity (     IdentityID Int Not Null Identity(1, 1)     ,IdentityDescription Varchar(10) )

Next, I will try to INSERT some records into the table.

Insert
Into dbo.testIdentity (IdentityDescription)

Values ('ABC')

,('A')

,('AB')

,('ABCD')

,('ABCDE')

,('ABCDEF')

,('ABCDEFG')

,('ABCDEFGH')

,('ABCDEFGHI')

And check the records that have been inserted.
Select * From dbo.testIdentity --9 records has been added


In the next step I will try to DELETE some records from my table.
    Delete From dbo.testIdentity Where IdentityID In (3, 6)
-- two sample records has been deleted


My process will start from here. So far, everything was very simple and fine.
I will transfer all my data in the table to a local table variable and DELETE all data from table, INSERT them back again with correct sequence number.

Set Identity_Insert dbo.testIdentity On

Go

This will let to INSERT new values in the table with our desired ID.
In the next step,

Declare @myTempTable Table (RowNo Int, IdentityDescription Varchar(10))
-- Create a local variable table to transfer data

Insert Into @myTempTable

Select Row_Number() Over (Order by IdentityID) As RowNo, IdentityDescription From dbo.testIdentity

Delete From dbo.testIdentity --Delete all records from main table

So, I transfer all my data to local variable table and delete all of them from main table.

Insert Into dbo.testIdentity (IdentityId, IdentityDescription) --Insert from local table to main table

Select * From @myTempTable

I inserted all my data back into original table. So, next step would be to RESET the IDENTITY to the MAX of values.

Declare @RecordCount Int

Select @RecordCount = Max(RowNo) From @myTempTable

DBCC CheckIdent('dbo.testIdentity', Reseed, @RecordCount);

And turn off "Identity_Insert".
Set Identity_Insert dbo.testIdentity Off
Finally, for the test will try to INSERT and new record and see what would be my new ID.
Insert Into dbo.testIdentity (IdentityDescription) Values ('AB')