That is a great question. I already liked the tool in its previous version and the new release makes it even more useful and advanced. You have the best of two worlds: Dataedo repository is in fact a plain SQL Server database that you can query. If you're looking for something more polished, I've also wrapped this . /*Query all column metadata in a database */SELECT t.name AS [Table Name] ,c.name AS [Column Name] ,MAX(y.name) as [Data Type] ,MAX(c.max_length) AS [Max Length] ,MAX(CASE WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END) AS [Is Nullable] ,MAX(CASE WHEN ep.name = 'Example Values' THEN ep.value ELSE NULL END) AS [Example Values] ,MAX(CASE WHEN ep.name = 'Description' THEN ep.value ELSE NULL END) AS [Description] ,MAX(CASE WHEN ep.name = 'FK To' THEN ep.value ELSE NULL END) AS [FK To] ,MAX(CASE WHEN ep.name = 'Comments' THEN ep.value ELSE NULL END) AS [Comments] ,MAX(CASE WHEN ep.name = 'Display Name' THEN ep.value ELSE NULL END) AS [Display Name] ,MAX(CASE WHEN ep.name = 'Source Table' THEN ep.value ELSE NULL END) AS [Source Table] ,MAX(CASE WHEN ep.name = 'Source Schema' THEN ep.value ELSE NULL END) AS [Source Schema] ,MAX(CASE WHEN ep.name = 'ETL Rules' THEN ep.value ELSE NULL END) AS [ETL Rules] ,MAX(CASE WHEN ep.name = 'SCD Type' THEN ep.value ELSE NULL END) AS [SCD Type] ,MAX(CASE WHEN ep.name = 'Source Field Name' THEN ep.value ELSE NULL END) AS [Source Field Name] ,MAX(CASE WHEN ep.name = 'Source System' THEN ep.value ELSE NULL END) AS [Source System] ,MAX(CASE WHEN ep.name = 'Extraction Transformation Rules' THEN ep.value ELSE NULL END) AS [Extraction Transformation Rules]FROM sys.extended_properties AS epINNER JOIN sys.tables AS t ON ep.major_id = t.object_idINNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_idINNER JOIN sys.types AS y on c.system_type_id = y.system_type_idWHERE class = 1 AND minor_id <> 0GROUP BY t.name ,c.nameORDER BY t.name ,c.name;GO, /*Query all table column extended property metadata in a database */SELECT (t.name) AS [Table Name] ,MAX(CASE WHEN ep.name = 'Display Name' THEN ep.value ELSE NULL END) AS [Display Name] ,MAX(CASE WHEN ep.name = 'Table Description' THEN ep.value ELSE NULL END) AS [Table Description] ,MAX(CASE WHEN ep.name = 'Table Name' THEN ep.value ELSE NULL END) AS [Table Name] ,MAX(CASE WHEN ep.name = 'Table Type' THEN ep.value ELSE NULL END) AS [Table Type] ,MAX(CASE WHEN ep.name = 'Used In Models' THEN ep.value ELSE NULL END) AS [Used In Models] ,MAX(CASE WHEN ep.name = 'View Name' THEN ep.value ELSE NULL END) AS [View Name]FROM sys.extended_properties AS epINNER JOIN sys.tables AS t ON ep.major_id = t.object_idWHERE class = 1 AND minor_id = 0GROUP BY t.nameORDER BY t.name ;GO. Oracle, SQL server, MySQL). 2023 Slashdot Media. it is ready for some serious enterprise applications. You can dump the tables, views, . This script doesn't handle all of my SQL Server object names. List table columns with their foreign keys in SQL Server database. I believe it now stands out even more and, despite being simple, is ready for serious enterprise data governance programs. Get the latest best practices, insight, and product news from our industry experts, Copyright 1999 - 2023 Red Gate Software Ltd. Hello, See: Generate Sql Server data dictionary table Script to build a SQL Server Data Dictionary and report with Microsoft Excel Olaf Helper ----- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich . What if I do not have access to any of those tools? the description will come from the description field where you give any description at the time of Lets have a look first why you should use a database documentation tool. And by using these tools all the information can be gathered and can be used for fetching the information. SQL Server data dictionary generator. Create documents as HTML, PDF, Microsoft Word or Markdown files. You signed in with another tab or window. Is it possible to build the extended properties and then be able to generate reports by using Excel or Reporting Services? Using the code Its not clear what data tables and columns actually hold. The online documentation has a few useful scripts you can run against the repository to modify metadata. 6 Share 1K views 1 year ago SEATTLE This Data Dictionary Generator tool is easier and faster to use than scripts to easily document an existing database schema. Wasn't that easy to manage? Build a SQL Server Analysis Services Data Dictionary Here is the column level Extended Properties for the Data Dictionary. I just uninstalled all the other test installs for every other documenter I have triedthis one wins HANDS DOWN. the only way is to copy the DB locally and run it on it? This gives enables documenting complex environments. Learn more about bidirectional Unicode characters, print '