10 August 2010

SQL Server New Features: 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:

Related Posts Plugin for WordPress, Blogger...

About Me

My photo
Koeln, NRW, Germany
IT Professional with more than 24 years experience in IT in the area of full life-cycle of Web/Desktop/Database Applications Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP implementations & support, Team/Project/IT Management, etc.