Clearing All Rows From All Tables

This couple of SQL statements will delete all the rows from all tables.

1. It disables referential integrity
2. DELETES or TRUNCATES each table
3. Enables referential integrity
4. Reseeds rows with identity

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

-- This will reseed each table [don't run this exec if you don't want all your seeds to be reset]
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)
'
GO

Thanks to Mauro Cardarelli's post

SQL Server 2008 Releases

What is available:

• SQL Server 2008 SP1 CU 11
• SQL Server 2008 SP2 CU 1
• SQL Server 2008 R2 SP CU2
  

SQL Maintenance Plans

For each instance I like to make a single Maintenance plan with several subplans:

Subplans:

1. System Databases Tasks
1. Reorganize Index
2. Update Statistics
3. Back Up Database
4. Check Database Integrity
5. Notify Operator of Failure
6. Notify Operator of Success
2. User Databases Tasks
   
1. Reorganize Index
2. Update Statistics
3. Back Up Database
4. Check Database Integrity
5. Notify Operator of Failure
6. Notify Operator of Success
3. Daily Transaction Logs Tasks
   
1. Back Up Database (Transaction log type)
   
2. Notify Operator of Success
3. Notify Operator of Failure
   
4. Hourly Transaction Logs Tasks
   
1. Back Up Database (Transaction log type)
2. Notify Operator of Success
3. Notify Operator of Failure
   
4. Cleanup Logs Tasks
   
1. Back Up Database (Transaction log type)
2. Notify Operator of Success
3. Notify Operator of Failure

Maintenance Cleanup
Notify Operator

Find Which Table(s) Contain a Column and the INFORMATION_SCHEMA Namespace.

To find which table(s) contain the PersonID column try this:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%PersonID%'

In the AdventureWorks DB this is what you see:


So of course you can search for any column, not just PersonID.

Another interesting tidbit is that any ANSI compliant DBMS provides the INFORMATION_SCHEMA namespaces. From this namespace you have access to the metadata on any DB object. You can look up information on Stored Procedures and Functions using INFORMATION_SCHEMA.ROUTINES or use any of the many different objects within this namespace: COLUMNS, ROUTINES, CHECK_CONSTRAINTS, PARAMETERS, TABLES, etc.

Enjoy