How to generate SQL Server data dictionary from SQL script

[@shafiulamin4128]

Dangerously useful mate. Hope it still available in git.

[@nagelfamily]

Great video. Spot on. Seriously good howto President. What could help others is to describe the purpose for the data dictionary.

[@DataResearchLabs]

Good point. I should have done a levelset at the beginning describing what a data dictionary is and why it is useful. Too late for the video (already published, can no longer edit)...but can put it here in the comments: "A data dictionary is a central metadata repository used to provide detailed information about the contents of a dataset or database. It includes data object names and descriptions. It includes data element properties including: data types, size, ordinal position, nullability, etc. It can include Entity-Relationship (ER) Diagrams...or if not that then at least primary key / foreign key / natural or unique key definitions to show how tables related between each other. Data dictionaries are meant to be shared amongst team members enabling faster, more complete knowledge transfer. Data dictionaries are often created as spreadsheets (no different here, just import the final result set out to Excel or Google Sheets). Although beyond the scope of this video and this data dictionary script, data dictionaries can also include system-level diagrams, reference data (static ref table contents), missing data codes (-1 for null, etc.), rejection or quality indicator codes, and business rules.

[@santhanam4565]

Thank you very much, glad that I stumbled upon your channel.

[@MichaelGibson1]

Any quick tips on how to sort out the formatting? The vid jumps over that. Going table by table seems tedious...any quick solutions? Thanks! Great video.

[@matthiasbarmettler8020]

Great video thx a lot! I still struggle to adapt the querry in such a way that the table schema is also queried, would you have any tips?

[@DataResearchLabs]

try modifying the "baseTbl" CTE query, adding in any table-specific columns of information you want. Then carry those new baseTbl.XXXX columns down into the subsequent CTE query "metadata" and also in the bottom select. Hope that helps. Ping if you need more clarification.

[@marceloleoncaceres6826]

Good job!, thanks for sharing it.

[@DataResearchLabs]

You are welcome. :)

[@E1knight]

Thanks Man. Could you please upload more vids for SQL server and dictionary update

[@DataResearchLabs]

Thank you.

[@DataResearchLabs]

Built a tool to automate the script. Video is here: kzbin.info/www/bejne/gpmUZZmBra6UosU

[@oladepoolatunde628]

Great video, it saves me, Please can you show how you format the table on excel?

[@barrysinanan1311]

Really Great, Thanks

[@OdgeBodge]

amazing script just what i needed thank you so much

[@DataResearchLabs]

Glad it helped

[@beerana3822]

@DataResearchLabs Do have you a MSSQL Script to generate Tables, Functions, Stored procedure for PostgreSQL Conversion of MSSQL table, views, functions and stored procedure into postgreSQL

[@hackproof114]

Nice 👍 video brother

[@DataResearchLabs]

Thank you for watching. Am glad it was helpful. :)

[@abhishek12865a]

Hey Thank you but this script is having some errors Like Msg 10757, Level 15, State 1, Line 82 The function 'STRING_AGG' may not have a WITHIN GROUP clause. Msg 10757, Level 15, State 1, Line 95 The function 'STRING_AGG' may not have a WITHIN GROUP clause. Msg 156, Level 15, State 1, Line 115 Incorrect syntax near the keyword 'AS'. Please help

[@sandeepgour2747]

Great video, but I want to only single table data dictionary. This is position by this sql script

[@yayaree01]

Thank you :)

[@DataResearchLabs]

Welcome.

[@jaymob6285]

is there a way to update a description through an update statement?

[@DataResearchLabs]

Yes. :) Checkout this stackoverflow article using "exec sp_addextendedproperty": stackoverflow.com/questions/3754180/adding-a-column-description ...or checkout this simple open source tool I wrote to wrap the script. It has a feature that automatically generates comment scripts for you to manipulate: github.com/DataResearchLabs/data_analysts_toolbox/tree/main/data_dictionary_generator

[@macapagalglaizaangel6818]

Hello can you help me iam currently using the 2022 or 2023 version i am not sure hhahaha and there is an error like the STRING_AGG

[@faizanmajid2922]

How to run this if you are working on SQL Server 2016? String_agg function is not supported on it...

[@DataResearchLabs]

Ahhh! You are correct. This is why others have been having problems. I need to update the documentation to make it clear that MSSQL 2017 or later is need. Thank you for pointing out.

[@DataResearchLabs]

Also, if you have and older version (2016 or prior), use FOR XML PATH approach here: stackoverflow.com/questions/15477743/listagg-in-sqlserver

[@thealchemist6133]

@@DataResearchLabs have you fixed it ? i am still having the same error. Using ssms 2018

[@thealchemist6133]

Hello Faisal, have you managed to modify your script? do you mind sharing it?

[@swatikumari-nu9te]

Gives error the function string_agg may not have a written group clause

[@DataResearchLabs]

Hello Swati. It is very difficult for me to troubleshoot without your exact environment. You've probably already Googled and seen this StackOverflow response for why it might be happening, but if not then please take a look. I am busy over the next several weeks (new videos put on hold), but when I get a chance will review the script to see if I can come up with any more ideas. stackoverflow.com/questions/43611024/issue-with-string-agg-within-group-and-visual-studio

[@dubVintage]

Are you aware of a similar method for documenting SSIS packages?

[@DataResearchLabs]

Hello. No, I have not yet gone looking for SSIS metadata in the system tables. I suspect its there and could be done, just have not tried yet. Here is a link that might help: www.sqlshack.com/how-to-retrieve-information-about-ssis-packages-stored-in-msdb-database/

[@ramnarayans2072]

Git hub link is not accessible.

[@DataResearchLabs]

github.com/DataResearchLabs/sql_scripts/blob/main/data_dictionary_scripts.md works for me. raw.githubusercontent.com/DataResearchLabs/sql_scripts/main/mssql/data_dictionary/data_dict_dump.sql also works. Maybe try again?

[@macapagalglaizaangel6818]

Please help me asap huhuhuhuuu