SQL Server – Capturing Table and Column metadata and description – Building Data Dictionary Part 2

In the first part of this series on Building a Data Dictionary for a SQL Server Database, we saw how to build a database diagram/data model for a SQL Server database. In this second part, we will take a look at how to capture the following information:

  • List of Schemas and additional information about each Schema.
  • List of Tables and additional information/description about each Table.
  • List of Columns in each Table with following information about each Column:
    • Column Name
    • Data Type
    • Null Option
    • Primary Key Indicator (Is PK)
    • Foreign Key Indicator (Is FK)
    • Description about the Column
    • Any other additional information

Before we get into the T-SQL Scripts to get the table and column metadata and descriptions, let’s understand a few things here.

As we all know that the list of tables and list of columns in each table along with each column’s properties like Data Type, and Null Option etc. are stored in the system tables when the tables/columns are created/modified and they can be easily queried using various techniques like by querying INFORMATION_SCHEMA Views, Catalog Views etc. These information is either mandatory while creating/modifying tables/columns or these have default values.

However, when it comes to table and column descriptions, there is no default value assigned to descriptions and hence it needs to be provided explicitly at the time of creating the objects. These are called as Extended Properties. Following are the 3 main Stored Procedures used in SQL Server to add/delete/update an extended property associated with a schema, object, and attribute etc. in an SQL Server database:

Following is a Function and a View to retrieve/view the extended properties associated with an object:

Following is a sample of adding an extended property (Description) to ContactID column in Person.Contact table in AdventureWorks database.

EXEC sys.sp_addextendedproperty
    @name = N’MS_Description’
    , @value = N’Primary key for Contact records.’
    , @level0type = N’SCHEMA’
    , @level0name = N’Person’
    , @level1type = N’TABLE’
    , @level1name = N’Contact’
    , @level2type = N’COLUMN’
    , @level2name = N’ContactID’
GO

On the similar lines, extended properties associated with a database object can be modified/deleted using the above mentioned stored procedures. For more information on extended properties, read this and related msdn articles.

As mentioned above, note that, if the extended properties (Descriptions) are not added to a database object, then the descriptions which we are going to retrieve will be empty/null.

Following is a simple query which gives us the list of schemas in a database along with the description associated with each schema:

SELECT
    [sSCH].[name] AS [SchemaName]
    , [sEXP].[value] AS [Description]
FROM
    [sys].[schemas] AS [sSCH]
    LEFT JOIN [sys].[extended_properties] AS [sEXP]
        ON [sSCH].[schema_id] = [sEXP].[major_id]
        AND [sEXP].[class] = 3
        AND [sEXP].[minor_id] = 0
        AND [sEXP].[name] = N’MS_Description’
WHERE [sSCH].[schema_id] > 4 AND [sSCH].[principal_id] = 1
ORDER BY [SchemaName]
GO

Following is the output of this query.

Database Schemas and Descriptions

Note that, in the above query we have excluded the dbo schema using the WHERE clause as it is the default schema and we are more interested in the user defined schemas.

Following query gives us the list of tables (SchemaName.TableName format) along with the description associated with each table.

SELECT
    SCHEMA_NAME([sOBJ].[schema_id]) + ‘.’ + [sOBJ].[name] AS [ObjectName]
    , [sEXP].[value] AS [ObjectDescription]
FROM
    [sys].[objects] AS [sOBJ]
    LEFT JOIN [sys].[extended_properties] AS [sEXP]
        ON [sOBJ].[object_id] = [sEXP].[major_id]
        AND [sEXP].[minor_id] = 0
        AND [sEXP].[name] = N’MS_Description’
WHERE [sOBJ].[type] = ‘U’
ORDER BY [ObjectName]
GO

Here is the output of the above query.

Database Tables and Descriptions

Now that we have got the list of Schemas, Tables in each Schema, and Description associated with each of the Schemas/Tables, it’s time to get to the next level and get the information related to Columns.

Following query gives the list of Columns and it’s properties like Data Type, Null Option, Primary and Foreign Key Indicators, and Description.

SELECT
    [sCOL].[name] AS [ColumnName]
    , CASE
        WHEN [sTYP].[name] IN (‘char’,’varchar’,’nchar’,’nvarchar’,’binary’,’varbinary’)
            THEN [sTYP].[name] + ‘(‘ + CAST([sCOL].[max_length] AS VARCHAR(10)) + ‘)’
        WHEN [sTYP].[name] IN (‘float’,’decimal’,’numeric’,’real’)
            THEN [sTYP].[name] + ‘(‘ + CAST([sCOL].[precision] AS VARCHAR(10)) + ‘,’ + CAST([sCOL].[scale] AS VARCHAR(10)) + ‘)’
        ELSE [sTYP].[name]
      END AS [DataType]
    , CASE [sCOL].[is_nullable]
        WHEN 0x1 THEN ‘Yes’
        ELSE ‘No’
      END AS [IsNullable]
    , CASE
        WHEN [IdxDtls].[column_id] IS NOT NULL THEN ‘Yes’
        ELSE ‘No’
      END AS [IsPK]
    , CASE
        WHEN [sFKC].[parent_column_id] IS NOT NULL THEN ‘Yes’
        ELSE ‘No’
      END AS [IsFK]
    , [sEXP].[value] AS [ColumnDescription]
FROM
    [sys].[objects] AS [sOBJ]
    INNER JOIN [sys].[columns] AS [sCOL]
        ON [sOBJ].[object_id] = [sCOL].[object_id]
    LEFT JOIN [sys].[types] AS [sTYP]
        ON [sCOL].[user_type_id] = [sTYP].[user_type_id]
    LEFT JOIN (
        SELECT [sIDX].[object_id], [sIXC].[column_id]
        FROM
            [sys].[indexes] AS [sIDX]
            INNER JOIN [sys].[index_columns] AS [sIXC]
                ON [sIDX].[object_id] = [sIXC].[object_id]
                AND [sIDX].[index_id] = [sIXC].[index_id]
        WHERE [sIDX].[is_primary_key] = 0x1
    ) AS [IdxDtls]
        ON [sCOL].[object_id] = [IdxDtls].[object_id]
        AND [sCOL].[column_id] = [IdxDtls].[column_id]
    LEFT JOIN [sys].[foreign_key_columns] AS [sFKC]
        ON [sCOL].[object_id] = [sFKC].[parent_object_id]
        AND [sCOL].[column_id] = [sFKC].[parent_column_id]
    LEFT JOIN [sys].[extended_properties] AS [sEXP]
        ON [sOBJ].[object_id] = [sEXP].[major_id]
        AND [sCOL].[column_id] = [sEXP].[minor_id]
        AND [sEXP].[class] = 1
        AND [sEXP].[minor_id] > 0
        AND [sEXP].[name] = N’MS_Description’
WHERE
    [sOBJ].[type] = ‘U’
    AND SCHEMA_NAME([sOBJ].[schema_id]) = N’Sales’
    AND [sOBJ].[name] = N’SalesOrderDetail’
ORDER BY [ColumnName]
GO

Here is the output of the above query.

Table Columns and Descriptions

Note that, in the above query we have restricted the table to “Sales.SalesOrderDetail”. While generating the entire data dictionary, you can remove those restrictions and generate it for all the columns (of course you need to add the table name in the select list:-))

In the next and final part of this series, we will build the appendix which will contain additional details like Views, Functions, and other objects.

Advertisements

About Dattatrey Sindol (Datta)

Datta is a Microsoft BI Enthusiast, passionate developer, and a blogger. View Full Profile

Posted on April 16, 2012, in Documentation, SQL Server and tagged , . Bookmark the permalink. 12 Comments.

  1. Hi Dattatrey,

    I have read, and enjoyed your post.

    Please could I get permission from you to list your query (the first one up from the end of the post) on my blog (datablazeit.wordpress.com)? I’ll cite your name, and blog, as the reference, indicating that I obtained the code from you.

    Thanks in advance.

  2. Hi Drikus,

    I am glad you liked it.
    Sure. Please go ahead and use it. As long as the necessary links/credit is povided, it should be fine.

    Best Regards,
    Datta

  3. Hi Dattatrey
    Great article and very helpful but just one thing. I am not able to determine the character between the 0 and the 1 in your second case statement for the last query. Can you tell me what it is?

    CASE [sCOL].[is_nullable]
    WHEN 0×1 THEN ‘Yes’
    ELSE ‘No’
    END AS [IsNullable]

  4. Hi Doug,

    Thank you. I am glad you liked it.
    It’s actually “x” (small letter “x”). Since we are checking for a bit column, we are representing the value as “0x1” instead of “1”. “0x1” means true and “0x0” means false.

    Hope that answers your question.

    Best Regards,
    Dattatrey Sindol (Datta)

  5. @ Doug (and Dattatrey too)

    When you paste the code directly into the SQL editor, what should come in as a lower-case x actually comes in as an extended-character multiplication sign which *looks* like an x. The hint is that the extended-character symbol is raised above the normal text base-line.

    Look carefully: × versus x

  6. Good post, thanks.

    I also had issues copy/pasting to SSMS, the single quote characters had also been replaced with fancy opening and closing single quotes.

    Also, the length value for varchar(max) and nvarchar(max) was coming through as -1 as that is how it is stored. A simple replace takes care of that:

    WHEN [sTYP].[name] IN (‘char’,’varchar’,’nchar’,’nvarchar’,’binary’,’varbinary’)
    THEN [sTYP].[name] + ‘(‘ + replace(CAST([sCOL].[max_length] AS VARCHAR(10)),’-1′,’max’) + ‘)’

  7. I am attempting to read from a table named “bfEvent”
    where I want to read the value of one particular
    field (@Field) from one particular row (where EventID
    = 16), along with the associated Column Name, Data
    Type, and Max Length of that field.

    This is the basic idea of what I’m looking for, although
    of course it does not work.

    How to do this? Any ideas?

    declare @Field varchar(50)
    declare @EventID int

    — THESE VALUES PASSED INTO STORED PROC…
    set @Field = ‘AvatarDeadline’
    set @EventID = 16

    select isc.Column_Name
    , isc.Data_Type
    , isc.Characater_Maximum_Length
    , evt.@Field as Value
    from INFORMATION_SCHEMA.COLUMNS isc
    where isc.Table_Name = ‘bfEvent’
    and isc.Column_Name = @Field
    and isc.Column_Name = evt.@Field

    • Ack. No edit button 😦 Here’s corrected code:

      I am attempting to read from a table named “bfEvent”
      where I want to read the value of one particular
      field (@Field) from one particular row (where EventID
      = 16), along with the associated Column Name, Data
      Type, and Max Length of that field.

      This is the basic idea of what I’m looking for, although
      of course it does not work.

      How to do this? Any ideas?

      declare @Field varchar(50)
      declare @EventID int

      — THESE VALUES PASSED INTO STORED PROC…
      set @Field = ‘AvatarDeadline’
      set @EventID = 16

      select isc.Column_Name
      , isc.Data_Type
      , isc.Characater_Maximum_Length
      , evt.@Field as Value
      from INFORMATION_SCHEMA.COLUMNS isc
      where isc.Table_Name = ‘bfEvent’
      and isc.Column_Name = @Field
      and isc.Column_Name = evt.@Field
      and evt.EventID = @EventID

      The table looks like:
      bfEvent
      =======
      EventID int
      EventName varchar(50)
      EventDate datetime
      AvatarDeadline datetime
      SomeText text

  8. The schema, table, and column traversal code is awesome and just what I was looking for. Thank you; you’ve saved me hours of work :o)

  1. Pingback: SQL Server – Building the Appendix – Building Data Dictionary Part 3 « Datta's Ramblings on Business Intelligence 'N' Life

  2. Pingback: Visual Studio 2012 Database Project (SSDT): Working with a Database Project « Data Blaze Information Technology

What are your thoughts?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s