Wednesday, June 18, 2008

Sql Server Delete All Records

This is a nifty bit of code. (use 'GO' statement after each line incase you are not using a GUI tool)
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
exec sp_MSforeachtable
@command1 = 'DELETE from ?',
@whereand = 'and name not like ''lt%''' //tables with names starting with lt are left out :)
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

-- or --
use DBNAME
exec sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
exec sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' "
exec sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"
exec sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"

For Dropping all Stored Procs
use DBNAme
declare @procName sysname
declare someCursor cursor FOR
SELECT name FROM sysobjects WHERE type = 'P' AND objectproperty(id, 'IsMSShipped') = 0
open someCursor
fetch next FROM someCursor INTO @procName
while @@FETCH_STATUS = 0
begin
print 'dropping '+@procName
exec('drop proc ' + @procName)
fetch next FROM someCursor INTO @procName
end
close someCursor
deallocate someCursor
go

No comments: