Tags

, ,


CREATE PROCEDURE dbo.showViews 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    SELECT  
        ViewName = TABLE_NAME, 
        Owner = TABLE_SCHEMA, 
        SchemaBound = CASE 
            OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘IsSchemaBound’) 
            WHEN 1 THEN ‘Yes’ 
            WHEN 0 THEN ‘No’ END, 
        Indexed = CASE 
            OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘IsIndexed’) 
            WHEN 1 THEN ‘Yes’ 
            WHEN 0 THEN ‘No’ END 
    FROM 
        INFORMATION_SCHEMA.TABLES 
    WHERE 
        TABLE_TYPE = ‘VIEW’ 
        AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘IsMsShipped’) = 0 
    ORDER BY 
        TABLE_SCHEMA, 
        TABLE_NAME 
END 
GO

 
If you want to view (no pun intended) the definition of an individual view, you can simply say: 
 

EXEC sp_helptext ‘viewname’

 
To get a big script with all the views, you can say: 
 

SELECT 
    Definition = [text] 
FROM 
    syscomments 
WHERE 
    OBJECTPROPERTY(id, ‘IsMsShipped’) = 0 
    AND OBJECTPROPERTY(id, ‘IsView’) = 1 
ORDER BY 
    OBJECT_NAME(id)
Advertisements