Using MetadataBy 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:
Books Online has details of each view including a complete description of the result set each view returns. Meta Data FunctionsSQL 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. |
- Advertisement - |