{"id":935,"date":"2010-07-09T00:00:00","date_gmt":"2010-07-09T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-crud-generation-from-system-views\/"},"modified":"2021-09-29T16:21:58","modified_gmt":"2021-09-29T16:21:58","slug":"sql-server-crud-generation-from-system-views","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-crud-generation-from-system-views\/","title":{"rendered":"SQL Server CRUD-Generation from System Views"},"content":{"rendered":"
\n

Much of the routine SQL code you write for applications is redundant, or should be. \u00a0Your 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. \u00a0It just needs touching up here and there for the occasion is it used. \u00a0Why then, spend so much typing out stuff when it can be generated automatically?<\/p>\n

In this article, I’ll be showing you how to produce properly documented, \u00a0run-of-the-mill code extremely quickly with very little frustrating effort, using system views.\u00a0 If you are a SQL Server developer who hates repetitive typing, then read on.<\/p>\n

Beautifully-documented too<\/h1>\n

Fortunately there are already tools for coming up with simple SELECT statements (e.g. SQL Prompt<\/a> and SSMS Tools<\/a>: even SSMS does it now), but the ones I’ve seen assume that you have been \u00a0too idle to properly document the database you’ve created \u00a0by using extended properties. There is some excuse for this. SQL Server Management studio makes it ridiculously tiresome to do so, and impossible in places.\u00a0 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.\u00a0 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. \u00a0(Yes, SQL Doc<\/a> has a nice interface for filling in extended properties.)\u00a0<\/p>\n

Here is a TSQL expression (SQL Server 2005 or above) \u00a0that will at least tell you what code in a database is not<\/b> properly documented, when you come to look at a database.<\/p>\n

--Which tables, routines or views\u00a0 do not have any documentation in extended properties\r\nSELECT Type_desc as [description], DB_NAME()+'.'+Object_Schema_name(s.[object_id])\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +'.'+s.name AS [Undocumented]\r\nFROM sys.objects s LEFT OUTER JOIN sys.extended_properties ep\r\n\u00a0\u00a0\u00a0 ON s.object_id=ep.major_ID\u00a0 AND minor_ID=0\r\nWHERE Type_desc IN (\r\n\u00a0 'CHECK_CONSTRAINT', 'DEFAULT_CONSTRAINT', 'FOREIGN_KEY_CONSTRAINT',\r\n\u00a0 'PRIMARY_KEY_CONSTRAINT', 'SQL_SCALAR_FUNCTION', 'SQL_STORED_PROCEDURE',\r\n\u00a0 'SQL_TABLE_VALUED_FUNCTION', 'SQL_TRIGGER', 'UNIQUE_CONSTRAINT',\r\n\u00a0 'USER_TABLE', 'VIEW')\r\n\u00a0 AND ep.value IS NULL\r\nUNION ALL --what about the database itself?\r\nSELECT Description, undocumented from\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0 (select 'DATABASE' as Description, DB_NAME() as undocumented)f\r\n\u00a0 left outer join sys.extended_properties ep\r\n\u00a0\u00a0 on ep.major_ID=0 AND minor_ID=0\r\n\u00a0\u00a0 where value is null\r\n\r\nUNION ALL -- what about parameters to stored procedures\r\nSELECT 'PARAMETER', DB_NAME()+'.'+Object_Schema_name(p.[object_id])+'.'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +OBJECT_NAME(p.[object_id])+'('+p.name+')'\r\n\u00a0FROM\u00a0 sys.parameters p LEFT OUTER JOIN sys.extended_properties ep\r\n\u00a0\u00a0\u00a0 ON p.object_id=ep.major_ID AND minor_ID=p.parameter_ID\r\nWHERE parameter_ID>0 AND ep.value IS NULL\r\n\r\nUNION ALL --or indexes? A little documentation, surely?\r\nSELECT 'INDEXES', DB_NAME()+'.'+Object_Schema_name(i.[object_id])\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +'.'+OBJECT_NAME(i.[object_id])+'('+i.name+')'\r\n\u00a0FROM sys.objects O\r\n\u00a0 INNER JOIN sys.indexes i ON O.object_id=i.object_id\r\n\u00a0 LEFT OUTER JOIN sys.extended_properties ep\u00a0 ON i.object_id=ep.major_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND minor_ID=i.index_ID\r\nWHERE index_ID>0 AND ep.value IS NULL \r\n\u00a0 AND\u00a0 O.Type_desc IN ('USER_TABLE', 'VIEW')\r\n\u00a0 AND OBJECT_NAME(i.[object_id]) NOT LIKE 'sys%'\r\n\r\n<\/pre>\n
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.<\/p>\n
SELECT \r\n\u00a0\u00a0 CAST(value AS VARCHAR(67))+'...' AS [automatically generated comment]\r\n\u00a0FROM\r\n\u00a0 sys.parameters p\r\n\u00a0 INNER JOIN sys.extended_properties ep\r\n\u00a0\u00a0\u00a0 ON p.object_id=ep.major_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND minor_ID=p.parameter_ID\r\nWHERE CAST(value AS VARCHAR(100)) LIKE 'Input parameter for the stored procedure%'\r\n<\/pre>\n
WWhich gives the following result on Adventureworks…<\/p>\n
\"1083-clip_image002.jpg\"<\/p>\n
Generating Stored Procedure calls automatically<\/h1>\n
The payback to documenting your code is immediate, If you use the\u00a0 SQL\u00a0 routines that \u00a0I provide in the way\u00a0 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\u00a0 create fully-commented \u00a0SELECT statements for all your base tables and views. This is handy, but wouldn’t you find something for calling stored procedures useful?\u00a0 If you answered ‘no’ to that question, then you need to get more familiar with the templates in SSMS.\u00a0 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 \u00a0a large number of stored procedures, \u00a0you can merely generate the EXECUTE \u00a0statements on the fly, as or when you need them. \u00a0In a later article, I’ll show you ways to\u00a0 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.<\/p>\n
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.<\/p>\n
\u00a0\r\nSELECT '\/* '+qualifiedName+' *\/'\r\n\u00a0\u00a0\u00a0\u00a0 +CHAR(13)+CHAR(10)+REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0 REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REPLACE( \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STUFF(ExecuteScript, \/*delete final comma line-terminator*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEN(ExecuteScript)-CHARINDEX('|,|',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(ExecuteScript)+'|')-1,3\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'\\n',CHAR(13)+CHAR(10))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'|,|',',') \/*put in new-lines and convert token to comma*\/\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'&lt;','<')\r\n\u00a0\u00a0\u00a0\u00a0 ,'&gt;','>')\u00a0 AS Template\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nFROM \r\n\u00a0 (SELECT \r\n\u00a0\u00a0\u00a0\u00a0 so.name AS Name, \r\n\u00a0\u00a0\u00a0\u00a0 OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName,\r\n\u00a0\u00a0\u00a0\u00a0 'EXEC '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))\r\n\u00a0\u00a0\u00a0\u00a0 +'.'+QUOTENAME(so.name)\r\n\u00a0\u00a0\u00a0\u00a0 +COALESCE('\u00a0 \/*'\u00a0\u00a0 +CONVERT(VARCHAR(300), value)+'*\/', '')\r\n\u00a0\u00a0\u00a0\u00a0 +REPLACE(COALESCE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT REPLACE ('\\n\u00a0\u00a0\u00a0\u00a0\u00a0 '+sp.name+' = '+'''<'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COALESCE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONVERT(VARCHAR(MAX),value)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,REPLACE(sp.name,'@','')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,',','')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +','+t.name+','+'>'''+'|,|'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +COALESCE(' \/*'+CONVERT(VARCHAR(MAX),value)+'*\/',''),'''',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN t.name IN ('char', 'varchar','nchar'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'nvarchar','text','ntext'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'datetime','date') THEN '''' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN t.precision=0 THEN '''' ELSE '' END)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM sys.parameters sp\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT OUTER JOIN sys.extended_properties ep \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON sp.object_id = ep.major_ID\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND sp.parameter_ID = minor_ID \r\n\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE sp.object_ID=so.object_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND parameter_ID>0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY parameter_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH('')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ),'|,|')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,',||', '')\u00a0 [ExecuteScript]\r\n\u00a0\u00a0\u00a0\u00a0 FROM\r\n\u00a0\u00a0\u00a0\u00a0 sys.objects so\r\n\u00a0\u00a0\u00a0\u00a0 LEFT OUTER JOIN sys.extended_properties ep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* get any extended properties *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON ep.name LIKE 'MS_Description'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND major_ID=so.object_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND minor_ID=0\r\n\u00a0\u00a0\u00a0\u00a0 WHERE\r\n\u00a0\u00a0\u00a0\u00a0 OBJECTPROPERTY(object_id, 'IsProcedure')<>0)f\r\n\u00a0\u00a0\u00a0\u00a0 ORDER BY Name\u00a0\r\n<\/pre>\n
The result of this, when executed against AdventureWorks, looks like this.<\/p>\n
\"1083-clip_image004.jpg\"<\/p>\n
But we’ll pick just one item \u00a0from the text version of the result pane.<\/p>\n
\/* HumanResources.uspUpdateEmployeeLogin *\/\r\nEXEC [HumanResources].[uspUpdateEmployeeLogin]\u00a0 \/*Updates the Employee table with the values specified in the input parameters for the given EmployeeID.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @EmployeeID = <Enter a valid EmployeeID from the Employee table.,int,>, \/* Enter a valid EmployeeID from the Employee table.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @ManagerID = <Enter a valid ManagerID for the employee.,int,>, \/* Enter a valid ManagerID for the employee.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @LoginID = '< Enter a valid login for the employee.,nvarchar,>', \/* Enter a valid login for the employee.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @Title = '< Enter a title for the employee.,nvarchar,>', \/* Enter a title for the employee.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @HireDate = '< Enter a hire date for the employee.,datetime,>', \/* Enter a hire date for the employee.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @CurrentFlag = <Enter the current flag for the employee.,Flag,> \/*Enter the current flag for the employee.*\/\r\n<\/pre>\n
Looks a bit odd, I grant you, because this is a template rather than an executable TSQL Expression. Hit Cntl Shift M<\/b><\/p>\n
This appears<\/p>\n
\"1083-clip_image006.jpg\"<\/p>\n
Yes, fill it in (I’ve just done so in the screen-grab) and bang the button. \u00a0The parameters have been filled in<\/p>\n
\u00a0\r\n\/* HumanResources.uspUpdateEmployeeLogin *\/\r\nEXEC [HumanResources].[uspUpdateEmployeeLogin]\u00a0 \/*Updates the Employee table with the \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 values specified in the input parameters for the given EmployeeID.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @EmployeeID = 13, \/* Enter a valid EmployeeID from the Employee table.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @ManagerID = 1072, \/*' Enter a valid ManagerID for the employee.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @LoginID = 'adventure-works\\sidney1', \/*' Enter a valid login for the employee.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @Title = 'Production Technician - WC10', \/*' Enter a title for the employee.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @HireDate = '12\/3\/2010', \/*' Enter a hire date for the employee.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @CurrentFlag = 1 \/*Enter the current flag for the employee.*\/\r\n<\/pre>\n
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)<\/p>\n
Automating The Simple Update Statement<\/h1>\n
\u00a0<\/p>\n
\u00a0\r\nSELECT '\/* '+qualifiedName+' *\/'\r\n\u00a0\u00a0\u00a0\u00a0 +CHAR(13)+CHAR(10)+REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0 REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REPLACE( \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 STUFF(UpdateScript, \/*delete final comma line-terminator*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEN(UpdateScript)-1-CHARINDEX('|,|',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REVERSE(UpdateScript)+'|'),3\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'\\n',CHAR(13)+CHAR(10))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'|,|',',') \/*put in new-lines and convert token to comma*\/\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'&lt;','<')\r\n\u00a0\u00a0\u00a0\u00a0 ,'&gt;','>')\u00a0 AS Template\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nFROM \r\n\u00a0 (SELECT \r\n\u00a0\u00a0\u00a0\u00a0 so.name AS Name, \r\n\u00a0\u00a0\u00a0\u00a0 OBJECT_SCHEMA_NAME(so.object_ID)+'.'+so.name AS qualifiedName,\r\n\u00a0\u00a0\u00a0\u00a0 'UPDATE '+QUOTENAME(OBJECT_SCHEMA_NAME(so.object_ID))\r\n\u00a0\u00a0\u00a0\u00a0 +'.'+QUOTENAME(so.name)\r\n\u00a0\u00a0\u00a0\u00a0 +COALESCE('\u00a0 \/*'\u00a0\u00a0 +CONVERT(VARCHAR(300), value)+'*\/', '')\r\n\u00a0\u00a0\u00a0\u00a0 +'\\nSET\u00a0\u00a0\u00a0\u00a0 '\r\n\u00a0\u00a0\u00a0\u00a0 +REPLACE(COALESCE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT REPLACE ('\\n\u00a0\u00a0\u00a0\u00a0\u00a0 '+sp.name+' = '+'|delim;<'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COALESCE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONVERT(VARCHAR(MAX),value)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,REPLACE(sp.name,'@','')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,',','')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +','+t.name+','+'>|delim;'+'|,|'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +COALESCE(' \/*'+CONVERT(VARCHAR(MAX),value)+'*\/',''),'|delim;',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN t.name IN ('char', 'varchar','nchar'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'nvarchar','text','ntext'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,'datetime','date') THEN '''' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN t.precision=0 THEN '''' ELSE '' END)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM sys.columns sp\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT OUTER JOIN sys.extended_properties ep \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON sp.object_id = ep.major_ID\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND sp.column_ID = minor_ID \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND class=1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE sp.object_ID=so.object_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND column_ID>0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY column_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH('')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ),'1')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ,',||', '')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 + CASE WHEN OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1 THEN\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '\\nWHERE '+SUBSTRING(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT '\\n\u00a0\u00a0\u00a0\u00a0 AND '+ quotename(COL_NAME(ic.object_id,ic.column_id))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +'='+REPLACE('&delim;<'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +REPLACE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COALESCE(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONVERT(VARCHAR(MAX),value)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ,t.name),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ',','')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +','+t.name+','+'>&delim;'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +COALESCE(' \/*'+CONVERT(VARCHAR(MAX),value)+'*\/',''),'&delim;',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'text','ntext','datetime','date')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN '''' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHEN t.precision=0 THEN '''' ELSE '' END)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0 sys.index_columns AS ic inner join sys.indexes i \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 on ic.index_ID=i.index_ID and ic.object_ID=i.object_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 inner join sys.columns sp on sp.column_ID= ic.column_ID \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 and sp.object_ID=ic.object_ID \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN sys.types t ON sp.user_Type_ID=t.user_Type_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 LEFT OUTER JOIN sys.extended_properties ep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* get any extended properties *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON ep.name LIKE 'MS_Description'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND major_ID=ic.object_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND minor_ID=sp.column_ID and type=1 AND class=1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE is_primary_key=1 AND so.object_id=ic.object_id\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ORDER BY key_ordinal\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOR XML PATH('')\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ),11,8000)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE '' END\u00a0 [UpdateScript]\r\n\u00a0\u00a0\u00a0\u00a0 FROM\r\n\u00a0\u00a0\u00a0\u00a0 sys.objects so\r\n\u00a0\u00a0\u00a0\u00a0 LEFT OUTER JOIN sys.extended_properties ep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/* get any extended properties *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON ep.name LIKE 'MS_Description'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND major_ID=so.object_ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND minor_ID=0 --for the table\r\n\u00a0\u00a0\u00a0\u00a0 WHERE\r\n\u00a0\u00a0\u00a0\u00a0 OBJECTPROPERTY(object_id, 'IsUserTable')<>0)f\r\nORDER BY name\r\n<\/pre>\n
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.<\/p>\n
UPDATE [Production].[ProductInventory]\u00a0 \/*Product inventory information.*\/\r\nSET\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/*Product inventory information.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ProductID = <Product identification number. Foreign key to Product.ProductID.,int,>, \/*Product identification number. Foreign key to Product.ProductID.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 LocationID = <Inventory location identification number. Foreign key to Location.LocationID. ,smallint,>, \/*Inventory location identification number. Foreign key to Location.LocationID. *\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Shelf = '<Storage compartment within an inventory location.,nvarchar,>', \/*Storage compartment within an inventory location.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Bin = <Storage container on a shelf in an inventory location.,tinyint,>, \/*Storage container on a shelf in an inventory location.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Quantity = <Quantity of products in the inventory location.,smallint,>, \/*Quantity of products in the inventory location.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 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.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ModifiedDate = '<Date and time the record was last updated.,datetime,>' \/*Date and time the record was last updated.*\/\r\nWHERE\u00a0 [ProductID]=<Product identification number. Foreign key to Product.ProductID.,int,> \/*Product identification number. Foreign key to Product.ProductID.*\/\r\n\u00a0\u00a0\u00a0\u00a0 AND [LocationID]=<Inventory location identification number. Foreign key to Location.LocationID. ,smallint,> \/*Inventory location identification number. Foreign key to Location.LocationID. *\/\r\n<\/pre>\n
This time, I’ve given you too much. You’ll\u00a0 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<\/p>\n
\"1083-img58.jpg\"<\/p>\n
UPDATE [Production].[ProductInventory]\u00a0 \/*Product inventory information.*\/\r\nSET\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \/*Product inventory information.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Shelf = 'C', \/*Storage compartment within an inventory location.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Bin = 5, \/*Storage container on a shelf in an inventory location.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Quantity = 256, \/*Quantity of products in the inventory location.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ModifiedDate = '09\/07\/2010' \/*Date and time the record was last updated.*\/\r\nWHERE\u00a0 [ProductID]=316 \/*Product identification number. Foreign key to Product.ProductID.*\/\r\n\u00a0\u00a0\u00a0\u00a0 AND [LocationID]=10 \/*Inventory location identification number. Foreign key to Location.LocationID. *\/\r\n<\/pre>\n
You’ll notice one or two restrictions. You might want to put GetDate()<\/strong> in the ModifiedDate <\/strong>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.<\/p>\n
Generating Code Templates For Table-Valued Functions<\/h1>\n
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.<\/p>\n
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<\/p>\n
SELECT \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ContactID], \/*Should be the same as the Contact_ID you supply!*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [FirstName], \/*The contact's given name*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [LastName], \/*output column, the Surname of the contact*\/\r\n\u00a0\u00a0\u00a0 \u00a0\u00a0[JobTitle], \/*output column, the contact's job title*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 [ContactType] \/*output column, the type of contact*\/\r\nFROM\u00a0 [dbo].[ufnGetContactInformation]\u00a0 \/*Gets the contact information from the contact_ID*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 34 \/* a valid ContactID from the Person.Contact table.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n<\/pre>\n
Automatically generating simple INSERT…VALES statements<\/h1>\n
The INSERT\u00a0 statement is somewhat of an anticlimax after this behemoth. I Won’t show it here but I’ll let you download it at the bottom 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<\/p>\n
INSERT INTO [Production].[WorkOrder]\u00a0 \/*Manufacturing work orders.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ProductID, \/*Product identification number. Foreign key to Product.ProductID.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 OrderQty, \/*Product quantity to build.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ScrappedQty, \/*Quantity that failed inspection.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 StartDate, \/*Work order start date.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 EndDate, \/*Work order end date.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 DueDate, \/*Work order due date.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ScrapReasonID, \/*Reason for inspection failure.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ModifiedDate \/*Date and time the record was last updated.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\nVALUES(\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 327, \/*Product identification number. Foreign key to Product.ProductID.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 3, \/*Product quantity to build.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 2, \/*Quantity that failed inspection.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 '1\/4\/2010', \/*Work order start date.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 '3\/5\/2010', \/*Work order end date.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 '4\/5\/2010', \/*Work order due date.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 2, \/*Reason for inspection failure.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 '11\/07\/2010' \/*Date and time the record was last updated.*\/\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n<\/pre>\n
(apologies for causing chaos in Adventureworks’ business by my guess at legit values)<\/p>\n
Conclusions<\/h1>\n
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) <\/code>datatype and the rather suspect technique of using FOR XML PATH(''),<\/code> one can automate quite a lot of\u00a0 routine database work.\u00a0 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.\u00a0 You’ll also notice that one doesn’t really stray from a small number of System Views.<\/p>\n
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.\u00a0 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.<\/p>\n
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.<\/p>\n
\n
Note: (16 Jul 2010) \u00a0I’ve updated the CallProcTemplate.SQL and the TableValuedFunctionTemplate.SQL. a ‘|,|’ string got changed to a ‘1’ somehow.<\/p>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4150,4151,4252],"coauthors":[6813],"class_list":["post-935","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-sql","tag-sql-server","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/935","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=935"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/935\/revisions"}],"predecessor-version":[{"id":85593,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/935\/revisions\/85593"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=935"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=935"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=935"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=935"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}