SQL scripts - Delete all Tables, Procedures, Views and Functions

Delete All Tables


DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO



Delete All Stored Procedures



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

Delete All Views



declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = 'v'
open cur

fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop view ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur


Delete All Functions



declare @procName varchar(500)
declare cur cursor
for select [name] from sys.objects where type = 'fn'
open cur

fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop function ' + @procName)
fetch next from cur into @procName
end

close cur
deallocate cur

沒有留言:

張貼留言