SQL Server ANSI Information Schema Views
SQL Server ANSI Information Schema Views
Based on the system tables, a set of ANSI views are defined in each database and inherited from the model database; they extract a limited set of information from the database system tables.
Each view has the owner INFORMATION_SCHEMA, and the owner must be referenced to identify the view. For example, to see a list of all the tables in a database, you query the TABLES view with :
SELECT * FROM INFORMATION_SCHEMA.TABLES
The advantage of the ANSI view is that it returns a table that you can manipulate; the disadvantage is that the owner name must be referenced, make the typing rather tedious. Also, the ANSI information schema views do not return as extensive a set of information as the built-in system stored procedures, such as SP_HELP or SP_HELPCONSTRAINT.
Written by,
Kasim Wirama, MCSD.NET, MCDBA
Database Administrator