If you are not keen on repetitive typing, you can still rapidly produce production-quality documented code by planning ahead and using Extended properties, and system views. Phil Factor explains, with some Scary SQL
Much of the routine SQL code you write for applications is redundant, or should be. Your creative contribution is, in percentage terms, quite minor. The simple code to call stored procedures, do simple SELECTS from tables or views, INSERTS, UPDATES, use Table-valued functions and so on are almost entirely determined by the metadata that is accessible in the system views. It just needs touching up here and there for the occasion is it used. Why then, spend so much typing out stuff when it can be generated automatically?
In this article, I’ll be showing you how to produce properly documented, run-of-the-mill code extremely quickly with very little frustrating effort, using system views. If you are a SQL Server developer who hates repetitive typing, then read on.
Beautifully-documented too
Fortunately there are already tools for coming up with simple SELECT statements (e.g. SQL Prompt and SSMS Tools: even SSMS does it now), but the ones I’ve seen assume that you have been too idle to properly document the database you’ve created by using extended properties. There is some excuse for this. SQL Server Management studio makes it ridiculously tiresome to do so, and impossible in places. This is one of the instances where I’m happy to be dogmatic: I never ever allow a database through a code-review unless every database object has at least a summary explanation of what on earth it does or why it is there. There is even a handy utility, SQLTAC, to check such things, and prompt you to add the documentation, or ‘domain knowledge’ as Steve refers to it. (Yes, SQL Doc has a nice interface for filling in extended properties.)
Here is a TSQL expression (SQL Server 2005 or above) that will at least tell you what code in a database is not properly documented, when you come to look at a database.
--Which tables, routines or views do not have any documentation in extended properties
SELECT Type_desc as [description], DB_NAME()+'.'+Object_Schema_name(s.[object_id])
+'.'+s.name AS [Undocumented]
FROM sys.objects s LEFT OUTER JOIN sys.extended_properties ep
ON s.object_id=ep.major_ID AND minor_ID=0
WHERE Type_desc IN (
'CHECK_CONSTRAINT', 'DEFAULT_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT',
'PRIMARY_KEY_CONSTRAINT', 'SQL_SCALAR_FUNCTION', 'SQL_STORED_PROCEDURE',
'SQL_TABLE_VALUED_FUNCTION', 'SQL_TRIGGER', 'UNIQUE_CONSTRAINT',
'USER_TABLE', 'VIEW')
AND ep.value IS NULL
UNION ALL --what about the database itself?
SELECT Description, undocumented from
(select 'DATABASE' as Description, DB_NAME() as undocumented)f
left outer join sys.extended_properties ep
on ep.major_ID=0 AND minor_ID=0
where value is null
UNION ALL -- what about parameters to stored procedures
SELECT 'PARAMETER', DB_NAME()+'.'+Object_Schema_name(p.[object_id])+'.'
+OBJECT_NAME(p.[object_id])+'('+p.name+')'
FROM sys.parameters p LEFT OUTER JOIN sys.extended_properties ep
ON p.object_id=ep.major_ID AND minor_ID=p.parameter_ID
WHERE parameter_ID>0 AND ep.value IS NULL
UNION ALL --or indexes? A little documentation, surely?
SELECT 'INDEXES', DB_NAME()+'.'+Object_Schema_name(i.[object_id])
+'.'+OBJECT_NAME(i.[object_id])+'('+i.name+')'
FROM sys.objects O
INNER JOIN sys.indexes i ON O.object_id=i.object_id
LEFT OUTER JOIN sys.extended_properties ep ON i.object_id=ep.major_ID
AND minor_ID=i.index_ID
WHERE index_ID>0 AND ep.value IS NULL
AND O.Type_desc IN ('USER_TABLE', 'VIEW')
AND OBJECT_NAME(i.[object_id]) NOT LIKE 'sys%'
Some programmers will write automated ways of filling in the extended properties to try to defeat this. Even in AdventureWorks, you will find crime scenes like this, where a programmer wanted to get home early.
SELECT
CAST(value AS VARCHAR(67))+'...' AS [automatically generated comment]
FROM
sys.parameters p
INNER JOIN sys.extended_properties ep
ON p.object_id=ep.major_ID
AND minor_ID=p.parameter_ID
WHERE CAST(value AS VARCHAR(100)) LIKE 'Input parameter for the stored procedure%'
WWhich gives the following result on Adventureworks...
Generating Stored Procedure calls automatically
The payback to documenting your code is immediate, If you use the SQL routines that I provide in the way I’m going to show you, you can generate SQL for your database that is immaculately documented. In my last article, I showed you how to create fully-commented SELECT statements for all your base tables and views. This is handy, but wouldn’t you find something for calling stored procedures useful? If you answered ‘no’ to that question, then you need to get more familiar with the templates in SSMS. All you need to do is to create template files that allow you to fill in the parameters from a form within SSMS. If you have a database that has a large number of stored procedures, you can merely generate the EXECUTE statements on the fly, as or when you need them. In a later article, I’ll show you ways to generate the templates and put them in your template directory so that they appear in the template browser. From then, it is just a matter of dragging and dropping. Yes, SSMS should be like that, but it isn’t.
Here’s the SQL Statement. Brace yourself, it is a bit scary, but I’m not going to insist that you understand it, just the process.
SELECT '/* '+qualifiedName+' */'
+CHAR(13)+CHAR(10)+REPLACE(
REPLACE(
REPLACE(
REPLACE(
STUFF(ExecuteScript, /*delete final comma line-terminator*/
LEN(ExecuteScript)-CHARINDEX('|,|',
REVERSE(ExecuteScript)+'|')-1,3
,'')
,'\n',CHAR(13)+CHAR(10))
,'|,|',',') /*put in new-lines and convert token to comma*/
,'<','<')
,'>','>') AS Template
FROM
(SELECT
so.name AS Name,
OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName,
'EXEC '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))
+'.'+QUOTENAME(so.name)
+COALESCE(' /*' +CONVERT(VARCHAR(300), value)+'*/', '')
+REPLACE(COALESCE(
(
SELECT REPLACE ('\n '+sp.name+' = '+'''<'
+REPLACE(
COALESCE(
CONVERT(VARCHAR(MAX),value)
,REPLACE(sp.name,'@','')
)
,',','')
+','+t.name+','+'>'''+'|,|'
+COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/',''),'''',
CASE WHEN t.name IN ('char', 'varchar','nchar'
,'nvarchar','text','ntext'
,'datetime','date') THEN ''''
WHEN t.precision=0 THEN '''' ELSE '' END)
FROM sys.parameters sp
INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID
LEFT OUTER JOIN sys.extended_properties ep
ON sp.object_id = ep.major_ID
AND sp.parameter_ID = minor_ID
WHERE sp.object_ID=so.object_ID
AND parameter_ID>0
ORDER BY parameter_ID
FOR XML PATH('')
),'|,|')
,',||', '') [ExecuteScript]
FROM
sys.objects so
LEFT OUTER JOIN sys.extended_properties ep
/* get any extended properties */
ON ep.name LIKE 'MS_Description'
AND major_ID=so.object_ID
AND minor_ID=0
WHERE
OBJECTPROPERTY(object_id, 'IsProcedure')<>0)f
ORDER BY Name
The result of this, when executed against AdventureWorks, looks like this.
But we’ll pick just one item from the text version of the result pane.
/* HumanResources.uspUpdateEmployeeLogin */
EXEC [HumanResources].[uspUpdateEmployeeLogin] /*Updates the Employee table with the values specified in the input parameters for the given EmployeeID.*/
@EmployeeID = <Enter a valid EmployeeID from the Employee table.,int,>, /* Enter a valid EmployeeID from the Employee table.*/
@ManagerID = <Enter a valid ManagerID for the employee.,int,>, /* Enter a valid ManagerID for the employee.*/
@LoginID = '< Enter a valid login for the employee.,nvarchar,>', /* Enter a valid login for the employee.*/
@Title = '< Enter a title for the employee.,nvarchar,>', /* Enter a title for the employee.*/
@HireDate = '< Enter a hire date for the employee.,datetime,>', /* Enter a hire date for the employee.*/
@CurrentFlag = <Enter the current flag for the employee.,Flag,> /*Enter the current flag for the employee.*/
Looks a bit odd, I grant you, because this is a template rather than an executable TSQL Expression. Hit Cntl Shift M
This appears
Yes, fill it in (I've just done so in the screen-grab) and bang the button. The parameters have been filled in
/* HumanResources.uspUpdateEmployeeLogin */
EXEC [HumanResources].[uspUpdateEmployeeLogin] /*Updates the Employee table with the
values specified in the input parameters for the given EmployeeID.*/
@EmployeeID = 13, /* Enter a valid EmployeeID from the Employee table.*/
@ManagerID = 1072, /*' Enter a valid ManagerID for the employee.*/
@LoginID = 'adventure-works\sidney1', /*' Enter a valid login for the employee.*/
@Title = 'Production Technician - WC10', /*' Enter a title for the employee.*/
@HireDate = '12/3/2010', /*' Enter a hire date for the employee.*/
@CurrentFlag = 1 /*Enter the current flag for the employee.*/
It would have been so easy for SSMS to do this. Be warned though, the current version does not do output variables. The code was getting a bit long, and I won't do it unless you pester me into believing that it is useful. (I add them by hand)
Automating The Simple Update Statement
Of course, we can then go on to provide the SQL For the rest of the Create, Read, Update and Delete operations. Here is SQL that provides the UPDATE, using the primary key for the where clause if there is one. You'll see it is just a modification of the first bit of SQL
SELECT '/* '+qualifiedName+' */'
+CHAR(13)+CHAR(10)+REPLACE(
REPLACE(
REPLACE(
REPLACE(
STUFF(UpdateScript, /*delete final comma line-terminator*/
LEN(UpdateScript)-1-CHARINDEX('|,|',
REVERSE(UpdateScript)+'|'),3
,'')
,'\n',CHAR(13)+CHAR(10))
,'|,|',',') /*put in new-lines and convert token to comma*/
,'<','<')
,'>','>') AS Template
FROM
(SELECT
so.name AS Name,
OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName,
'UPDATE '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))
+'.'+QUOTENAME(so.name)
+COALESCE(' /*' +CONVERT(VARCHAR(300), value)+'*/', '')
+'\nSET '
+REPLACE(COALESCE(
(
SELECT REPLACE ('\n '+sp.name+' = '+'|delim;<'
+REPLACE(
COALESCE(
CONVERT(VARCHAR(MAX),value)
,REPLACE(sp.name,'@','')
)
,',','')
+','+t.name+','+'>|delim;'+'|,|'
+COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/',''),'|delim;',
CASE WHEN t.name IN ('char', 'varchar','nchar'
,'nvarchar','text','ntext'
,'datetime','date') THEN ''''
WHEN t.precision=0 THEN '''' ELSE '' END)
FROM sys.columns sp
INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID
LEFT OUTER JOIN sys.extended_properties ep
ON sp.object_id = ep.major_ID
AND sp.column_ID = minor_ID
AND class=1
WHERE sp.object_ID=so.object_ID
AND column_ID>0
ORDER BY column_ID
FOR XML PATH('')
),'1')
,',||', '')
+ CASE WHEN OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1 THEN
'\nWHERE '+SUBSTRING(
(
SELECT '\n AND '+ quotename(COL_NAME(ic.object_id,ic.column_id))
+'='+REPLACE('&delim;<'
+REPLACE(
COALESCE(
CONVERT(VARCHAR(MAX),value)
,t.name),
',','')
+','+t.name+','+'>&delim;'
+COALESCE(' /*'+CONVERT(VARCHAR(MAX),value)+'*/',''),'&delim;',
CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar',
'text','ntext','datetime','date')
THEN ''''
WHEN t.precision=0 THEN '''' ELSE '' END)
FROM sys.index_columns AS ic inner join sys.indexes i
on ic.index_ID=i.index_ID and ic.object_ID=i.object_ID
inner join sys.columns sp on sp.column_ID= ic.column_ID
and sp.object_ID=ic.object_ID
INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID
LEFT OUTER JOIN sys.extended_properties ep
/* get any extended properties */
ON ep.name LIKE 'MS_Description'
AND major_ID=ic.object_ID
AND minor_ID=sp.column_ID and type=1 AND class=1
WHERE is_primary_key=1 AND so.object_id=ic.object_id
ORDER BY key_ordinal
FOR XML PATH('')
),11,8000)
ELSE '' END [UpdateScript]
FROM
sys.objects so
LEFT OUTER JOIN sys.extended_properties ep
/* get any extended properties */
ON ep.name LIKE 'MS_Description'
AND major_ID=so.object_ID
AND minor_ID=0 --for the table
WHERE
OBJECTPROPERTY(object_id, 'IsUserTable')<>0)f
ORDER BY name
There is an extra complication here because you will want to update a row based on a value of the primary key more often than not, and the primary key, if it exists, can involve more than one row. Here, you will get something that looks like this, using Adventureworks's ProductInventory as an example.
UPDATE [Production].[ProductInventory] /*Product inventory information.*/
SET /*Product inventory information.*/
ProductID = <Product identification number. Foreign key to Product.ProductID.,int,>, /*Product identification number. Foreign key to Product.ProductID.*/
LocationID = <Inventory location identification number. Foreign key to Location.LocationID. ,smallint,>, /*Inventory location identification number. Foreign key to Location.LocationID. */
Shelf = '<Storage compartment within an inventory location.,nvarchar,>', /*Storage compartment within an inventory location.*/
Bin = <Storage container on a shelf in an inventory location.,tinyint,>, /*Storage container on a shelf in an inventory location.*/
Quantity = <Quantity of products in the inventory location.,smallint,>, /*Quantity of products in the inventory location.*/
rowguid = '<ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.,uniqueidentifier,>', /*ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.*/
ModifiedDate = '<Date and time the record was last updated.,datetime,>' /*Date and time the record was last updated.*/
WHERE [ProductID]=<Product identification number. Foreign key to Product.ProductID.,int,> /*Product identification number. Foreign key to Product.ProductID.*/
AND [LocationID]=<Inventory location identification number. Foreign key to Location.LocationID. ,smallint,> /*Inventory location identification number. Foreign key to Location.LocationID. */
This time, I've given you too much. You'll want to give it a haircut. I'm tempted not to show key columns as it is rare to want to alter those but the delete key is the easiest one on the keyboard to use. With these queries, I've used the principle that it is easier to pare stuff back than to have to type code in. So, we take out the first two lines and hit Cntl Shift M
UPDATE [Production].[ProductInventory] /*Product inventory information.*/
SET /*Product inventory information.*/
Shelf = 'C', /*Storage compartment within an inventory location.*/
Bin = 5, /*Storage container on a shelf in an inventory location.*/
Quantity = 256, /*Quantity of products in the inventory location.*/
ModifiedDate = '09/07/2010' /*Date and time the record was last updated.*/
WHERE [ProductID]=316 /*Product identification number. Foreign key to Product.ProductID.*/
AND [LocationID]=10 /*Inventory location identification number. Foreign key to Location.LocationID. */
You'll notice one or two restrictions. You might want to put GetDate() in the ModifiedDate field , but this will require a small amount of editing. Still, faster than typing all those comments by hand. I've also forgotten to leave out computed columns. You'll find it easy to alter the code.
Generating Code Templates For Table-Valued Functions
So next, we do some minor alterations to product the code for a Table-Valued Function. This, together with the Stored Procedure is going to be the classic component of an interface. As the SQL is pretty similar, I won't publish it, in the article body but you can download the code from the speech-bubble.
In order to test it, i actually had to add the documentation into AdventureWorks for the columns passed back. Howver, it gives you an idea of the payback through doing it, if your function is called several times. This is particularly true of the parameters: this level of documentation makes it very easy to follow and check through code
SELECT
[ContactID], /*Should be the same as the Contact_ID you supply!*/
[FirstName], /*The contact's given name*/
[LastName], /*output column, the Surname of the contact*/
[JobTitle], /*output column, the contact's job title*/
[ContactType] /*output column, the type of contact*/
FROM [dbo].[ufnGetContactInformation] /*Gets the contact information from the contact_ID*/
(
34 /* a valid ContactID from the Person.Contact table.*/
)
Automatically generating simple INSERT...VALES statements
The INSERT statement is somewhat of an anticlimax after this behemoth. I Won't show it here but I'll let you download it from the speech-bubble at the head of the article. The way the template is used is identical so I won't show that either. It produces some nice code like this
INSERT INTO [Production].[WorkOrder] /*Manufacturing work orders.*/
(
ProductID, /*Product identification number. Foreign key to Product.ProductID.*/
OrderQty, /*Product quantity to build.*/
ScrappedQty, /*Quantity that failed inspection.*/
StartDate, /*Work order start date.*/
EndDate, /*Work order end date.*/
DueDate, /*Work order due date.*/
ScrapReasonID, /*Reason for inspection failure.*/
ModifiedDate /*Date and time the record was last updated.*/
)
VALUES(
327, /*Product identification number. Foreign key to Product.ProductID.*/
3, /*Product quantity to build.*/
2, /*Quantity that failed inspection.*/
'1/4/2010', /*Work order start date.*/
'3/5/2010', /*Work order end date.*/
'4/5/2010', /*Work order due date.*/
2, /*Reason for inspection failure.*/
'11/07/2010' /*Date and time the record was last updated.*/
)
(apologies for causing chaos in Adventureworks' business by my guess at legit values)
Conclusions
With the editor nervously signaling to me that I have outrun my 2000 words, I'd like to round up by saying that the combination of the System views, the Varchar(MAX) datatype and the rather suspect technique of using FOR XML PATH(''), one can automate quite a lot of routine database work. If you study the code, you will see that I'm just reusing the same sections of code over and over again but in different ways to suit the syntax, and the object. You'll also notice that one doesn't really stray from a small number of System Views.
Although these templates can be used with the techniques I showed you at the start of the series, using the SELECT statement as an example, there is more one can do to make it slick. The next stage is to be able to generate automatically all the application's template code automatically for every workstation, so that one can have them directly draggable-n-droppable from the Template Explorer whenever you need to do some routine CRUD. I'll admit that this is more likely with views, Stored Procedures and Table-valued functions, but then I though I ought to include table/view work as well. This would suggest that every DBA's dream of having a defined independent interface between the application and the database base-tables could be made a much more realistic alternative with the basic operations made much easier for the application developer via templates.
Putting automatically-generated templates into SSMS requires a workstation script, in PowerShell or whatever. We'll be using whatever, and hope to show you more soon.
Note: (16 Jul 2010) I've updated the CallProcTemplate.SQL and the TableValuedFunctionTemplate.SQL. a '|,|' string got changed to a '1' somehow.
