CODEWITHSUNDEEP
sqlsql-serversql-server-2008view
Prethia
Prethia

Reputation: 1203

How to select a single element from each VIEW in Microsoft SQL 2008

I am trying to select a single element from each view in db to check that if they are still in a "correct form". The hard thing is that the number of this views are too much. While I was researching this I found this piece of code which finds all the table names with their column names.

  GO 
    SELECT t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    ORDER BY schema_name, table_name;

For the first step I think finding the names of All views in my DB will be a good start. Here is the db structure.

Database: DB1,DB2,DB3....... For each DB : DatabaseDiagrams , Tables , Views...

So to found all views in my DB I wrote this.

SELECT t.name AS view_name
FROM sys.views AS t
ORDER BY  view_name;

Some of these databases has 0 views in their folder while some have lots of views. My aim is to select a single element from each of these views. How can I accomplish this?

Edit: Here is the answer I found 

SET NOCOUNT ON;
DECLARE @ViewCount   int                = 0;
DECLARE @Counter     int                = 0;
DECLARE @sql         nvarchar(max)    = '';
DECLARE @viewName    nvarchar(120)    = ''
DECLARE @Views    as TABLE (  pk int identity(1,1), 
                              viewName nvarchar(300), 
                              Primary Key clustered  (pk)
                            );

INSERT INTO  @Views (viewName)
SELECT       name
FROM         sys.views;

SET          @ViewCount = SCOPE_IDENTITY();

WHILE(@Counter < @ViewCount) BEGIN
    SET @Counter = @Counter+1;

    SELECT  @sql = 'select TOP 1 * FROM ' + viewName +';', @viewName = viewName
    FROM    @Views
    WHERE    pk = @Counter;

    BEGIN TRY
        exec(@sql); 
    END TRY BEGIN CATCH
        Print ('Cannot query the view ' + @viewname );
    END CATCH
END;

The code is crystal clear to me but I can explain it to anyone who has a problem in somewhere.

Upvotes: 1

Views: 280

Answers (1)

S.Karras
S.Karras

Reputation: 1493

Ok this next query is a bit long but I think it does what you want:

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @stmt NVARCHAR(MAX);
DECLARE @testViews TABLE (selectStmt NVARCHAR(MAX));

;WITH  dbs
    AS ( SELECT  name
         FROM    sys.databases
         WHERE name NOT IN ('master','msdb','tempdb','model')
       )
SELECT   @SQL = 'SELECT ''SELECT TOP 1* FROM ''' + '+' + '''' + QUOTENAME(dbs.name) + ''''  + '+ ''.'' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + ''.'' + QUOTENAME(t.name) FROM '
        + QUOTENAME(name) + '.sys.views AS t;' + @sql
FROM     dbs;

INSERT INTO @testViews
  EXEC(@sql);

DECLARE EXEC_CURSOR CURSOR
 FOR SELECT * FROM @testViews 
--WHERE selectStmt NOT LIKE '%DB1%';

OPEN EXEC_CURSOR
FETCH NEXT FROM EXEC_CURSOR INTO @stmt;

WHILE (@@FETCH_STATUS = 0)
BEGIN TRY
  PRINT 'Executing: ' + @stmt;
  EXEC(@stmt);
  FETCH NEXT FROM EXEC_CURSOR INTO @stmt;
END TRY
BEGIN CATCH
  PRINT 'Execution failed: ' + @stmt;
  FETCH NEXT FROM EXEC_CURSOR INTO @stmt;
END CATCH

CLOSE EXEC_CURSOR;
DEALLOCATE EXEC_CURSOR;

So what this actually does. First from the CTE I construct a SELECT statement for each database (except the system ones, you can turn them on if you please) which has the following template: 

SELECT 'SELECT TOP 1* FROM '+'[database_name]'+ '.' + 
QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) FROM  
[database_name].sys.views AS t;

So, now you have a SELECT statement per database that constructs TOP 1 * SELECT statements for all the views in that database.

Then we execute the string we generated and output all the SELECT TOP 1 * statements into a table variable. Then we open a cursor and execute the statements row by row. If you want, you can even filter the databases in the cursor by uncommenting the --WHERE selectStmt NOT LIKE '%DB1%'; part.

For the views the execution succeeded no message will appear. For the failed executions an 'Execution failed:' + the view message will appear.

Two things you have to be careful:

1) If you have A LOT of views this will take a long time even if you filter the cursor.

2) You should probably consider outputting the results into a file.

Upvotes: 1

Related Questions