SQL Server Tool to script drop statements which have check existence statements before them?

Question

I have tried SQL Server 2008 Management Studio and other third party tools to script all database objects (views, SPs & tables) and I can't get anything to generate a one file script which has a drop statement preceded with an "If exists.." statement for every object.

I need the "if exists" statement so I don't get any errors if an object doesn't exist. The tool doesn't have to be for sql server 2008.

Answer 1

SQL 2008 Management Studio can do this. Right click on the database name and select Tasks->Generate Scripts...check the box that says 'All Database Objects' and then on the next screen set Include If NOT EXISTS to True (this will also do the If Exists...though it's not apparent) and Script Drop to True. I think this will work for you.

Answer 2

Set 'results to text' and run the following script. Cut and paste results into a window and run. You can fiddle the script to filter out certain tables if desired.

select 'if object_id ('''+ s.name + '.' + t.name + 
       ''') is not null drop table ' + s.name + '.' + t.name +
       char (10) + 'go'
  from sys.schemas s
  join sys.tables t
    on t.schema_id = s.schema_id

Would produce results like:

--------------------------------------------------------------------------
if object_id ('dim.Dim1') is not null drop table dim.Dim1
go
if object_id ('dim.TestSnapshot') is not null drop table dim.TestSnapshot
go
if object_id ('fact.Test') is not null drop table fact.Test
go