RSS

SQL Server – Building the Appendix – Building Data Dictionary Part 3

19 Apr

In the first and second part of this series on Building a Data Dictionary for a SQL Server Database, we saw how to build a database diagram, capture the information related to tables and columns and the metadata and descriptions associated with them. In this third and last part of the series, we will build the appendix which will contain the following information:

  • Views
  • User Defined Types
  • User Defined Functions
  • Stored Procedures

Capturing the View Details

Following query gives you the list of Database Views along with the descriptions for each one of them.

SELECT
    SCHEMA_NAME([sOBJ].[schema_id]) + ‘.’ + [sOBJ].[name] AS [ViewName]
    , [sEXP].[value] AS [ViewDescription]
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] = ‘V’
ORDER BY [ViewName]
GO

Here is the output of the above query.

List of Views and Descriptions

Capturing the User Defined Type Details

Following query gives the list of user defined types along with their descriptions.

SELECT
    SCHEMA_NAME([sTYP].[schema_id]) + ‘.’ + [sTYP].[name] AS [TypeName]
    , [sEXP].[value] AS [TypeDescription]
FROM
    [sys].[types] AS [sTYP]
    LEFT JOIN [sys].[extended_properties] AS [sEXP]
        ON [sTYP].[user_type_id] = [sEXP].[major_id]
        AND [sEXP].[minor_id] = 0
        AND [sEXP].[name] = N’MS_Description’
WHERE [sTYP].[is_user_defined] = 0x1
ORDER BY [TypeName]
GO

Here is the output of the above query.

List of Types and Descriptions

Note that, in my copy of AdventureWorks, there was no description available for these user defined types, so for the purpose of this demonstration, I added the description using the sp_addextendedproperty Stored Procedure.

Capturing the User Defined Function Details

Below query gives the list of following types of functions along with their description:

  • AF: Aggregate function (CLR)
  • FN: SQL scalar function
  • FS: Assembly (CLR) scalar-function
  • FT: Assembly (CLR) table-valued function
  • IF: SQL inline table-valued function
  • TF: SQL table-valued-function

SELECT
    SCHEMA_NAME([sOBJ].[schema_id]) + ‘.’ + [sOBJ].[name] AS [FunctionName]
    , CASE [sOBJ].[type]
        WHEN ‘AF’ THEN ‘Aggregate function (CLR)’
        WHEN ‘FN’ THEN ‘SQL scalar function’
        WHEN ‘FS’ THEN ‘Assembly (CLR) scalar-function’
        WHEN ‘FT’ THEN ‘Assembly (CLR) table-valued function’
        WHEN ‘IF’ THEN ‘SQL inline table-valued function’
        WHEN ‘TF’ THEN ‘SQL table-valued-function’
      END AS [FunctionType]
    , [sEXP].[value] AS [FunctionDescription]
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] IN (‘AF’,’FN’,’FS’,’FT’,’IF’,’TF’)
ORDER BY [FunctionName]
GO

Here is the output of the above query.

List of Functions and Descriptions

Capturing the Stored Procedure Details

Here is the query to capture the List of Stored Procedures and the corresponding descriptions.

SELECT
    SCHEMA_NAME([sOBJ].[schema_id]) + ‘.’ + [sOBJ].[name] AS [ProcedureName]
    , CASE [sOBJ].[type]
        WHEN ‘P’ THEN ‘SQL Stored Procedure’
        WHEN ‘PC’ THEN ‘Assembly (CLR) stored-procedure’
      END AS [ProcedureType]
    , [sEXP].[value] AS [ProcedureDescription]
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] IN (‘P’,’PC’)
ORDER BY [ProcedureName]
GO

Following is the output of the above query.

List of Procedures and Descriptions

Apart from the information that we have captured in this part of the series to build the appendix, there can be various other details which you/your team might want to capture like Extended Properties associated with Stored Procedure Parameters, XML Schema Collections etc. which we have not covered in this series.

I have created a sample data dictionary based on the AdventureWorks database for the purpose of this demonstration and the same can be downloaded from my Sky Drive.

Please let me know your feedback on this series by leaving a comment below.

Note: The output displayed in these 3 parts of the series might slightly differ from the output you might get due to the changes that either your or my copy of AdventureWorks database might have undergone certain changes, which we might have done for testing purposes.

About these ads
 
 

Tags: ,

One response to “SQL Server – Building the Appendix – Building Data Dictionary Part 3

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