Hello everybody!
All right with you ?
In this post I would like to comment on something very important during the day-to-day of DBA's and Query Developers, which is the database documentation. I hardly see environments where the columns or tables have a clear description of what this bank object is about.
Introduction
For those who create queries all day long, such as BI analysts, this information makes understanding queries and the accuracy of information much easier. To enter this information in the database, we will use a very old feature of SQL Server, but few people use or even know, which is the Extended Property.
Using system procedures, we can describe database objects using their own words to make it easier for others to understand.
Once these descriptions are entered into the database, you can use tools to view this information as you develop your queries or even to generate complete database documentation from Extended Property descriptions.
Unlike some database documentation tools, this feature stores descriptions in the database itself, so that the time taken to register these descriptions is not lost if you intend to change the documentation tool, otherwise these descriptions will be saved. in a safe place with backups (your bank has backup right ?!).
How to document SQL Server databases
For the documentation of SQL Server databases, we will use the feature called Extended Property and the system procedures. sp_addextendedproperty, sp_updateextendedproperty e sp_dropextendedproperty.
In order to use these procedures, the user must be in database roles db_owner or ddl_admin (this role does not allow you to add descriptions for the database itself, users or roles) or have the ALTER / CONTROL privilege on the objects they want to add. the descriptions. And of course, server role users like sysadmin can also use these procedures.
The types of objects that can be documented using Extended Property using these system SP's (@level1type) are: AGGREGATE, DEFAULT, FUNCTION, LOGICAL FILE NAME, PROCEDURE, QUEUE, RULE, SYNONYM, TABLE, TABLE_TYPE, TYPE, VIEW, and XML SCHEMA COLLECTION.
The subtypes of objects that can be documented (@level2type) are: COLUMN, CONSTRAINT, EVENT NOTIFICATION, INDEX, PARAMETER and TRIGGER. Subtypes (@ level2type) depend on the type (@ level1type) to define them, for example, to document a column, where you must define @ level1type = 'TABLE' and @ level2type = 'COLUMN', referring to which table that column is part of.
In order to facilitate the use of these procedures, I will provide here some procedures that will check if the object in question already has Extended Property and, if it has, uses the sp_updateextendedproperty or if not, uses the sp_addextendedproperty.
stpExtendedProperty_Table
View sourcestpExtendedProperty_Column
View sourcestpExtendedProperty_Trigger
View sourcestpExtendedProperty_View
View sourcestpExtendedProperty_Procedure
View sourcestpExtendedProperty_Function
View sourcestpExtendedProperty_User
View sourcestpExtendedProperty_Database
View sourceCan I document objects using an interface?
In addition to allowing you to document your database objects using line of code, you can also add Extended Property metadata using the SQL Server Management Studio (SSMS) interface and is quite simple, although it does not allow automation:
Extended Property Example in a Table
Extended Property Example in a Column
Example of a form for entering metadata and documenting the object in question
How to export already made documentation?
With the script below, you will be able to easily export all Extended Property metadata you have in the desired database. This is useful for scripting and applying to another instance of your environment without having to restore the database to it.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 | SELECT 'EXEC sys.sp_addextendedproperty @name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A WHERE class_desc = N'DATABASE'; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.schemas B ON A.major_id = B.schema_id WHERE A.class_desc = N'SCHEMA'; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.tables A INNER JOIN sys.schemas B ON A.schema_id = B.schema_id INNER JOIN sys.extended_properties C ON A.object_id = C.major_id WHERE C.class = 1 AND C.minor_id = 0 AND ( C.value <> '1' AND C.value <> 1 ); SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''COLUMN'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.columns B ON A.major_id = B.object_id AND A.minor_id = B.column_id INNER JOIN sys.tables C ON A.major_id = C.object_id INNER JOIN sys.schemas D ON C.schema_id = D.schema_id WHERE A.class = 1 AND ( A.value <> '1' AND A.value <> 1 ); SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + D.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.tables A INNER JOIN sys.schemas B ON A.schema_id = B.schema_id INNER JOIN sys.extended_properties C INNER JOIN sys.key_constraints D ON C.major_id = D.object_id ON A.object_id = D.parent_object_id WHERE D.type_desc = N'PRIMARY_KEY_CONSTRAINT'; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + D.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.tables A INNER JOIN sys.schemas B ON A.schema_id = B.schema_id INNER JOIN sys.extended_properties C INNER JOIN sys.key_constraints D ON C.major_id = D.object_id ON A.object_id = D.parent_object_id WHERE D.type_desc = N'UNIQUE_CONSTRAINT' AND ( C.value <> '1' AND C.value <> 1 ); SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''TABLE'', @level1name = [' + D.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.check_constraints B ON A.major_id = B.object_id INNER JOIN sys.schemas C INNER JOIN sys.tables D ON C.schema_id = D.schema_id ON B.parent_object_id = D.object_id; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''INDEX'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(B.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(B.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.indexes A INNER JOIN sys.extended_properties B ON A.object_id = B.major_id AND A.index_id = B.minor_id INNER JOIN sys.tables C INNER JOIN sys.schemas D ON C.schema_id = D.schema_id ON A.object_id = C.object_id WHERE B.class_desc = N'INDEX' AND A.is_primary_key = 0; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.foreign_keys B ON A.major_id = B.object_id INNER JOIN sys.tables C ON B.parent_object_id = C.object_id INNER JOIN sys.schemas D ON C.schema_id = D.schema_id; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + B.name + '], @level1type = ''TABLE'', @level1name = [' + C.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(D.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(D.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.default_constraints A INNER JOIN sys.schemas B INNER JOIN sys.tables C ON B.schema_id = C.schema_id ON A.parent_object_id = C.object_id INNER JOIN sys.extended_properties D ON A.object_id = D.major_id; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''VIEW'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.views B ON A.major_id = B.object_id INNER JOIN sys.schemas C ON B.schema_id = C.schema_id WHERE A.minor_id = 0; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + C.name + '] , @level2type = ''COLUMN'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.columns B ON A.major_id = B.object_id AND A.minor_id = B.column_id INNER JOIN sys.views C ON A.major_id = C.object_id INNER JOIN sys.schemas D ON C.schema_id = D.schema_id WHERE A.class = 1 AND ( A.value <> '1' AND A.value <> 1 ); SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + C.name + '] , @level2type = ''INDEX'', @level2name = [' + A.name + '] ,@name = ''' + REPLACE(CAST(B.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(B.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.indexes A INNER JOIN sys.extended_properties B ON A.object_id = B.major_id AND A.index_id = B.minor_id INNER JOIN sys.views C INNER JOIN sys.schemas D ON C.schema_id = D.schema_id ON A.object_id = C.object_id WHERE B.class_desc = N'INDEX'; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''FUNCTION'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.objects B ON A.major_id = B.object_id INNER JOIN sys.schemas C ON B.schema_id = C.schema_id WHERE B.type_desc LIKE N'%FUNCTION%' AND A.minor_id = 0; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + C.name + '], @level1type = ''PROCEDURE'', @level1name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.procedures B ON A.major_id = B.object_id INNER JOIN sys.schemas C ON B.schema_id = C.schema_id WHERE A.minor_id = 0; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''TRIGGER'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.triggers B ON A.major_id = B.object_id WHERE B.parent_class_desc = N'DATABASE'; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''TABLE'', @level1name = [' + A.name + '] , @level2type = ''TRIGGER'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.tables A INNER JOIN sys.triggers B ON A.object_id = B.parent_id INNER JOIN sys.extended_properties C ON B.object_id = C.major_id INNER JOIN sys.schemas D ON A.schema_id = D.schema_id; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''SCHEMA'', @level0name = [' + D.name + '], @level1type = ''VIEW'', @level1name = [' + A.name + '] , @level2type = ''TRIGGER'', @level2name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(C.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(C.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.views A INNER JOIN sys.triggers B ON A.object_id = B.parent_id INNER JOIN sys.extended_properties C ON B.object_id = C.major_id INNER JOIN sys.schemas D ON A.schema_id = D.schema_id; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''PARTITION FUNCTION'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.partition_functions B ON A.major_id = B.function_id; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''PARTITION SCHEME'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.partition_schemes B ON A.major_id = B.function_id; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + B.name + '] ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.data_spaces B ON A.major_id = B.data_space_id WHERE B.type_desc = 'ROWS_FILEGROUP'; SELECT 'EXEC sys.sp_addextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + C.name + '], @level1type = ''LOGICAL FILE NAME'', @level1name = ' + B.name + ' ,@name = ''' + REPLACE(CAST(A.name AS NVARCHAR(300)), '''', '''''') + ''' ,@value = ''' + REPLACE(CAST(A.value AS NVARCHAR(4000)), '''', '''''') + '''' FROM sys.extended_properties A INNER JOIN sys.database_files B ON A.major_id = B.file_id INNER JOIN sys.data_spaces C ON B.data_space_id = C.data_space_id WHERE A.class_desc = N'DATABASE_FILE'; |
How can I view this information?
And now we come to the most interesting part of the formatting work, which is the visualization of the registered metadata. This view can be of 2 types:
- Official documentation: Document in CHM, HTML, Word or PDF format, where you can view all information, such as tables, Stored Procedures, Views,
etc, and their descriptions - During development: Tools that allow you to view documentation in the SSMS itself, allowing you to quickly access this information as you query and access objects.
Apex SQL Complete
Hardware free which aims to improve (and VERY) the productivity of SQL Server query writing in SSMS and is the main competitor of RedGate SQL Prompt.
One of the features of this tool is that it allows you to view Extended Property metadata of objects while you are programming, as shown below:
RedGate SQL Prompt
Business tool that aims to improve (and VERY) the productivity of writing SQL Server queries in SSMS and is the market leader (rightly) in this segment. It really is a sensational resource, in which I have even made the article SQL Server - Write T-SQL as a Ninja Using Redgate SQL Prompt.
One of the features of this tool is that it allows you to view Extended Property metadata of objects while you are programming, as shown below:
RedGate SQLDoc
Using this commercial RedGate documentation tool, you will be able to generate complete documentation of your databases, with statistical information, indexes, tables, procedures and more technical information about your instance objects and databases, as well as the metadata registered. for you (Extended Property).
Examples:
Dateedo
Using this commercial documentation tool (you can use a version of free, but with limitations), you will be able to generate a complete documentation of your databases, with statistics information, indexes, tables, procedures and a series of technical information about your instance objects and databases, as well as the metadata registered by you ( Extended Property).
Examples:
Want more options?
- ApexSQL Doc (examples)
- Dateedo
- TMD Schema Reporter (examples)
- dbdesc (examples)
- DBScribe 1.4 for SQL Server (examples)
- Document! X
- DOCxPRESS
- LiveDoco
- Red-Gate's SQL Doc (demonstration)
- SchemaToDoc (examples)
- SQLDocKit
- SqlSpec (examples)
- SQL Data Dictionary
- SQLDoc
- SQL Documentation Tool (examples)
- SQL Help Builder
- T-SQL Source Code Unscrambler
- turtle SQL
Conclusion
As you can see, there are several ways to document and describe your database objects. And there are several tools for generating official documentation and also for viewing this information in real time while you are programming.
To cite as an example a real case, in a given company, there was documentation of bank objects, but in Excel spreadsheets shared on the network. This spreadsheet was not up to date, the search for information was very bad, and most people had to constantly search the descriptions of each column in this spreadsheet, generating a huge waste of time from the BI and Development teams.
To improve the productivity of these teams, me and DBA Caroline goltara, we did a market analysis and opted for the Redgate SQL Doc tool as the company's official documentation tool.
We imported, in an automated way, all the texts registered in the spreadsheets into the database (using the SP's shown in this post) and the project was very successful, as this greatly accelerated the teams' productivity, since they used SQL Prompt there , loading the documentation metadata as soon as it was updated in the bank.
In addition, it was made available on a web server, the documentation generated in HTML format, updated and accessible to all.
I hope you enjoyed this post and that it can be helpful to you.
A big hug, see you later.
YOU ARE THE MAN! THANK YOU, MY DEAR!
Thank you, Nissandro! Needing, we are at your orders!
Great post Dirceu!
Really this form of documentation is very useful, very good to share this knowledge and success story.
Hey Carol! Good to see you here .. lol ..
Many thanks for the feedback and help during the project that I mentioned in the post itself. It was thanks to you that he was a success story.
Hug!
Excellent post, Dirceu! This is the kind of tip we hardly find in our research! Congratulations on the didactics and explanation of the subject! Go, go!
Thank you for your visit and feedback, Meirieli 🙂