There is a lot of information held in SQL Server metadata about your SQL Server procedures, triggers, views and functions. Some of it is valuable, other nuggets are useful and a few are rather dull but worthy. It really all needs to be tied together to tell the full story, especially if you are not able to, or do not want to, query the metadata directly. Until JSON arrived, this information could be viewed but it was all a bit tedious to get and display because it was hierarchical in nature. Now we can do a lot better in getting all this information about our database’s routines. (Routines, including triggers, views, procedures, Replication filter procedures, rules, defaults and functions, are also referred to as modules). Once we have the information, there are plenty of ways of inspecting it or getting reports from it.
We know from the metadata the identity of the other routines that use our routine, the objects that it references, its settings, parameters, return value, the table source it produces, any documentation in the extended properties. It is great to know all this when you are developing or maintaining SQL Server code. Over the years, I’ve used various approaches to getting all this information. Nowadays, I generally get a JSON document that has all the routines I’m interested in.
Here is an example, using AdventureWorks2016, where I’ve got the details of just one of each type of module contained there. The descriptions of the routines and the inline comments on the columns, and parameters come from the MS_Description extended property
|
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 |
[ { "Name":"dbo.uspPrintError", "Type":"sql stored procedure (P)", "Description":"Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.", "Dates":{ "Created":"2017-10-16T15:52:22.977", "Last_Modified":"2017-10-16T15:52:22.977" }, "Config":{ "Ansi nulls":"true", "Quoted identifier":"true" }, "ReferencedBy":[ "Sales.uSalesOrderHeader", "Purchasing.dVendor", "Production.iWorkOrder", "Production.uWorkOrder", "dbo.uspLogError", "Purchasing.iPurchaseOrderDetail", "Purchasing.uPurchaseOrderDetail", "Purchasing.uPurchaseOrderHeader", "Sales.iduSalesOrderDetail" ] }, { "Name":"dbo.ufnLeadingZeros", "Type":"sql scalar function (FN)", "Description":"Scalar function used by the Sales.Customer table to help set the account number.", "Dates":{ "Created":"2017-10-16T15:52:23", "Last_Modified":"2017-10-16T15:52:23" }, "Properties":[ "IsDeterministic", "IsPrecise", "IsSystemVerified" ], "Config":{ "Ansi nulls":"true", "Quoted identifier":"true", "Schema-bound,":"true", "Database collation":"true" }, "Parameters":[ "@Value int -- Input parameter for the scalar function ufnLeadingZeros. Enter a valid integer." ], "Returned":[ " varchar (8)" ], "ReferencedBy":[ "Sales.Customer.AccountNumber" ] }, { "Name":"Person.iuPerson", "Type":"sql trigger (TR)", "Parent":"Person.Person", "Description":"AFTER INSERT, UPDATE trigger inserting Individual only if the Customer does not exist in the Store table and setting the ModifiedDate column in the Person table to the current date.", "Dates":{ "Created":"2017-10-16T15:52:35.017", "Last_Modified":"2017-10-16T15:52:35.017" }, "Config":{ "Ansi nulls":"true", "Quoted identifier":"true" }, "Referencing":[ "inserted.Demographics.exist", "inserted", "Person.Person" ] }, { "Name":"Person.vAdditionalContactInfo", "Type":"view (V)", "Description":"Displays the contact name and content from each element in the xml column AdditionalContactInfo for that person.", "Dates":{ "Created":"2017-10-16T15:52:35.060", "Last_Modified":"2017-10-16T15:52:35.060" }, "Properties":[ "SystemDataAccess", "UserDataAccess" ], "Config":{ "Ansi nulls":"true", "Quoted identifier":"true" }, "Columns":[ "BusinessEntityID int ", "FirstName Name ", "MiddleName Name ", "LastName Name ", "TelephoneNumber nvarchar (50)", "TelephoneSpecialInstructions nvarchar (MAX)", "Street nvarchar (50)", "City nvarchar (50)", "StateProvince nvarchar (50)", "PostalCode nvarchar (50)", "CountryRegion nvarchar (50)", "HomeAddressSpecialInstructions nvarchar (MAX)", "EMailAddress nvarchar (128)", "EMailSpecialInstructions nvarchar (MAX)", "EMailTelephoneNumber nvarchar (50)", "rowguid uniqueidentifier ", "ModifiedDate datetime " ], "Referencing":[ "Person.Person", "ContactInfo.ref.value" ] }, { "Name":"dbo.ufnGetContactInformation", "Type":"sql table valued function (TF)", "Description":"Table value function returning the first name, last name, job title and contact type for a given contact.", "Dates":{ "Created":"2017-10-16T15:52:35.217", "Last_Modified":"2017-10-16T15:52:35.217" }, "Properties":[ "SystemDataAccess", "UserDataAccess" ], "Config":{ "Ansi nulls":"true", "Quoted identifier":"true", "Database collation":"true" }, "Columns":[ "PersonID int ", "FirstName nvarchar (50)", "LastName nvarchar (50)", "JobTitle nvarchar (50)", "BusinessEntityType nvarchar (50)" ], "Parameters":[ "@PersonID int -- Input parameter for the table value function ufnGetContactInformation. Enter a valid PersonID from the Person.Contact table." ], "Referencing":[ "Person.BusinessEntityContact", "Person.ContactType", "Sales.Customer", "HumanResources.Employee", "Person.Person", "Sales.Store", "Purchasing.Vendor" ] } ] |
This is very useful for documentation purposes, of course and the information about each routine would normally be added to the header of the source code for that routine. To do this, it is often better for visibility to convert it to YAML which doesn’t have to be ‘beautified’ to make it readable and indented. It is also easy to pop it into a JSON-oriented database such as MongoDB to get reports on your code.
The JSON data above is easily queried to provide reports. This simple code, for example …
|
1 2 3 4 |
SELECT Name, type, Description FROM OpenJson(@json) WITH (Name NVARCHAR(80) '$.Name', Type nvarchar(20) '$.Type', Description NVARCHAR(MAX) '$.Description') |
… would produce this. The @json variable contains the JSON data above.
How do we get this JSON? It is also easy to get started with this sort of thing. If we do this:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT Object_Schema_Name(object.object_id) + '.' + object.name AS "Name", Lower(Replace(object.type_desc, '_', ' ')) + ' (' + RTrim(object.type) + ')' AS "Type", Object_Schema_Name(object.parent_object_id) + '.' + Object_Name(object.parent_object_id) AS "Parent", Convert(NVARCHAR(2000), value) AS "Description" FROM sys.objects AS object LEFT OUTER JOIN sys.extended_properties AS EP ON object.object_id = EP.major_id AND class = 1 AND minor_id = 0 WHERE is_ms_shipped = 0 FOR JSON AUTO; |
we get something like this (first six records only)
|
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 |
[ { "Name":"Person.DF_Person_ModifiedDate", "Type":"default constraint (D)", "Parent":"Person.Person", "Description":"Default constraint value of GETDATE()" }, { "Name":"Sales.uSalesOrderHeader", "Type":"sql trigger (TR)", "Parent":"Sales.SalesOrderHeader", "Description":"AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables." }, { "Name":"Sales.PK_SalesPerson_BusinessEntityID", "Type":"primary key constraint (PK)", "Parent":"Sales.SalesPerson", "Description":"Primary key (clustered) constraint" }, { "Name":"Sales.SalesTaxRate", "Type":"user table (U)", "Description":"Tax rate lookup table." }, { "Name":"Person.CK_Person_EmailPromotion", "Type":"check constraint (C)", "Parent":"Person.Person", "Description":"Check constraint [EmailPromotion] >= (0) AND [EmailPromotion] <= (2)" }, { "Name":"Purchasing.dVendor", "Type":"sql trigger (TR)", "Parent":"Purchasing.Vendor", "Description":"INSTEAD OF DELETE trigger which keeps Vendors from being deleted." } ] |
There is a lot more than this that I want to know, though. This article is really about how to drill in further into the detail. The code that I generally use to generate the JSON report of a database’s routines is a single, rather large, query or temporary stored procedure. I do this because it doesn’t require the database to be modified. Here in this article I break it into manageable Table-valued functions that can be ‘inlined’. The problem with this is that they aren’t really designed for this sort of work because they have to be created in the database you are documenting and deleted after use. They therefore have to be eliminated from the set of modules being documented. You can use temporary stored procedures but not functions. I generally gloop all the code into a rather terrifying query, because I’ll be doing it as a repeatable script in PowerShell. It leaves no trace in the database being documented, of course. It also allows me to do the conversion to YAML more easily, allows me to update headers and so on.
For the purposes of this article, I’ll use the TVF approach, because one can explain it incrementally. To use this, you have to remember to delete it once you’ve finished. Some developers use a schema especially for such utilities that is not put into source control. I generally use temporary stored procedures for this sort of work but you can’t have temporary functions and this code relies heavily on Table-valued functions.
We’ll work through some of the routines that are required to give us a report on a database that we wish to document.
Getting a list of the names of objects referenced by this routine
We start by collecting the names of the objects, If any, that are referenced by the routine
|
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 |
CREATE OR ALTER FUNCTION dbo.JsonReferencedObjects /** Summary: > This lists all the all the objects that are referenced by the object, as a json array Author: PhilFactor FirstCreated: 23/11/2018 Examples: - Select referenced from dbo.JsonReferencedObjects(Object_Id('Sales.SalesOrderHeader')) Name: 'dbo.JsonReferencedObjects' Type: 'sql inline table valued function (IF)' Columns: - 'referenced nvarchar (4000)' Parameters: - '@Object_id int ' Returns: > single column with list of referenced objects **/ ( @Object_id INT ) RETURNS TABLE AS RETURN SELECT '["'+ String_Agg( Coalesce(referenced_server_name + '.', '') + --possible server name if cross-server Coalesce(referenced_database_name + '.', '') + --possible database name if cross-database Coalesce(referenced_schema_name + '.', '') + --likely schema name Coalesce(referenced_entity_name, '') + --very likely entity name Coalesce('.' + Col_Name(referenced_id, referenced_minor_id), '') ,'", "') +'"]' AS referenced FROM sys.sql_expression_dependencies WHERE referencing_id = @object_id Go |
This only returns a string as a single row containing a single column. It must access a table, the sys.sql_expression_dependencies system view. It is capable of improvement, I reckon: this is easy with a modular construction. Should these lists actually be lists of both names and object descriptions? When scanning them it isn’t always obvious, with many databases, what type of object it is. Be my guest and do it!
This function isn’t a huge step forward by itself but here is how you could use it to get a JSON report on all the modules in the database that make references to other objects such as tables. For these, it provides a References key that has as its value a list of the names of all the objects it references.
|
1 2 3 4 5 6 7 |
SELECT Object_Schema_Name(module.object_id) + '.' + Object_Name(module.object_id) AS "Name", Json_Query([References]) AS "References" FROM sys.sql_modules AS module OUTER APPLY dbo.JsonReferencedObjects(module.object_id) WHERE Json_Query("References") IS NOT NULL FOR JSON AUTO; |
Getting a list of the names of objects that reference the routine
This routine is even simpler. It simply gets the names of all the objects that reference the routine.
|
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 |
CREATE OR alter FUNCTION dbo.JsonObjectReferences /** Summary: > This lists all the all the objects that reference the object Author: philfactor Date: 23/11/2018 Examples: - Select * from dbo.JsonObjectReferences(Object_Id('Sales.SalesOrderHeader')) Config: Ansi nulls: 'true' Quoted identifier: 'true' Columns: - 'referencing nvarchar (4000)' Parameters: - '@Object_id int ' Returns: > single column with list of referencing objects **/ ( @Object_id INT ) RETURNS TABLE AS RETURN SELECT '["'+ String_Agg(--likely schema name Coalesce(Object_Schema_Name(referencing_id) + '.', '') + Object_Name(referencing_id) + --definite entity name Coalesce('.' + Col_Name(referencing_id, referencing_minor_id), '') ,'", "') +'"]' AS referencing FROM sys.sql_expression_dependencies WHERE referenced_id = @object_id Go |
You can easily use this in a query to list out those routines that are referenced by other objects and with them the names of the referencing objects. As a variation, we’ll use it to get a document and then convert it back into a relational table just to illustrate the flexibility of JSON.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE table #JsonRoutines (document VARCHAR(20), TheJSON nvarchar(MAX)); --insert the JSON document into it INSERT INTO #JsonRoutines (document, TheJSON) SELECT 'Referencing', (SELECT Object_Schema_Name(module.object_id) + '.' + Object_Name(module.object_id) AS "Name", Json_Query([Referencing]) AS "ReferencedBy" FROM sys.sql_modules AS module OUTER APPLY dbo.JsonObjectReferences(module.object_id) WHERE Json_Query("Referencing") IS NOT NULL FOR JSON AUTO ); SELECT Name, Value AS Referencing FROM OpenJson( (SELECT TheJson FROM #JsonRoutines WHERE document='Referencing')) WITH (Name NVARCHAR(80) '$.Name', ReferencedBy nvarchar(max) '$.ReferencedBy' AS json) OUTER APPLY OpenJson(ReferencedBy) |
Of course, it is much easier to get this result from sys.sql_expression_dependencies directly but by storing the json an querying it, this would allow you to make queries even when the live database isn’t available.
Our next task is to render the declaration of our parameter, return value, or the component columns of a returned table. As this is repeated several times it is worth taking the performance hit and abstracting the logic as a function.
We really want all the information about values in routines to be in a form that is easy to inspect, so as to be able to take it in at a glance. They ought, therefore to be the same as they were in their declaration. This complicates the code more than somewhat, but this justifies having it in a function. Another complication is that we may have documented these values with an extended property. We’d need to pick this information up in an inline comment.
|
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 |
CREATE OR ALTER FUNCTION [dbo].[DataDeclaration] ( @ValueTypemaxlength int, @ValueTypePrecision int, @ValueTypeScale int, @tname sysname, @XMLcollectionID int, @isXMLDocument bit, @Schemaename sysname, @SchemaCollectionName sysname, @Value sql_variant ) RETURNS nvarchar(1000) AS BEGIN RETURN @tname + ' ' + CASE -- we may have to put in the length WHEN @tname IN ('char', 'varchar', 'nchar', 'nvarchar') THEN '(' + CASE WHEN @ValueTypemaxlength = -1 THEN 'MAX' ELSE CONVERT(VARCHAR(4), CASE WHEN @tname IN ('nchar', 'nvarchar') THEN @ValueTypemaxlength / 2 ELSE @ValueTypemaxlength END) END + ')' WHEN @tname IN ('decimal', 'numeric') THEN '(' + CONVERT(VARCHAR(4), @ValueTypePrecision) + ',' + CONVERT(VARCHAR(4), @ValueTypeScale) + ')' ELSE ''--we’ve done with putting in the length END+ --when an XML document CASE WHEN @XMLcollectionID <> 0 THEN --deal with object schema names '(' + CASE WHEN @isXMLDocument = 1 THEN 'DOCUMENT ' ELSE 'CONTENT ' END + COALESCE( QUOTENAME(@Schemaename) + '.' + QUOTENAME(@SchemaCollectionname) ,'NULL') + ')' ELSE '' END + --when an XML document CASE WHEN @value IS NOT NULL --the comment from the extended value THEN COALESCE('-- '+CONVERT(VARCHAR(2000),@value),'') ELSE '--' END End go |
Getting a list of the parameters and their data type for the routine
We now use the function that we just now created, so as to extract all the parameters of the routine whose object_id we pass to it. We need to create this list in the right order.
|
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 |
CREATE OR alter FUNCTION dbo.JsonObjectParameters /** Summary: > This provides a JSON list of parameters together with their data type and any MS_Description they might have Author: philfactor Date: 23/11/2018 Examples: - Select * from dbo.JsonObjectParameters(object_id('dbo.DataDeclaration')) Config: Ansi nulls: 'true' Quoted identifier: 'true' Columns: - 'parameters nvarchar (4000)' Parameters: - '@Object_id int ' Returns: > single column with list of parameters **/ ( @Object_id INT ) RETURNS TABLE AS RETURN SELECT ( SELECT '["'+ String_Agg(parameter.name +' '+ dbo.DataDeclaration(parameter.max_length, parameter.precision,parameter.scale, t.name, SchemaCollection.XML_collection_ID, is_XML_Document , Schemae.name, SchemaCollection.name, Value) + CASE WHEN is_Output=1 THEN 'return value' WHEN has_default_value=1 THEN ' default '+Convert(VARCHAR(20),default_value) WHEN is_readonly=1 THEN ' readonly' ELSE '' END ,'", "') WITHIN GROUP ( ORDER BY parameter_id asc ) +'"]' AS parameter FROM sys.parameters parameter INNER JOIN sys.types t ON parameter.user_Type_ID = t.user_Type_ID LEFT outer JOIN sys.xml_schema_collections SchemaCollection ON SchemaCollection.xml_collection_ID = parameter.XML_collection_ID LEFT OUTER JOIN Sys.Schemas Schemae ON SchemaCollection.schema_ID = Schemae.schema_ID LEFT OUTER JOIN sys.extended_properties ON major_id=parameter.object_id AND minor_id=parameter_id AND class=2 --parameter AND sys.extended_properties.name='MS_Description' WHERE parameter_id >0 AND parameter.object_id=@Object_id ) AS "parameters" GO |
Getting a list of return values and their data type for the routine
|
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 |
CREATE OR alter FUNCTION dbo.JsonObjectReturn /** Summary: > This provides a return data type Author: philfactor Date: 23/11/2019 Examples: - Select * from dbo.JsonObjectReturn(object_id('dbo.DataDeclaration')) Columns: - 'returned nvarchar (4000)' Parameters: - '@Object_id int ' Returns: > single column “returned” with list **/ ( @Object_id INT ) RETURNS TABLE AS RETURN SELECT ( SELECT '["'+ String_Agg(parameter.name +' '+ dbo.DataDeclaration(parameter.max_length, parameter.precision,parameter.scale, t.name, SchemaCollection.XML_collection_ID, is_XML_Document , Schemae.name, SchemaCollection.name, null) + CASE WHEN is_Output=1 THEN 'return value' WHEN has_default_value=1 THEN ' default '+Convert(VARCHAR(20),default_value) WHEN is_readonly=1 THEN ' readonly' ELSE '' END ,'", "') +'"]' AS ReturnValue FROM sys.parameters parameter INNER JOIN sys.types t ON parameter.user_Type_ID = t.user_Type_ID LEFT outer JOIN sys.xml_schema_collections SchemaCollection ON SchemaCollection.xml_collection_ID = parameter.XML_collection_ID LEFT OUTER JOIN Sys.Schemas Schemae ON SchemaCollection.schema_ID = Schemae.schema_ID WHERE parameter_id = 0 AND parameter.object_id=@object_id ) AS "Returned" go |
Getting a list of the Columns and their data type for the routine
Several types of routines, such as views and table functions, produce a table source. It is useful to know the columns and their correct order when using them, and it is particularly useful to be able to provide temporary tables with the correct datatypes in order to save data from them
|
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 |
CREATE OR ALTER FUNCTION dbo.JsonObjectColumns /** Summary: > This provides a list of prarameters together with their data type Author: philfactor Date: 23/11/2018 Database: master Examples: - Select * from dbo.JSONObjectColumns(object_id('dbo.ufnGetContactInformation')) Returns: > single column with list **/ ( @Object_ID int ) RETURNS TABLE AS RETURN ( SELECT ( SELECT TOP 100 percent '["'+ String_Agg(c.name +' '+ dbo.DataDeclaration(c.max_length, c.precision,c.scale, t.name, SchemaCollection.XML_collection_ID, is_XML_Document , Schemae.name, SchemaCollection.name, null),'", "' ) WITHIN GROUP ( ORDER BY column_id asc ) +'"]' FROM sys.columns c INNER JOIN sys.types t ON c.user_Type_ID = t.user_Type_ID LEFT outer JOIN sys.xml_schema_collections SchemaCollection ON SchemaCollection.xml_collection_ID = c.XML_collection_ID LEFT OUTER JOIN Sys.Schemas Schemae ON SchemaCollection.schema_ID = Schemae.schema_ID LEFT OUTER JOIN sys.extended_properties ON major_id = c.object_id AND minor_id=column_id AND class= 1 -- Object or column AND sys.extended_properties.name='MS_Description' where c.object_id = @object_id ) AS "columns" ) |
Putting it all together
Now we can put all this together. This can be executed as is, or turned into a stored procedure
|
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 |
DECLARE @json NVARCHAR(MAX)= (SELECT Object_Schema_Name(object.object_id) +'.'+object.name AS "Name", Lower(Replace(object.type_desc,'_',' '))+' ('+RTrim(object.type)+')' AS "Type", Object_Schema_Name(object.parent_object_id) +'.'+Object_Name(object.parent_object_id) AS "Parent", Convert(NVARCHAR(2000),Value) AS "Description", json_query( (select Convert(VARCHAR(33),object.create_date,126) AS "Created", Convert(VarCHAR(33),object.modify_date,126) AS "Last_Modified" for json path, WITHOUT_ARRAY_WRAPPER) ) as "Dates", --module.definition AS definition, Json_Query ((Select '['+ String_Agg ('"'+f.attribute+'"',' ,')+']' from (values ('IsDeterministic'),('IsEncrypted'),('IsPrecise'), ('IsSystemVerified'), ('SystemDataAccess'),('UserDataAccess'))f(attribute) where OBJECTPROPERTYEX(object.object_id, f.attribute) =1)) as "Properties", Json_Query('{'+ (SELECT String_Agg ('"'+attribute+'":'+CASE WHEN value=1 THEN '"true"' ELSE '"false"' END, ', ') from (VALUES ( 'MS-shipped', object.is_ms_shipped), ( 'Published', object.is_published), ( 'Schema-published',object.is_schema_published), ( 'Ansi nulls', module.uses_ansi_nulls), ( 'Quoted identifier' , module.uses_quoted_identifier), ( 'Schema-bound,',module.is_schema_bound), ( 'Database collation', module.uses_database_collation), ( 'Recompiled', module.is_recompiled), -- ( 'Is Inlineable', module.is_inlineable), --2019 only ( 'Null on null input', module.null_on_null_input), ( 'Native compilation',module.uses_native_compilation)) f("Attribute","value") WHERE value=1)+'}') AS Config, Json_Query(columns) AS "Columns", Json_Query(parameters) AS "Parameters", Json_Query(returned) AS "Returned",Json_Query(referencing) AS ReferencedBy, Json_Query([references]) AS Referencing FROM sys.objects object INNER JOIN sys.sql_modules module ON module.object_id = object.object_id outer APPLY dbo.JsonObjectColumns(module.object_id) OUTER APPLY dbo.JsonObjectParameters(module.object_id) OUTER APPLY dbo.JsonObjectReturn(module.object_id) OUTER APPLY dbo.JsonReferencedObjects(module.object_id) OUTER APPLY dbo.JsonObjectReferences(module.object_id) LEFT OUTER JOIN sys.extended_properties ON major_id = module.object_id AND minor_id=0 AND class= 1 -- Object or column AND sys.extended_properties.name='MS_Description' WHERE OBJECT_SCHEMA_NAME(object.object_ID) <> 'sys' and object.is_ms_shipped=0 order by modify_date asc FOR JSON auto) SELECT @json |
Conclusions
Now we’ve got this far, it should give you some ideas for improvement. This rather depends on your team standards for documentation. You can, for example, document the structured headers that you put into your code. This means updating whatever structured header there is with the current version whenever the code is updated. It is quite easy to do this.
It should be possible to edit a JSON document like this to add a description of each routine, column and parameter. This could then be processed by a routine that saved it to the database as extended properties. As you know, these are surprisingly difficult to code by hand.
I hope this gets you thinking about ideas for improving database documentation to assist teamwork. I’m as bad as anyone in documenting my code and for some reason, every fibre of my body tends to resist adding explanation for code. For this reason, I cherish every device for making the documentation chore easier. This is one of them!
Load comments