Drop all objects

HI,

Been poking around in sysobjects and information_schema.routines trying to work out how to best write scripts that will drop all specific objects from a database.

That is, scripts to drop all tables, views, stored procs, functions ( FN, IF, TF ) but can't seem to figure out appropriate way to do it.

A pointer on how to drop all of any one of the above object types would be greatly appreciated and I should be able to work out the others.

Further, when executing multiple scripts I am writing scripts like this...

ddl_batch.sql

Code Snippet

:R table1.sql

:R table2.sql

:R ufn_func1.sql

:R ufn_func2.sql

:R view_table1

:R view_table2

:R usp_proc1.sql

:R usp_proc2.sql

And executing via:

sqlcmd -S server\instance -i ddl_batch.sql

This is to maintain individual object type scripts, and then to execute them together in dependency order, rather than executing one monolithic batch script. Is this a reasonable way to go about it or is there a better way?

Many thanks in advance for your help.

Compose your SQL Strings on the fly using something like the following:

SELECT 'DROP TABLE [' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND OBJECTPROPERTY(OBJECT_ID('[' + TABLE_SCHEMA + ']' + '.' + '[' + TABLE_NAME + ']'),'IsMSShipped') = 0 --You do not want to drop the MS Shipped, right ;-) ?

You can use the output and store it in a file or execute it on the fly. Make sure that you first frop the foreign keys on the tables, then drop the tables, then the views, afterwards the functions.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Once again Jens many thanks for your help.