CODEWITHSUNDEEP
t-sqlsql-server-2012
Andrew Sehr
Andrew Sehr

Reputation: 352

Querying all views in SQL Server 2012 database instance

I am having a problem where the query below is only selecting the views from the current database I am connected to and not all. Is there anyway to do this and not use sp_MSForEachDB?

DECLARE @useText VARCHAR(500)
DECLARE @viewNM VARCHAR(500)
DECLARE @schemaNM VARCHAR(500)
DECLARE @dbNM VARCHAR(500)
DECLARE @sqlCmd varchar(4000)

DECLARE DBCursor CURSOR GLOBAL FOR
  SELECT NAME FROM SYS.DATABASES

OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @dbNM

WHILE @@FETCH_STATUS <> -1
BEGIN
    SET @useText = 'USE [' + @dbNM + ']'
    EXEC(@useText)

    DECLARE ViewCursor CURSOR FOR
        SELECT 'GRANT SELECT ON ' + '[' + SCHEMA_NAME(schema_id) + '].[' +  name + ']' + ' TO [account]'
        FROM SYS.VIEWS V

    OPEN ViewCursor
    FETCH NEXT FROM ViewCursor INTO @sqlCmd

    WHILE @@FETCH_STATUS = 0
    BEGIN
         PRINT(@sqlCmd)
         FETCH NEXT FROM ViewCursor INTO @sqlCmd
    END

    CLOSE ViewCursor
    DEALLOCATE ViewCursor

    FETCH NEXT FROM DBCursor INTO @dbNM
END

CLOSE DBCursor
DEALLOCATE DBCursor

Upvotes: 0

Views: 465

Answers (1)

jim31415
jim31415

Reputation: 8818

When EXEC statement executes a string, the string is executed as its own self-contained batch. So, the USE dbname does not affect the query outside of the EXEC.

I build a query statement like this, SELECT 'GRANT SELECT ON [' + SCHEMA_NAME(schema_id) + '].[' + name + '] TO [account]' FROM [master].SYS.VIEWS V; where the database name is inserted before the SYS.VIEWS.

See below:

DECLARE @useText VARCHAR(500)
DECLARE @viewNM VARCHAR(500)
DECLARE @schemaNM VARCHAR(500)
DECLARE @dbNM VARCHAR(500)
DECLARE @sqlCmd varchar(4000)

DECLARE DBCursor CURSOR GLOBAL FOR
  SELECT NAME FROM SYS.DATABASES

OPEN DBCursor
FETCH NEXT FROM DBCursor INTO @dbNM

WHILE @@FETCH_STATUS <> -1
BEGIN

        set @sqlCmd = 'SELECT ''GRANT SELECT ON ['' + SCHEMA_NAME(schema_id) + ''].['' +  name + ''] TO [account]'' FROM [' + @dbNM + '].SYS.VIEWS V;'

        exec( @sqlCmd );

    FETCH NEXT FROM DBCursor INTO @dbNM
END

CLOSE DBCursor
DEALLOCATE DBCursor

EDIT:

You could append a USE in front of the GRANT.

set @sqlCmd = 'SELECT ''USE ' + @dbNM + '; GRANT SELECT ON ['' + SCHEMA_NAME(schema_id) + ''].['' +  name + ''] TO [account]'' FROM [' + @dbNM + '].SYS.VIEWS V;'

Upvotes: 1

Related Questions