This article will explore various options for obtaining SQL
2005 metadata information.
Introduction
When a SQL Server object is created, its properties are
called metadata. The metadata is stored in special System Tables. For example,
in SQL 2000, when a new column was created, the column name and data type could
be found in an internal System Table called syscolumns. All SQL objects
produce metadata. Every time SQL 2000 Enterprise Manager or SQL 2005 SQL
Server Management Studio is browsed, the information displayed about database, tables,
and all objects, comes from this metadata. There are many uses for this
metadata, including gathering performance statistics, discovering table and
column similarities and differences during a database upgrade, and obtaining
lock information. In previous versions of SQL Server, these System Tables were
exposed and could be queried like any standard table. However, starting with
SQL 2005, System Tables are hidden and they cannot be directly queried. Even
with full DBA rights, System Tables are restricted. Although not directly
accessible, there are built in views and procedures for extracting metadata.
Some of these are new in SQL 2005; others were carried forward from pervious
versions. Most have the advantage of being more readable and self-describing
than querying System Tables. If you have legacy scripts directly referencing
System Tables, there are many new System Views that will directly take their
place.
System Views
System Views are predefined Microsoft created views for
extracting SQL Server metadata. There are over 230 various System Views. To
display all the views in SQL 2005, launch the SQL Management Studio; expand
Databases, System Databases, and select master, Views, System Views.
These System Views will be automatically inserted into any
user created database. The System Views are grouped into several different
schemas. In SQL 2005, schemas are used as security containers. There can be
several different schemas inside a single database. This is a better ANSI
implementation of schemas compared to their use in SQL 2000. See Marcin Policht’s
excellent article; SQL Server 2005 Security, at http://www.databasejournal.com/features/mssql/article.php/3481751
for a detailed explanation of SQL 2005 schemas and security.
Information Schema
The first group of System Views belongs to the Information
Schema set. Information Schema is an ANSI specification for obtaining
metadata. There are twenty different views for displaying most physical
aspects of a database, such as table, column, and view information.
Information Schema views were available in SQL 2000 and
should continue to appear in future versions of SQL. They are a few ANSI terms
that translate differently in SQL. An ANSI "Catalog" is a SQL "Database";
an ANSI "RowVersion" is a SQL "Timestamp"; and an ANSI "Timestamp"
is a SQL "DateTime." Aside from this, Information Schema views are
easy to implement. For an example, we will create a small table with a few
columns.
CREATE DATABASE Test
GO
USE Test
GO
CREATE TABLE MyTable
(
Col1 int,
Col2 varchar(10),
Col3 datetime
)
GO
To use Information Schema views, select them like any
standard view. The following TSQL will display column and table information on
the new database;
SELECT *
FROM INFORMATION_SCHEMA.TABLESSELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘MyTable’
Most of the Information Schema view names are
self-explanatory. INFORMATION_SCHEMA.TABLES returns a row for each table.
INFORMATION_SCHEMA.COLUMNS returns a row for each column. A few though, refer
to ANSI names. INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE contains a row for each
column created with a user-defined type, and INFORMATION_SCHEMA.DOMAIN lists a
row for each user-defined type. INFORMATION_SCHEMA.ROUTINES shows a record for
each stored procedure or function. A benefit to Information Schema views is
that because they are an ANSI standard, you will find them in many other
database packages.
