Database dictionary

rgfriend

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

ben_b

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.

ben_b

Hello

the below link explains how you can use sp_addextendedproperty within SQL server to add explanations to column and tables and store this information in the database... You can then use red-gate SQL doc to use that information to generate a data dictionary in Word/PDF/HTML

https://www.red-gate.com/hub/product-learning/sql-doc/documenting-a-sql-server-data-catalog-in-html-and-git-markdown?product=sql-doc

One potential alternative tool is Microsoft Purview - it should mean that you can add table/column descriptions using a web UI (as asset descriptions within Purview Data Catalog)... although I have not actually used Purview yet so remains unproven to me as of today.

cheers
Ben

rgfriend

Thank you.
Can I generate the doc with table and columns description field empty in a word document, and asked someone like business analyst to work on wording of the description of the table or column, then somehow load into the database, and regenerate the SQL doc?

Thanks,