Delete all views from Sql Server

sql sql-server-2008 sp-msforeachtable
Sam picture Sam · Jul 27, 2012 · Viewed 50.1k times · Source

By using this statement in SQL Server: 

EXEC sp_msforeachtable 'DROP TABLE ?'

I know it's possible to delete all tables at once.

Is there a similar statement for views? I tried this hoping to be lucky: EXEC sp_msforeachview 'DROP VIEW ?' but it doesn't work!

Answer

Yaroslav picture Yaroslav · Jul 27, 2012

Here you have, no cursor needed:

DECLARE @sql VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM   sys.views v

PRINT @sql;
EXEC(@sql);

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More