asked on

Creating a dynamic drop view sql statement

Hey everyone,

I'm attempting to create a dynamically generated sql statement that will drop all views in the designated database and schema but I'm running into some issues. Here's what I have so far:

declare @databasename varchar(50);
declare @model_id INT;
declare @SchemaName varchar(8);
declare @SQL nvarchar(max)

set @DatabaseName = 'Database1'
set @model_id = 35
set @SchemaName = 'Schema1'

select @SQL = 'IF EXISTS(select * from [' + @DatabaseName + '].INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = ''' + @SchemaName + ''')
DROP VIEW [TABLE_SCHEMA].[TABLE_NAME]
FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.VIEWS
WHERE [TABLE_SCHEMA] = ''' + @SchemaName = '''

print @SQL
EXEC (@SQL)
'

print @SQL
EXEC(@SQL)

Open in new window

I think I have the bones but just having trouble with the last bit to make it run properly. In theory this should generate a view existence check and drop statement for each view that exists in the database schema and execute them. Any help is very much appreciated.

Thanks,
Glen

Scott Pletcher

That won't do it. You need a separate, specific DROP statement for every view.

I suggest inserting the generated drop statements into a temp table, and cursor thru them and execute them. If you're willing to take that approach, let me know and I will code it up and post it here.

You could also try to concatenate the statements using XML and run them as a CLOB, but certain special chars are then a royal p.i.t.a. to deal with.

ASKER CERTIFIED SOLUTION

Alpesh Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jisoo411

ASKER

Got it! Here's the piece of corrected code that I'll be integrating into the original script:

declare @SQL nvarchar(max);

select @SQL = coalesce(@SQL + ',','') + 'DROP VIEW ' + TABLE_SCHEMA + '.' + TABLE_NAME from database1.INFORMATION_SCHEMA.VIEWS where TABLE_SCHEMA = 'schema1'

exec database1.dbo.sp_executesql @SQL

Open in new window

Thanks for the help, I didn't want to do this procedurally by using temp tables and loops so that my code would run faster.