Resetting DB

> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
Below is a script that will drop all objects. If the database is not large
or you are using SQL 2005, you might find it easy to simply drop and
recreate the database.
IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
BEGIN
RAISERROR('Not for use on system databases', 16, 1)
GOTO Done
END
--Drop objects from current the database
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.SequenceNumber int
DECLARE @.LastError int
DECLARE @.TablesDropped int
DECLARE DropStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
1 AS SequenceNumber,
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
UNION ALL
--procedures and functions
SELECT
2 AS SequenceNumber,
N'DROP PROCEDURE ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = N'FUNCTION' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsSchemaBound') = 1 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0
UNION ALL
--foreign keys
SELECT
3 AS SequenceNumber,
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
N' DROP CONSTRAINT ' +
CONSTRAINT_NAME AS DropStatement
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = N'FOREIGN KEY'
UNION ALL
--tables
SELECT
4 AS SequenceNumber,
N'DROP TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'BASE TABLE' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
ORDER BY SequenceNumber
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
--EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError > 0
BEGIN
RAISERROR('Script terminated due to unexpected error', 16, 1)
GOTO Done
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
Done:
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
> Hi
> Is there an easy way to drop all object of a db so it is as new? Just so I
> can run a script to recreate the objects.
> Thanks
> Regards
>
Just a thought here, but another way, if you're doing this often, might be to
make a backup of the empty database and restore it as needed or to make a
backup of the database files while the database is empty and detach/attach as
needed - though that second option doesn't sound nearly as fast as just about
any of the others but it, like the first option here, would give you clean
log files and database files.
Dale
Dale Preston
MCAD C#
MCSE, MCDBA
"Dan Guzman" wrote:

> Below is a script that will drop all objects. If the database is not large
> or you are using SQL 2005, you might find it easy to simply drop and
> recreate the database.
>
> IF DB_NAME() IN ('master', 'msdb', 'model', 'distribution')
> BEGIN
> RAISERROR('Not for use on system databases', 16, 1)
> GOTO Done
> END
> --Drop objects from current the database
> SET NOCOUNT ON
> DECLARE @.DropStatement nvarchar(4000)
> DECLARE @.SequenceNumber int
> DECLARE @.LastError int
> DECLARE @.TablesDropped int
> DECLARE DropStatements CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> --views
> SELECT
> 1 AS SequenceNumber,
> N'DROP VIEW ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'VIEW' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --procedures and functions
> SELECT
> 2 AS SequenceNumber,
> N'DROP PROCEDURE ' +
> QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.ROUTINES
> WHERE
> ROUTINE_TYPE = N'FUNCTION' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsSchemaBound') = 1 AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
> N'.' +
> QUOTENAME(ROUTINE_NAME)),
> 'IsMSShipped') = 0
> UNION ALL
> --foreign keys
> SELECT
> 3 AS SequenceNumber,
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) +
> N' DROP CONSTRAINT ' +
> CONSTRAINT_NAME AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE
> CONSTRAINT_TYPE = N'FOREIGN KEY'
> UNION ALL
> --tables
> SELECT
> 4 AS SequenceNumber,
> N'DROP TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME) AS DropStatement
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = N'BASE TABLE' AND
> OBJECTPROPERTY(
> OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)),
> 'IsMSShipped') = 0
> ORDER BY SequenceNumber
> OPEN DropStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM DropStatements INTO @.SequenceNumber, @.DropStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> BEGIN
> RAISERROR('%s', 0, 1, @.DropStatement) WITH NOWAIT
> --EXECUTE sp_ExecuteSQL @.DropStatement
> SET @.LastError = @.@.ERROR
> IF @.LastError > 0
> BEGIN
> RAISERROR('Script terminated due to unexpected error', 16, 1)
> GOTO Done
> END
> END
> END
> CLOSE DropStatements
> DEALLOCATE DropStatements
> Done:
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:%23vTMcTheHHA.1312@.TK2MSFTNGP03.phx.gbl...
>