Using Metadata
By Bill Graziano
on 24 March 2003
| 2 Comments
| Tags: Database Design
The simplest definition I can find for metadata is simply "data about data". SQL Server has a number of different functions that you can use to query your database structure. This articles discusses the Information Schema views and functions such as ObjectProperty and ColumnProperty.
The Information Schema views are part of the SQL-92 standard. We published a
short article on them earlier. The SQL-92 standard defined a number of views that would provide information about the database. For example, there's a view called TABLES that provides information about the tables in a database. You can query it just like any other view. The query
select *
from pubs.information_schema.tables
will return information on all the tables and views in the pubs database:
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
--------------- --------------- ------------------------- ----------
pubs dbo authors BASE TABLE
pubs dbo discounts BASE TABLE
...
pubs dbo titleview VIEW
My result set also included some system objects that are used for replication. These include tables such as sysarticles and syspublications. The TABLE_CATALOG is the database name and TABLE_SCHEMA is the object owner. Be sure to include INFORMATION_SCHEMA as the owner of the view.
Another interesting view is the COLUMNS view. The following query
select TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
from pubs.information_schema.columns
where table_name = 'authors'
will return information about the columns in the authors table:
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH
------------- ------------ ---------- ----------- ---------- ------------------------
pubs dbo authors au_id varchar 11
pubs dbo authors au_lname varchar 40
pubs dbo authors au_fname varchar 20
pubs dbo authors phone char 12
pubs dbo authors address varchar 40
pubs dbo authors city varchar 20
pubs dbo authors state char 2
pubs dbo authors zip char 5
pubs dbo authors contract bit NULL
There's actually quite a bit more information than that but this is all I could fit on the screen. There are columns for column ordinal position, nullability, numeric precision, defaults, character set, sort order and any information on user defined datatypes. Below is a table listing all the INFORMATION_SCHEMA views:
| View Name |
Description |
| CHECK_CONSTRAINTS |
Holds information about constraints in the database |
| COLUMN_DOMAIN_USAGE |
Identifies which columns in which tables are user-defined datatypes |
| COLUMN_PRIVILEGES |
Has one row for each column level permission granted to or by the
current user |
| COLUMNS |
Lists one row for each column in each table or view in the database |
| CONSTRAINT_COLUMN_USAGE |
Lists one row for each column that has a constraint defined on it |
| CONSTRAINT_TABLE_USAGE |
Lists one row for each table that has a constraint defined on it |
| DOMAIN_CONSTRAINTS |
Lists the user-defined datatypes that have rules bound to them |
| DOMAINS |
Lists the user-defined datatypes |
| KEY_COLUMN_USAGE |
Lists one row for each column that's defined as a key |
| PARAMETERS
|
Lists one row for each parameter in a stored procedure or user-defined
function |
| REFERENTIAL_CONSTRAINTS |
Lists one row for each foreign constraint |
| ROUTINES |
Lists one row for each stored procedure or user-defined function |
| ROUTINE_COLUMNS |
Contains one row for each column returned by any table-valued functions |
| SCHEMATA |
Contains one row for each database |
| TABLE_CONSTRAINTS |
Lists one row for each constraint defined in the current database |
| TABLE_PRIVILEGES |
Has one row for each table level permission granted to or by the current
user |
| TABLES |
Lists one row for each table or view in the current database |
| VIEW_COLUMN_USAGE |
Lists one row for each column in a view including the base table of the
column where possible |
| VIEW_TABLE_USAGE |
Lists one row for each table used in a view |
| VIEWS |
Lists one row for each view |
Books Online has details of each view including a complete description of the result set each view returns.
Meta Data Functions
SQL Server also has a number of functions that return information about objects in the database. One that I recently had an opportunity to use is the COLUMNPROPERTY function. Running the following query in Northwind
SELECT COLUMNPROPERTY( OBJECT_ID('Categories'),'CategoryID','IsIdentity')
returns 1 which indicates that CategoryID is an identity column. There are additional functions that return information about the identity column. The ColumnProperty function has quite a few properties it can check including nullability, precision, scale, etc. Many of these are also in the information schema views but some aren't. Books Online has the complete list.
We also used the OBJECT_ID function in that query. Many of these functions only accept an object ID and we use this function to return an object ID given an object name. The OBJECT_NAME function will return the name given an object ID.
Another handy function is the ObjectProperty function. It works like the ColumnProperty but has many more properties it can check. For example, the following query will show you which tables have identities, clustered indexes and primary keys.
select table_name,
IDNTY = objectproperty(object_id(TABLE_NAME), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(TABLE_NAME), 'TableHasClustIndex'),
PK = objectproperty(object_id(TABLE_NAME), 'TableHasPrimaryKey')
from information_schema.tables
where table_type = 'base table'
You can also check properties for whether or not a primary key is a clustered index. Pretty handy on a project where developers can create their own tables. Additional functions include IndexProperty, DatabaseProperty, FileGroupProperty, FullTextProperty and a few others. Books Online has additional information about these.
That's my little tour through some of the meta data functions that SQL Server provides to give you information about what the structure of the database looks like.
