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
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:
Post a Comment