List Views In Database

Posted on February 25, 2013October 26, 2015 By David Kittell

Simple ListSELECT
    name
FROM
    sys.views
ORDER BY
    name

List With Row CountSET NOCOUNT ON
 
DECLARE   @ViewName AS nVarChar(128)
        , @Query AS nVarChar(500)
 
/* Declare Cursor */
DECLARE Cur_Views CURSOR
    FOR
    SELECT  name
        FROM    [sys].[all_views] x
        WHERE   x.schema_id = 1
 
-- Loop through the views.
OPEN Cur_Views
 
-- Fetch the first view
FETCH NEXT FROM Cur_Views
    INTO      @ViewName
 
WHILE   @@Fetch_Status = 0 BEGIN
    -- Set up our dynamic sql
    SELECT  @Query = 'SELECT ''' + @ViewName + ''' AS Name, COUNT(*) AS [Count] FROM ' + @ViewName
    -- Print the query we're executing for debugging purposes
    -- PRINT @Query
    -- Execute the dynamic query
    EXECUTE(@Query)
 
    -- Fetch subsequent views
    FETCH NEXT FROM Cur_Views
        INTO      @ViewName
 
-- Loop back to the beginning
END -- WHILE    @@Fetch_Status = 0 BEGIN
 
-- Close the cursor
CLOSE Cur_Views
 
-- Dispose of the cursor
DEALLOCATE Cur_Views
 
GO

Originally Posted on February 25, 2013
Last Updated on October 26, 2015

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.

Related

Code MSSQL - Audit