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
[
{
"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 would normally be added to the header of the source code. 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.
This code is a single, rather large, query. I usually 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
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.
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.
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.
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.
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.
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
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
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
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!
The post What’s in that database? Getting information about routines appeared first on Simple Talk.
from Simple Talk https://ift.tt/2PHUzY5
via
