It is not uncommon to come across scenarios where you were given a database and are required to understand the application and the business logic. The quick and easy way to do this in case of a Sql Server database is to generate a database diagram also called as Entity Relationship Diagram (ERD). ERD gives a very good picture of the database if the database was well-designed and well structured. In most other cases, when the database has huge number of tables or when the data structure is not well defined like missing foreign key references, de-normalized table structure etc, you will have no choice but analyze the data by querying the tables. Sometimes you will need to query the metadata about the tables and columns to deduce certain relations. Did you ever come across a scenario where you wanted to list all the tables in an excel file? You wanted to know all the columns in the database that had a certain string ‘product’ in it? You wanted to know all the primary keys defined. All the unique columns in the database?
In Sql Server, this kind of querying metadata of a database is possible through Information_schema or the system tables.
Information_schema is an open standard formulated as part of Ansi Sql-92 standard and most databases implement it to expose the metadata of a database. The system tables (sys.objects etc) are Sql Server specific meta data storage tables.
Here are a few code samples that demonstrate the usefulness of information_schema.
To list all the tables in a database.
|
select * from information_schema.tables |
List all the views in a database
|
select * from information_schema.tables where table_type=‘view’ |
List all the tables in ‘Northwind’ catalog excluding some of the system tables
|
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = ‘Northwind’ AND TABLE_TYPE = ‘BASE TABLE’ AND TABLE_NAME != ‘dtproperties’ AND TABLE_NAME != ’sysconstraints’ AND TABLE_NAME != ’syssegments’ AND TABLE_NAME != ’sysdiagrams’ ORDER BY TABLE_NAME ASC |
List all the columns in the database that has the word ‘product’ in it.
|
select * from information_schema.columns where column_name=‘%product%’
|
Although information_schema.tables and information_schema.columns views offer most of the information the views do not enumerate most the column level details. This is where the COLUMNPROPERTY helps.
List all columns in the database that are identity fields.
|
SELECT INFORMATION_SCHEMA.COLUMNS.* from INFORMATION_SCHEMA.COLUMNS WHERE (SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, ‘IsIdentity’)) = 1 |
List all columns in the database that are computed fields.
|
SELECT INFORMATION_SCHEMA.COLUMNS.* from information_schema.columns where (SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, ‘IsComputed’))=1 |
There are two more important information_schema views that are useful in retrieving the table constraints, keys and indexes. They are information_schema.table_constraints and information_schema.key_column_usage
|
List all the primary key columns in the database.
SELECT K.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = ‘PRIMARY KEY’ |
List all the foreign key constraints in the catalog.
|
SELECT K.* FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON K.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = ‘FOREIGN KEY’ |
