Batch Delete tables and stored procedures using SQL.

Recently, I used Godaddy space. Due to the large number of tables in the system, it was very troublesome to delete them one by one. I collected some solutions online.

To share with you:

 

1. Batch delete stored procedure declare @ procname varchar (500)

Declare cur cursor

For select [name] From SYS. objects where type = 'P'

Open cur

Fetch next from cur into @ procname

While @ fetch_status = 0

Begin

If @ procname <> 'deleteallprocedures'

Exec ('drop procedure '+ @ procname)

Fetch next from cur into @ procname

End

Close cur

Deallocate cur
2. Batch Delete Foreign keys

Declare C1 cursor
Select 'alter table ['+ object_name (parent_obj) +'] Drop constraint ['+ name +'];'
From sysobjects
Where xtype = 'F'
Open C1
Declare @ C1 varchar (8000)
Fetch next from C1 into @ C1
While (@ fetch_status = 0)
Begin
Exec (@ C1)
Fetch next from C1 into @ C1
End
Close C1
Deallocate C1
3. Batch Delete tables

Declare C2 cursor
Select 'drop table ['+ name +'];'
From sysobjects
Where xtype = 'U'
Open C2
Declare @ C2 varchar (8000)
Fetch next from C2 into @ C2
While (@ fetch_status = 0)
Begin
Exec (@ C2)
Fetch next from C2 into @ C2
End
Close C2
Deallocate C2
-- Batch clear table content:

-- 1. Disable foreign key constraints
Declare C1 cursor
Select 'alter table ['+ object_name (parent_obj) +'] nocheck constraint ['+ name +'];'
From sysobjects
Where xtype = 'F'
Open C1
Declare @ C1 varchar (8000)
Fetch next from C1 into @ C1
While (@ fetch_status = 0)
Begin
Exec (@ C1)
Fetch next from C1 into @ C1
End
Close C1
Deallocate C1
-- 2. Clear table content
Declare C2 cursor
Select 'truncate table ['+ name +'];'
From sysobjects
Where xtype = 'U'
Open C2
Declare @ C2 varchar (8000)
Fetch next from C2 into @ C2
While (@ fetch_status = 0)
Begin
Exec (@ C2)
Fetch next from C2 into @ C2
End
Close C2
Deallocate C2
-- 3. enable foreign key constraints
Declare C1 cursor
Select 'alter table ['+ object_name (parent_obj) +'] Check constraint ['+ name +'];'
From sysobjects
Where xtype = 'F'
Open C1
Declare @ C1 varchar (8000)
Fetch next from C1 into @ C1
While (@ fetch_status = 0)
Begin
Exec (@ C1)
Fetch next from C1 into @ C1
End
Close C1
Deallocate C1