May 25, 2012 at 11:31 am
My script drops a specific set of views by using a cursor to loop through the view names. Can the following cursor block be replaced with a CTE construction?
DECLARE @cmd VARCHAR(MAX)
DECLARE curViews CURSOR FOR
SELECT cmd = 'DROP VIEW ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND TABLE_NAME LIKE 'x[_]%'
ORDER BY TABLE_NAME
OPEN curViews
FETCH NEXT FROM curViews INTO @cmd
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE (@cmd)
FETCH NEXT FROM curViews INTO @cmd
END
CLOSE curViews
DEALLOCATE curViews
Thanks,
Gordon Rogers
May 25, 2012 at 12:46 pm
USE AdventureWorks2008R2;
GO
DECLARE @sql nvarchar(MAX) =
(
SELECT
N'DROP ' +
QUOTENAME(SCHEMA_NAME(v.[schema_id])) +
N'.' +
QUOTENAME(v.name) +
N';' + NCHAR(13) + NCHAR(10)
FROM sys.views AS v
WHERE
is_ms_shipped = 0
ORDER BY
SCHEMA_NAME(v.[schema_id]),
v.name
FOR XML
PATH (''),
TYPE
).value('.[1]', 'nvarchar(max)')
PRINT @sql;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 25, 2012 at 1:44 pm
Thanks! That worked with only a couple of minor changes:
SQL Kiwi (5/25/2012)
DECLARE @sql nvarchar(MAX) =
(
SELECT
N'DROP VIEW' +
QUOTENAME(SCHEMA_NAME(v.[schema_id])) +
N'.' +
QUOTENAME(v.name) +
N';' + NCHAR(13) + NCHAR(10)
FROM sys.views AS v
WHERE
v.is_ms_shipped = 0
AND v.name LIKE 'x[_]%'
ORDER BY
SCHEMA_NAME(v.[schema_id]),
v.name
FOR XML
PATH (''),
TYPE
).value('.[1]', 'nvarchar(max)')
PRINT @sql;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply