Extracting Metadata - Working with Information Schema Views
(Page 15 of 22 )
SQL-92 databases, such as SQL Server and Oracle, provide information schema views as a standard way to provide information about database schemas. These views are normalized, meaning that there are many of them. Information schema views are the best way to access structural information about your database. Accessing system tables directly isn’t a good idea because Microsoft might change the layout. The system stored procedure sp_help provides most of the information, but it’s more difficult to work with and it’s specific to SQL Server. So, the best approach is to use the SQL-92 information schema views.
Understanding SQL-92 Terminology
You’ll find several features in information schemas named in ways quite different from common SQL Server terminology. Table 2-3 shows the information schema and the corresponding SQL Server terms.
Table 2-3. Translating Terminology Between Information Schema and SQL Server Terminology
INFORMATION_SCHEMA | SQL SERVER |
Catalog | Database |
Schema | Owner |
Routine | Stored procedure or function |
Domain | User-defined type |
RowVersion | Timestamp |
Timestamp | DateTime data type |
I’ll stick with SQL Server terminology because it sounds more familiar. The tool that extracts metadata from SQL-92 databases uses the underlying SQL-92 based table and column names to access the data, so you’ll need to be able to translate between the naming if you work with the internals of this tool.
Object names for tables, views, user-defined types, stored procedures, and so on are all defined via a three-part name in information schema views. This name consists of the catalog or database name, schema or owner name, and the object name itself. Most of you are likely to target one database and the dbo in your applications, so I’ll stick with simple object names. If you’re targeting multiple databases or multiple owners, you may need to make changes to the metadata extraction classes to avoid potentially ambiguous naming across different scopes.
Introducing the Specific Schemas
Information schemas are views, not stored procedures. This means you select from them rather than call them. Also, unlike most things you encounter in SQL Server, the information schema views have to be fully qualified, such as this:
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
SCHEMATA is one of the 20 or so information schema views provided in the SQL-92 standard. The SCHEMATA view returns the databases available to the user within the server instance. Table 2-4 summarizes what information each view provides. For the full information on these views and what each returns, refer to the “INFORMATION_SCHEMA” section in the TSQL help you can access though Query Analyzer (or MSDN).
Table 2-4. Individual Information Schema View Contents
| VIEW | DESCRIPTION | COMMENTS |
| SCHEMATA | Contains a row for each database in which the user has permissions | |
| TABLES | Contains a row for each table | |
| TABLE_PRIVELEGES | Contains a row for each table privilege | |
| TABLE_CONSTRAINTS | Contains a rom for each table constraint | See the section "Using Constraints" for a discussion of how constraints work. |
CONSTRAINT_TABLE_USAGE | Similar to TABLE_CONSTRAINTS with less information | Not used in tool. |
| COLUMNS | Contains a row for each column | |
COLUMN_PRIVILEGES | Contains a row for each column privilege | |
| CONSTRAINT_COLUMN_USAGE | Contains a row for each column constraint | |
COLUMN_DOMAIN_USAGE | Contains a row for each column that’s defined as a user-defined type. This information is also contained in COLUMNS | Not used in this metadata extraction. |
| ROUTINES | Contains a row for each stored procedure or function | |
| PARAMETERS | Contains a row for each stored procedure or function parameter | |
| ROUTINE_COLUMNS | Contains a row for each column returned from a table value function | Don’t get excited—this just applies to table value functions. I’ll show you later how to retrieve stored procedure recordset structures. |
| CHECK_CONSTRAINTS | Contains a row for each accessible check constraint | |
REFERENTIAL_CONSTRAINTS | Contains a row for each accessible referential constraint | |
| KEY_COLUMN_USAGE | Contains a row for each column used in an accessible key | |
VIEWS | One row for each accessible view | |
| VIEW_TABLE_USAGE | Contains a row for each accessible table used in a view | Not used in tool. |
| VIEW_COLUMN_USAGE | Contains a row for each column used in each view | Not used in tool. |
| DOMAINS | Contains a row for each accessible user defined type | |
| DOMAIN_CONSTRAINTS | Contains a row for each constraint on an accessible user-defined type | |
In the SQL-92 data extraction tool, I include features you’re likely to use and don’t attempt to incorporate everything from the schema views. For example, I ignore aspects such as character set, collations, and so on. If you’re using these features, you’ll want to extend the tool with a derived class or change the source code.
 This is from Code Generation in Microsoft .NET, by Kathleen Dollard (Apress, ISBN 1590591372). Check it out at your favorite bookstore today. Buy this book now. |
Next: Using Constraints >>
More Database Articles
More By Apress Publishing
/ 17 
