//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Schema Tutorials :: Schema: How do I show the views in a SQL Server database?


Schema: How do I show the views in a SQL Server database?

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)

Related Articles

Schema: How do I list all the indexes in a database?
Schema: How do I list the databases on my server?
Schema: How do I show all the primary keys in a database?
Schema: How do I show all the triggers in a database?
Schema: How do I show the columns for a table?
Schema: How do I show the description property of a column?
Schema: How do I show the parameters for a function or stored procedure?
Schema: How do I show the stored procedures in a database?
Schema: How do I show the tables in a database?
Schema: How do I show the user-defined functions (UDFs) in a database?

 

 


Created: 6/9/2004 | Last Updated: 3/24/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (227)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...