SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Clear Database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arvind_ramugade
Starting Member

India
30 Posts

Posted - 07/26/2012 :  11:22:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/26/2012 :  11:29:16  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

arvind_ramugade
Starting Member

India
30 Posts

Posted - 07/26/2012 :  11:41:58  Show Profile  Reply with Quote
We don't want to drop the database , just drop all schema objects within it


ramugade
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/26/2012 :  11:48:21  Show Profile  Reply with Quote
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/

Go to Top of Page

arvind_ramugade
Starting Member

India
30 Posts

Posted - 07/26/2012 :  11:57:45  Show Profile  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 07/26/2012 :  12:07:54  Show Profile  Visit russell's Homepage  Reply with Quote
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');
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 07/26/2012 :  12:11:47  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

robvolk
Most Valuable Yak

USA
15636 Posts

Posted - 07/26/2012 :  12:19:07  Show Profile  Visit robvolk's Homepage  Reply with Quote
Another option is to create an empty database and rename it. You'd have to drop or rename the old one first.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000