Tuesday, August 21, 2007

Empty Database Script

As an alternative to scripting, and to see operational issues encountered, I'm creating a 'Delete All Data' script.
This is primarily so I have a test database to use with Replication, Service Broker etc.
In this example I'm creating AdventureWorksTarget, an empty shell of the sample database for sql 2005.

1 Backup AdventureWorks

2 Restore as AdventureWorksTarget

3 Drop any views.
I'm adding this step as this is only a test, and the SCHEMABINDING option on the views preventing me from deleting from some tables.
Use this dynamic SQL to generate the sql, then run it >
SELECT 'DROP VIEW [' + S.name + '].[' + V.name + ']'
FROM sys.views V
JOIN sys.schemas S
ON V.SCHEMA_ID = S.SCHEMA_ID


4 Create supporting procedures -
USP_DropXMLIndexes (code here)
USP_DropTableConstraints_2005 (code here)

5 Clear down XML Indexes and table constraints.
The order is important here, i.e -
1 Remove XML Indexes
2 Remove CHECK constraints
3 Remove FOREIGN KEY constraints
4 Remove PRIMARY KEY constraints

This dynamic sql calls the supporting procedures in the correct order.
Run it, then resulting sql it produces.
SELECT Command FROM
( SELECT Command = 'EXEC USP_DropXMLIndexes ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + '''', 0 as SortOrder
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'AdventureWorksTarget'
UNION
SELECT Command = 'EXEC USP_DropTableConstraints_2005 ''' + TABLE_CATALOG + ''', ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + ''', ''CHECK''', 1 as SortOrder
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'AdventureWorksTarget'
UNION
SELECT Command = 'EXEC USP_DropTableConstraints_2005 ''' + TABLE_CATALOG + ''', ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + ''', ''FOREIGN KEY''', 2 as SortOrder
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'AdventureWorksTarget'
UNION
SELECT Command = 'EXEC USP_DropTableConstraints_2005 ''' + TABLE_CATALOG + ''', ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + ''', ''PRIMARY KEY''', 3 as SortOrder
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'AdventureWorksTarget' ) CommandBlock
ORDER BY
SortOrder, Command


6 We can finally delete the data in all the tables -
EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?;'

No comments: