Sql Query to list all views in an SQL Server 2005 database

Question

I need an sql query to enumerate all views  I only need the view names  of a specific database in SQL Server 2005  Thanks in advance

User · Answer

This is old  but I thought I d put this out anyway since I couldn t find a query that would give me ALL the SQL code from EVERY view I had out there  So here it is      SELECT SM definition   FROM sys sql modules SM   INNER JOIN sys Objects SO ON SM Object id   SO Object id   WHERE SO type    v

User · Answer

Run this adding DatabaseName in where condition     SELECT TABLE NAME  ROW NUMBER   OVER ORDER BY TABLE NAME  AS  RowNumber     FROM INFORMATION SCHEMA VIEWS    WHERE TABLE CATALOG    DatabaseName    or remove where condition adding use     use DataBaseName    SELECT TABLE NAME  ROW NUMBER   OVER ORDER BY TABLE NAME  AS  RowNumber     FROM INFORMATION SCHEMA VIEWS

User · Answer

To finish the set off  with what has already been suggested    SELECT   FROM sys views   This gives extra properties on each view  not available from sys objects  which contains properties common to all types of object  or INFORMATION SCHEMA VIEWS  Though INFORMATION SCHEMA approach does provide the view definition out-of-the-box

User · Answer

Some time you need to access with schema name as an example you are using AdventureWorks Database you need to access with schemas    SELECT s name      v name FROM sys views v inner join sys schemas s on s schema id   v schema id

User · Answer

Necromancing    Since you said ALL views  technically  all answers to date are WRONG   Here is how to get ALL views   SELECT       sch name AS view schema       sysv name AS view name       ISNULL sysm definition  syssm definition  AS view definition       create date       modify date  FROM sys all views AS sysv   INNER JOIN sys schemas AS sch      ON sch schema id   sysv schema id   LEFT JOIN sys sql modules AS sysm      ON sysm object id   sysv object id   LEFT JOIN sys system sql modules AS syssm      ON syssm object id   sysv object id   -- INNER JOIN sys objects AS syso ON syso object id   sysv object id   WHERE  1 1   AND  sysv type    V   -- seems unnecessary  but who knows -- AND sch name    INFORMATION SCHEMA      AND sysv is ms shipped   0   AND NOT EXISTS        SELECT   FROM sys extended properties AS syscrap     WHERE syscrap major id   sysv object id     AND syscrap minor id   0      AND syscrap class   1      AND syscrap name   N microsoft database tools support           ORDER BY       view schema       view name

User · Answer

select v name from INFORMATION SCHEMA VIEWS iv join sys views v on v name   iv Table Name where iv Table Catalog    Your database name

User · Answer

SELECT SCHEMA NAME schema id  AS schema name  name AS view name  OBJECTPROPERTYEX OBJECT ID  IsIndexed   AS IsIndexed  OBJECTPROPERTYEX OBJECT ID  IsIndexable   AS IsIndexable FROM sys views

User · Answer

SELECT    FROM    sys objects WHERE   type    V

[sql] Sql Query to list all views in an SQL Server 2005 database

Examples related to sql

Examples related to sql-server

Examples related to metadata