Pages

Wednesday, November 9, 2011

Information Schema Views in SQL Server 2005/2008

Introduction
The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database.  These views can be found in the master database under Views / System Views and be called from any database in your SQL Server instance.  The reason these were developed was so that they are standard across all database platforms.  In SQL 2005 and SQL 2008 these Information Schema views comply with the ISO standard.


In order to retrieve the views available under the INFORMATION_SCHEMA schema and their definitions, could be used the following query: 
-- retrieving objects' definition for INFORMATION_SCHEMA 
SELECT s.name [schema_name] 
, o.name [object_name] 
, sm.definition 
FROM sys.all_sql_modules sm 
    JOIN sys.all_objects o 
       ON sm.object_id = o.object_id 
   JOIN sys.schemas s 
      ON o.schema_id = s.schema_id 
WHERE s.name = 'INFORMATION_SCHEMA' 
ORDER BY o.name 


Find below the query result:


Here is the list of views with information: 
  1. CHECK_CONSTRAINTS: Returns one row for each CHECK constraint
  2. COLUMN_DOMAIN_USAGE: Returns one row for each column that has an alias data type
  3. COLUMN_PRIVILEGES: Returns one row for each column that has a privilege that is either granted to or granted by
  4. COLUMNS: Returns one row for each column
  5. CONSTRAINT_COLUMN_USAGE: Returns one row for each column that has a constraint defined on it
  6. CONSTRAINT_TABLE_USAGE: Returns one row for each table that has a constraint defined on it
  7. DOMAIN_CONSTRAINTS: Returns one row for each alias data type that has a rule bound to it
  8. DOMAINS: Returns one row for each alias data type
  9. KEY_COLUMN_USAGE: Returns one row for each column that is constrained as a key
  10. PARAMETERS: Returns one row for each parameter of a user-defined function or stored procedure
  11. REFERENTIAL_CONSTRAINTS: Returns one row for each FOREIGN KEY constraint
  12. ROUTINES: Returns one row for each stored procedure and function
  13. ROUTINE_COLUMNS: Returns one row for each column returned by table-valued functions
  14. SCHEMATA: Returns one row for each schema
  15. TABLE_CONSTRAINTS: Returns one row for each table constraint
  16. TABLE_PRIVILEGES: Returns one row for each table privilege that is granted to or granted by
  17. TABLES: Returns one row for each table
  18. VIEW_COLUMN_USAGE: Returns one row for each column that is used in a view definition
  19. VIEW_TABLE_USAGE: Returns one row for each table that is used in a view
  20. VIEWS: Returns one row for views



No comments: