09 August 2010

SQL Server 2008 Information Schema Views

    In the last post, Database Object Dependencies – An introduction, I was talking about metadata, in common understanding defined as “data about data”, and its meaning in the context of databases as “information (data) stored about data, the structures or objects related to data”. The SQL92, SQL99, SQL2003, and I suppose also SQL2008 and the next coming ISO standard document versions, define an information schema called INFORMATION_SCHEMA supposed to contain a minimum of metadata about a database’s underlying objects, namely view descriptors, one base table descriptor and several domain descriptors [1]. The information schema is independent from databases’ internal system metadata implementations, fact that makes it a valuable approach in retrieving common metadata, at least from this perspective being possible to write vendor independent code as long the vendor adheres and implements the ISO/IEC 9075 standard(s).

    SQL Server 2008 makes available the Information Schema Views through INFORMATION_SCHEMA schema available in each database storing information about all database objects contained in the respective database. 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

INFORMATION_SCHEMA definitions

    Unfortunately there are no descriptions stored together with the database objects, and even if most of the view names are self-describing, there are a few cases in which more information are required. Here is the list again with the views and their descriptions together with the link to the MSDN section that gives more information on view’s definition:
No View Description

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


       As stated above, the information schema is available for each database, therefore the information schema views return only the records available in the current database to which the current user has access.

Notes:
1. Above their informative character, the INFORMATION_SCHEMA definitions could be used in order to understand more about the systems tables used as source for the respective views and the relations between them.
2. Microsoft recommends not to use the INFORMATION_SCHEMA views in order to determine the schema of an object and use instead the sys.objects system catalog view [2], warning that appears in several pages from information schema documentation.
3. As it seems the SQL Server 2008 doesn’t implement all the views defined in information schema, for example ASSERTIONS, CHARACTER_SETS, COLLATIONS, TRANSLATIONS, SQL_LANGUAGES, etc. It’s also interesting to note that the respective information are available in .Net through the OleDbSchemaGuid class (see also this).

References:
[1] ISO/IEC. (1992) ISO/IEC 9075:1992,  Database Language SQL. [Online] Available from: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (Accessed: 8 August 2010)
[2] MSDN. (2010). Querying the SQL Server System Catalog. [Online] Available from: http://msdn.microsoft.com/en-us/library/ms189082.aspx (Accessed: 9 August 2010)

No comments: