Accurate documentation is paramount for any system. Unfotunately, when developing a database we sometimes tend to overlook this particular document.

A data dictionary shows metadata about your database. This information can be generated from the DBMS itself using the INFORMATION_SCHEMA compatibility views. Information such as the list of tables, table columns, views, stored procedures, and more can be extracted using the below queries and stored in a document to serve as a snapshot or say, to distribute to developers.

So, for example, to extract the list of tables in the current database, we can execute the following query:

-- tables
SELECT table_catalog, table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name NOT IN ('sysconstraints', 'syssegments')
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY table_catalog, table_schema, table_name;

A list of views can be retrieved by executing:

-- views
SELECT table_catalog, table_schema, table_name AS view_name, is_updatable
FROM INFORMATION_SCHEMA.VIEWS
WHERE table_name NOT IN ('sysconstraints', 'syssegments')
ORDER BY table_catalog, table_schema, table_name;

The table columns and some of the attributes can be retrieved using:

-- table columns
SELECT 
    table_catalog, table_schema, table_name, column_name, ordinal_position, 
    data_type, 
    [length/precision] =
         CASE data_type
             WHEN 'char' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(10), character_maximum_length) END)
             WHEN 'nchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(10), character_maximum_length) END)
             WHEN 'varchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(10), character_maximum_length) END)
             WHEN 'nvarchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(10), character_maximum_length) END)
             WHEN 'numeric'  THEN (CASE ISNULL(numeric_precision, 0) WHEN 0 THEN '' ELSE CONVERT(VARCHAR(10), numeric_precision) + ', ' + CONVERT(VARCHAR(10), numeric_scale) END)
             WHEN 'decimal'  THEN (CASE ISNULL(numeric_precision, 0) WHEN 0 THEN '' ELSE CONVERT(VARCHAR(10), numeric_precision) + ', ' + CONVERT(VARCHAR(10), numeric_scale) END)
             ELSE ''
         END
FROM INFORMATION_SCHEMA.Columns
WHERE table_name NOT IN ('sysconstraints', 'syssegments')
ORDER BY table_catalog, table_schema, table_name, ordinal_position;

Stored procedeures and functions’ information is generated using:

-- stored procedures and functions
SELECT
     routine_catalog, routine_schema, routine_name, routine_type,
     [return_data_type] =
         CASE ISNULL(data_type, '')
             WHEN 'char' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN data_type + ' (MAX)' ELSE data_type + ' (' + CONVERT(VARCHAR(10), character_maximum_length) + ')' END)
             WHEN 'nchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN data_type + ' (MAX)' ELSE data_type + ' (' + CONVERT(VARCHAR(10), character_maximum_length) + ')' END)
             WHEN 'varchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN data_type + ' (MAX)' ELSE data_type + ' (' + CONVERT(VARCHAR(10), character_maximum_length) + ')' END)
             WHEN 'nvarchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN data_type + ' (MAX)' ELSE data_type + ' (' + CONVERT(VARCHAR(10), character_maximum_length) + ')' END)
             ELSE ISNULL(data_type, '')
         END
FROM INFORMATION_SCHEMA.routines
ORDER BY routine_catalog, routine_schema, routine_name;

And the final example will generate the stored procedure and function input and output parameters for the current database:

-- stored procedure and function parameters
SELECT
    specific_catalog, specific_schema, specific_name,
    parameter_name,
    [length/precision] =
         CASE data_type
             WHEN 'char' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(10), character_maximum_length) END)
             WHEN 'nchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(10), character_maximum_length) END)
             WHEN 'varchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(10), character_maximum_length) END)
             WHEN 'nvarchar' THEN (CASE ISNULL(character_maximum_length, 0) WHEN 0 THEN '' WHEN -1 THEN 'MAX' ELSE CONVERT(VARCHAR(10), character_maximum_length) END)
             WHEN 'numeric'  THEN (CASE ISNULL(numeric_precision, 0) WHEN 0 THEN '' ELSE CONVERT(VARCHAR(10), numeric_precision) + ', ' + CONVERT(VARCHAR(10), numeric_scale) END)
             WHEN 'decimal'  THEN (CASE ISNULL(numeric_precision, 0) WHEN 0 THEN '' ELSE CONVERT(VARCHAR(10), numeric_precision) + ', ' + CONVERT(VARCHAR(10), numeric_scale) END)
             ELSE ''
         END,
    ordinal_position, parameter_mode, is_result
FROM INFORMATION_SCHEMA.parameters
ORDER BY specific_catalog, specific_schema, specific_name, ordinal_position;

The below diagram shows the relationships between the INFORMATION_SCHEMA objects.

INFORMATION_SCHEMA compatibility views

For more information about the INFORMATION_SCHEMA compatibility views and which other information can be retrieved can be found in the SQL Server Books Online at SQL Server Books Online.