SQL Server Tool to script dropdown statements that check for existing events before them?
I have tried SQL Server 2008 Management Studio and other third party tools to script all database objects (views, SP and tables) and I can't get anything to generate a single script file that has a drop statement preceded by an "If exists .." for each object.
I need an if if statement, so I don't get any errors if the object doesn't exist. The tool doesn't have to be for SQL Server 2008.
source to share
SQL 2008 Management Studio can do this. Right click on the database name and select "Tasks -> Generate Scripts" ... check "All Database Objects" and then on the next screen set "Enable" if "DOES NOT EXIST" to "True" (this will also do "If exists ..." although not obvious) and Script Drop to True. I think this will work for you.
source to share
Set "results to text" and run the following script. Cut and paste the results into the window and run. You can script your script to filter out specific tables if you like.
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
Result:
--------------------------------------------------------------------------
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
source to share