Drop all database objects in sql 2005

Question

Drop all database objects in sql 2005

testonvia

Grasshopper

Points: 18
More actions
February 15, 2009 at 1:43 am

#155275

Hi All,
I am using SQL 2005
There is a situation in my project ,where in I need to drop all the user created database objects likes tables, views, functions, stored procedures, functions , constraints etc , so that the database as if it was a newly created fresh database. In order to achieve this , I believe I need to drop the constraints first , then views or something like this..
1. Can any one tell me in what order should I drop the objects , so that there are no errors.
2. What are the objects that I need to drop
3. Has someone already written a TSQL Script or a stored procedure which does the same thing. If so, please post it or post the link to it.
Thanks,
Onvia

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

If you are deleting all the user tables and therefore the data in the DB, then is there anything that stops you from simply deleting and re-creating the (empty) DB?

This might be an easier alternative than to struggle with the correct order to drop dependant objects.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

ps. One Orange Chip Points: 29422 More actions · Answer 2

I'd go with John's advice to create an empty database (you may want to script your tables for future use)

Anyways, you'll need to begin with deleting dependent objects before deleting the tables. query sys.objects table with xtype='u' and join it on sys.indexes to find out indexes created on user tables and with sysconstraints seperately to find out constraints on user tables and them delete those.

then you can delete SPs/functions and so on..

finally user tables...

select * from sys.objects where xtype='u'

Pradeep Singh

testonvia Grasshopper Points: 18 More actions · Answer 3

Hi John,

Actually I could have created a new database(I totally agree with you John) , but the thing that stops me from doing that is that I dont have permission to create that on the server where the database gets hosted and also this is for automating for the purpose of testing.

I believe that someone must have definitely tried this thing out . So I would probably need to have a look at their code .

Thanks,

Onvia

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

1 drop constraint

2 drop views, functions, sp

3 drop tables.

That should pretty much work as is. The 2 things that block you from dropping a table are fk constraints and schema bound objects (like views or functions). Dropping in that sequense should work out just fine.

You can also script the whole db with EM or ssms. When you include the drop statements, you'll see in what sequense MS does it... Heck you could most likely use their code as is.

Lowell SSC Guru Points: 323518 More actions · Answer 5

there is a built in procedure to get all your objects in dependancy order:

EXEC sp_msdependencies @intrans = 1

I've only used it to build script, and not to drop stuff, but it's a helpful proc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Since when are the ms dependencies reliable??? God knows they were far from it in 2000. I heard they were working on it, but never got the news that it was fixed and 100% reliable.

John H Marsh SSChampion Points: 11671 More actions · Answer 7

Hi Onvia,

Shame you don’t have the permissions as that would have been the easiest solution.

You could use RedGate’s SQL Compare to compare the DB to an empty DB, and then use its Wizard to generate a script to synchronise your DB to the empty one i.e. drop everything.

Regards,

John

www.sql.lu
SQL Server Luxembourg User Group