CODEWITHSUNDEEP
sqlsql-serverloopsview
toosweetnitemare
toosweetnitemare

Reputation: 2276

SQL how do i loop through my list of views

Question
How do i loop though my list of views in my SQL DB?

background and things tried
I have googled around for about 2 hours now and i have found quite a few ways to loop through different attributes of my DB. I can loop through a list of tables and a list of columns and a list of counts but i have yet to find anything that allows me to loop through a list of Views.

code to loop through tables i tried to modify

link i used to obtain sample code below

--List all the tables of current database and total no rows in it
EXEC sp_MSForEachTable 'SELECT ''?'' as TableName, COUNT(1) 
as TotalRows FROM ? WITH(NOLOCK)' 

--List all the tables of current database and space used by it EXECUTE sp_MSforeachtable 'EXECUTE sp_spaceused [?];'; 
GO

sudo code for what i want to do

loop through each view in list
run query here for each view
output results

request for help

Can anyone link me to more information about how to do this or provide some sample code? All help is greatly appreciated! Thanks!

Upvotes: 3

Views: 10330

Answers (3)

EBarr
EBarr

Reputation: 12026

There are a lot of ways to do this, cursors, manual loops. There are likely ways to find what you want (meaning the answer to your mystery query) in a set based way. Either way, as phrased you'll need dynamic sql.

Option 1 - a so/so method 

SET NOCOUNT ON;
DECLARE @Views as TABLE (Object_id int, name nvarchar(200));

INSERT INTO @Views (Object_ID, name)
SELECT      Object_ID, name 
FROM        sys.views


DECLARE @viewName   nvarchar(200) = (select top 1 name from @Views);
DECLARE @sql        nvarchar(max) = '';

WHILE(Exists(select 1 from @Views)) BEGIN
    SET @sql = 'select count(*) FROM ' + @ViewName +';'
    --exec(@sql);   --careful you're not accepting user input here!
    Print (@sql);

    DELETE FROM @Views where name = @viewName;
    SET @ViewName = (select top 1 name from @Views);
END;

Option 2 - A More Resilient Method
While trying this out in my little test DB I realized my first solution (as well as all the others) have a potential issue. Views can become out of date --meaning underlying objects change like a column rename. All of the others solutions will throw an error and stop processing. If you're doing large/long queries this may be undesirable. So I added a smidge of error handling to note the error and continue on processing.

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

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

SET          @ViewCount = SCOPE_IDENTITY();

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

    SELECT  @sql = 'select count(*) FROM ' + name +';', @viewName = name
    FROM    @Views
    WHERE    rownum = @Counter;

    BEGIN TRY
        -- exec(@sql); --careful you're not accepting user input here!
        Print (@sql);
    END TRY BEGIN CATCH
        Print ('ERROR querying view - ' + @viewname + ' // ' + ERROR_MESSAGE());
    END CATCH
END;

Upvotes: 3

Vinnie
Vinnie

Reputation: 3929

I am using ROW_NUMBER() and the INFORMATION_SCHEMA to iterate through the views.

This is a quick look at the work list:

SELECT TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME AS ViewName
    ,ROW_NUMBER() OVER (
        ORDER BY TABLE_CATALOG
            ,TABLE_SCHEMA
            ,TABLE_NAME
        )
FROM INFORMATION_SCHEMA.VIEWS

Here is the stored proc:

CREATE PROCEDURE LoopThroughViews
AS
DECLARE @sql VARCHAR(max)
    ,@tableCount INT
    ,@i INT = 0

SELECT @tableCount = COUNT(*)
FROM INFORMATION_SCHEMA.VIEWS

WHILE (@tableCount >= @i)
BEGIN
    SELECT @sql = 'select * from ' + ViewName
    FROM (
        SELECT TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME AS ViewName
            ,ROW_NUMBER() OVER (
                ORDER BY TABLE_CATALOG
                    ,TABLE_SCHEMA
                    ,TABLE_NAME
                ) AS ViewNum
        FROM INFORMATION_SCHEMA.VIEWS
        ) x
    WHERE @i = ViewNum

    EXEC(@sql)

    SET @i = @i + 1
END

And finally the call:

EXEC LoopThroughViews

Upvotes: 2

Lamak
Lamak

Reputation: 70638

One way to do this is using cursors. And you are definitely gonna need dynamic SQL, so first go to this link. Then, you can try the following:

DECLARE @View VARCHAR(200), @Query VARCHAR(MAX)


DECLARE YourView CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT name
FROM sys.views

OPEN YourView
FETCH NEXT FROM YourView INTO @View
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Query = 'SELECT '''+@View+''' ViewName, COUNT(1) Rows
                  FROM '+QUOTENAME(@View)  -- Your code here
    PRINT(@Query)
    FETCH NEXT FROM YourView INTO @View
END
CLOSE YourView
DEALLOCATE YourView

Upvotes: 3

Related Questions