Bgonzales Bgonzales - 3 months ago 7
SQL Question

How do I drop a view from all databases on a server

I'm trying to drop a specific view from most databases on a server. Pasted below is what I currently have. It completes successfully but it does not actually drop the view.

What am I missing, why would it not error but not actually drop the view? Also, When I run the exec statement in a specific database it does drop the view.

USE [Master]

DECLARE @DBname VARCHAR (128);
DECLARE @NumDBs INT;
DECLARE @Count INT = 0;


DECLARE getDBs CURSOR
FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('DBName1','DBName2')
ORDER BY name;

SET @NumDBs = (SELECT COUNT(9) FROM sys.databases WHERE
name NOT IN ('DBName1','DBName2') )
;

OPEN getDBs
FETCH NEXT FROM getDBs
INTO @DBname;

WHILE (@Count < @NumDBs)
BEGIN

Exec('IF EXISTS (SELECT *
FROM '+@DBname+'.sys.views
WHERE object_id= OBJECT_ID(N''[ViewName]''))
DROP VIEW [ViewName]'
);

SET @Count = @Count + 1;

FETCH NEXT FROM getDBs
INTO @DBname;
END;

CLOSE getDBs;
DEALLOCATE getDBs;

Answer
Exec('USE ' +@DBname+'; IF  EXISTS (SELECT * 
                  FROM sys.views 
                  WHERE object_id= OBJECT_ID(N''[ViewName]''))                                                    
DROP VIEW [ViewName]'
);
Comments