Using Metadata

By Bill Graziano on 24 March 2003 | 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.