Prethia Prethia - 3 months ago 12
SQL Question

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.

Answer

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.