-- disable all constraints EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- delete data in all tables EXEC sp_MSForEachTable "DELETE FROM ?" -- enable all constraints exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
What's up!
Pyaarey Allah!
Sunday, March 27, 2011
Truncate db
Saturday, March 26, 2011
Drop all objects from a sql server database
-------------START--------------------------------- declare @n char(1) set @n = char(10) declare @stmt nvarchar(max) -- procedures select @stmt = isnull( @stmt + @n, '' ) + 'drop procedure [' + name + ']' from sys.procedures -- check constraints select @stmt = isnull( @stmt + @n, '' ) + 'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']' from sys.check_constraints -- functions select @stmt = isnull( @stmt + @n, '' ) + 'drop function [' + name + ']' from sys.objects where type in ( 'FN', 'IF', 'TF' ) -- views select @stmt = isnull( @stmt + @n, '' ) + 'drop view [' + name + ']' from sys.views -- foreign keys select @stmt = isnull( @stmt + @n, '' ) + 'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']' from sys.foreign_keys -- tables select @stmt = isnull( @stmt + @n, '' ) + 'drop table [' + name + ']' from sys.tables -- user defined types select @stmt = isnull( @stmt + @n, '' ) + 'drop type [' + name + ']' from sys.types where is_user_defined = 1 exec sp_executesql @stmt --------------------------END---------------------
Subscribe to:
Posts (Atom)