Useful operations with sp_MSforeachtable Stored Procedure

Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase:
---------------------
--Delete all data in the database
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

EXEC sp_MSForEachTable
'BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
DELETE FROM ?
END CATCH;'
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
---------------------
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable 'DELETE ?'
---------------------
--Drop all Tables
exec sp_MSforeachtable 'DROP TABLE ?'
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
---------------------










Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase with printed message in Messages window of SQL Server Management Studio:
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'"
exec sp_MSforeachtable "ALTER TABLE ? DISABLE TRIGGER ALL PRINT'? trigger altered'"
---------------------
--Perform delete operation on all table for cleanup
exec sp_MSforeachtable "DELETE ? PRINT'? deleted'"
--Delete all data in the database
EXEC sp_MSForEachTable "DELETE FROM ? PRINT'? deleted'"
EXEC sp_MSForEachTable "TRUNCATE TABLE ? PRINT'? truncated'"
EXEC sp_MSForEachTable
"BEGIN TRY
TRUNCATE TABLE ? PRINT'? truncated'
END TRY
BEGIN CATCH
DELETE FROM ? PRINT'? deleted'
END CATCH;"
---------------------
--Drop all Tables
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped'"
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL PRINT'? constraint altered'"
exec sp_MSforeachtable "ALTER TABLE ? ENABLE TRIGGER ALL PRINT'? trigger altered'"
---------------------








Following commands displays how to execute delete, truncate, drop, enable/disable constraints/trigger operations on a DataBase with printed message in Messages window of SQL Server Management Studio and for a particular schema:
---------------------
--Disable Constraints & Triggers
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL PRINT'? constraint altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? DISABLE TRIGGER ALL PRINT'? trigger altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------
--Drop table of particular shcemaID/shemaName
Exec sp_MSforeachtable
@command1 = "DROP TABLE ? PRINT '? dropped'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------
--Enable Constraints & Triggers again
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? CHECK CONSTRAINT ALL PRINT'? constraint altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
exec sp_MSforeachtable
@command1 = "ALTER TABLE ? ENABLE TRIGGER ALL PRINT'? trigger altered'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')"
---------------------








Following displays how to execute Drop operation on a DataBase for a particular schema and with Like condition:
---------------------
--Drop table of particular shcemaID/shemaName and with name starting with 'Temp_'
Exec sp_MSforeachtable
@command1 = "DROP TABLE ? PRINT '? dropped'",
@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')
and o.name LIKE 'Temp_%'"
---------------------




Source: http://avinashkt.blogspot.com/2008/05/useful-operations-with-spmsforeachtable.html

How to empty all the tables in a database?

Sometimes it is exercised to empty all the tables in a database by deleting all the records from all the tables or by truncating every table in a database. This is indeed needed while we are developing a real life application and playing around with the database in order to carry out testing. But as it seems to be this task is not that much easy and trivial because usually there exists so many Primary Key-Foreign Key relationships between parent and child tables at many levels in such types of applications. If you start deleting records in improper sequence, records might not be deleted from every table successfully as depending upon the option ON DELETE CASCADE set on foreign tables records might not be deleted from parent tables as long as their corresponding child records are existing and you don’t want to be warned by SQL Server with the error message “The DELETE statement conflicted with the REFERENCE constraint…” every time you try to delete the records from some tables and at the same time you also don’t want to remember the complete hierarchy of parent-child relationships between all the tables in order to carry out delete operation in proper sequence. While emptying tables, sometimes we need to reset the SEED value of identity fields which the TRUNCATE command does it for us and DELETE command does not. But unfortunately TRUNCATE also does not work because as long as the foreign key constraints are existing, parent tables can’t to be truncated. Keeping all these in mind, I needed to create a solution which solves this problem.

I wrote the following stored procedure that performs this task for us. Just create this stored procedure in the database for which the tables need to be emptied.

-----------------------

Create Procedure dbo.sp_EmptyAllTables (@ResetIdentity Bit)

As

Begin

Declare @SQL VarChar(500)

Declare @TableName VarChar(255)

Declare @ConstraintName VarChar(500)

Declare curAllForeignKeys SCROLL CurSor For SelectTable_Name,Constraint_Name FromInformation_Schema.Table_Constraints WhereConstraint_Type='FOREIGN KEY'

Open curAllForeignKeys

Fetch Next From curAllForeignKeys INTO@TableName,@ConstraintName

While @@FETCH_STATUS=0

Begin

Set @SQL = 'ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ' + @ConstraintName

Execute(@SQL)

Fetch Next From curAllForeignKeys INTO@TableName,@ConstraintName

End

Declare curAllTables Cursor For Select Table_Name FromInformation_Schema.Tables Where TABLE_TYPE='BASE TABLE'

Open curAllTables

Fetch Next From curAllTables INTO @TableName

While @@FETCH_STATUS=0

Begin

Set @SQL = 'DELETE FROM ' + @TableName

If @ResetIdentity = 1 AND OBJECTPROPERTY(OBJECT_ID(@TableName),'TableHasIdentity')=1

Set @SQL = @SQL + '; DBCC CHECKIDENT(''' + @TableName +''',RESEED,0)'

Execute(@SQL)

Fetch Next From curAllTables INTO @TableName

End

Fetch First From curAllForeignKeys INTO@TableName,@ConstraintName

While @@FETCH_STATUS=0

Begin

Set @SQL = 'ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT '+ @ConstraintName

Execute(@SQL)

Fetch Next From curAllForeignKeys INTO@TableName,@ConstraintName

End

Close curAllTables

Deallocate curAllTables

Close curAllForeignKeys

Deallocate curAllForeignKeys

End

-----------------------

The above stored procedure accepts one parameter (@ResetIdentity)which can be either 0 or 1 depending upon whether we need to reset the SEED of identity fields or not (1 for resetting otherwise 0). To empty all the tables while resetting the SEED value of identity fields at the same time, just call this store procedure in the following way:

sp_EmptyAllTables 1

I hope this technique will save some of your time needed to perform the task of emptying all the tables in a database.

-Bihag Thaker


Source: http://www.bihag.net/2008/08/how-to-empty-all-tables-in-database.html

Clear and Reset a Database sql2005

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

/*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

Disable the Shutdown Event Tracker on Win2k3

If the event tracker is of no use to you then you can disable it. To do this, open the Group Policy Object Editor Console. Go to Start > Run…, type gpedit.msc and press OK.
Navigate to Computer Configuration > Administrative Templates > System and in the right hand pane, select the “Display Shutdown Event Tracker” setting.

Double Click this setting to open the Properties page. You are now given the option to leave it in a default state of Not Configured, set it to Always Enabled, Enabled for Servers/Workstations (Windows XP Pro) or Disabled completely (as the image below demonstrates).