Database dictionary

I have a SQL server database, I would like to create a data dictionary, that basically explains what the table and its columns definition. 
What production should I use to help the process?
Thanks

Best Answer

  • ben_bben_b Posts: 83 Silver 2
    edited March 8, 2023 4:43PM Answer ✓
    1.  Run this query, save results to excel and then send to the business analyst to fill in the description

    SELECT TABLE_SCHEMA AS SchemaName,
           TABLE_NAME AS TableName,
           COLUMN_NAME AS ColumnName,
           '' AS [Description goes here]
    FROM 
          INFORMATION_SCHEMA.COLUMNS
    ORDER BY SchemaName,
             TableName,
             ColumnName;<br>


    2.  Once you have the description from the BA, plug them into this query

    exec sp_addextendedproperty  
         @name = N'MS_Description' 
        ,@value = N'Description supplied by the business analyst' 
        ,@level0type = N'Schema', @level0name = 'dbo' 
        ,@level1type = N'Table',  @level1name = 'The table name goes here' 
        ,@level2type = N'Column', @level2name = 'The column name goes here'

    3.  Run SQL doc and it should produce what you need

    This all said, I've not actually tried it myself yet.

Answers

Sign In or Register to comment.