Clear Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Clear Database

Author

Topic

arvind_ramugade
Starting Member

30 Posts

Posted - 2012-07-26 : 11:22:16

Hi,
is there any equivalent of "clear database" command in MSSQL ?
In Sybase we use "clear database" to drop all the objects.
is there any command available in MSSQL 2008 ?

Thanks !

ramugade

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-26 : 11:29:16

drop the database and recreate it?
script drop statements for all objects then run it (several times if you have referential integrity)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

arvind_ramugade
Starting Member

30 Posts

Posted - 2012-07-26 : 11:41:58

We don't want to drop the database , just drop all schema objects within it

ramugade

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 11:48:21

quote:
Originally posted by arvind_ramugade

We don't want to drop the database , just drop all schema objects within it

ramugade

iterate through sys.objects and generate DROP statements and execute them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arvind_ramugade
Starting Member

30 Posts

Posted - 2012-07-26 : 11:57:45

Hi,
Iterating through sys.objects and generating DROP statements , will this take care of dependency issues. e.g.foreign key constraints etc (if we run one by one) ?

ramugade

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-07-26 : 12:07:54

This will do it. In your SSMS query editor, set the output to text and run this. Then copy/paste the results and execute them. You may need to add other types (CLR functions for example. See complete list here. Constraints, except for FKs are dropped with the parent table.)


SELECT	'ALTER TABLE [' + schema_name(schema_id) + '].[' +
	object_name(parent_object_id) + '] 
DROP CONSTRAINT [' + name + '];
GO'
FROM	sys.foreign_keys
WHERE	is_ms_shipped = 0
UNION ALL
SELECT	'DROP ' +
	CASE
		WHEN type = 'U' THEN 'TABLE'
		WHEN type = 'P' THEN 'PROC'
		WHEN type = 'V' THEN 'VIEW'
		WHEN type = 'FN' THEN 'FUNCTION'
		WHEN type = 'SN' THEN 'SYNONYM'
	END
	+ ' ['+ schema_name(schema_id) + '].[' +
	name + '];
GO'
FROM	sys.objects
WHERE	is_ms_shipped = 0
AND	type in ('U', 'P', 'V', 'FN', 'SN');

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-26 : 12:11:47

backup restore may be the cleanest way. Dropping and recreating all those objects is going to take a lot of time for very little value

Make up a blank db. Back it up and then you can restore it whenever you like.

or -- modify the Model database on the server to be your stub. Then whenever you create a new db it'll get the objects in Model.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-07-26 : 12:19:07

Another option is to create an empty database and rename it. You'd have to drop or rename the old one first.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources