I was using user-defined tables types (UDTT) quite intensively despite of their current disadvantages especially their forced READONLY behavior when used as stored procedures parameter. Another disadvantage which I didn’t realize impact for is that UDTT cannot be altered. I hit maintenance nightmare when they became spread across my T-SQL code.
What I did is that I encapsulated few business objects as UDTT and started to use them consistently in my T-SQL stored procedures and functions (directly in code and as input parameters as well). It works great, your code looks “strongly-typed” and it is definitely big step forward in T-SQL programming. But whenever I wanted to add or alter field in my UDTT, I had to drop/create all my procedures or functions which this UDTT depends on. It looks peaceful but it’s totally annoying because you have no built-in option to script references for UDTT and you have to do it manually or through “script database” task. If you are in development phase when everything changes from minute to minute, it’s unusable.
I wrote helper stored procedures which take UDTT name as only parameter and returns DROP and CREATE scripts of all references. It basically do what I’ve mentioned, it scripts all UDTT’s references and saves my time.
First is ap_FindReferences which finds all references and returns them as single result set. It would be enough to display this result set as text in SSMS (CTRL+T) but it’s not because SSMS cannot display strings long more than few thousands characters in results pane. That’s why there is second procedure ap_WriteReferences which do quite nasty job of writing result from ap_FindReferences into .sql file you can display in SSMS.
Usage is following:
EXEC ap_WriteReferences '<your user-defined table type>'
…result is text file defined within ap_WriteReferences with scripted references to your UDDT.
Good option is to bind this command to keyboard shortcut in SSMS.
Here are those two procedures (some notes follow):
-- Find all referencing objects to user-defined table type in @fullObjectName parameter -- and generate DROP scripts and CREATE scripts for them CREATE PROC ap_FindReferences (@fullObjectName VARCHAR(200)) AS BEGIN SET NOCOUNT ON IF (TYPE_ID (@fullObjectName) IS NULL) BEGIN RAISERROR ('User-defined table type ''%s'' does not exists. Include full object name with schema.', 16,1, @fullObjectName) RETURN END; WITH sources AS ( SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(m.object_id)) RowId, definition FROM sys.sql_expression_dependencies d JOIN sys.sql_modules m ON m.object_id = d.referencing_id JOIN sys.objects o ON o.object_id = m.object_id WHERE referenced_id = TYPE_ID(@fullObjectName) ) SELECT 'DROP ' + CASE OBJECTPROPERTY(referencing_id, 'IsProcedure') WHEN 1 THEN 'PROC ' ELSE CASE WHEN OBJECTPROPERTY(referencing_id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(referencing_id, 'IsInlineFunction') = 1 THEN 'FUNCTION ' ELSE '' END END + SCHEMA_NAME(o.schema_id) + '.' + + OBJECT_NAME(m.object_id) FROM sys.sql_expression_dependencies d JOIN sys.sql_modules m ON m.object_id = d.referencing_id JOIN sys.objects o ON o.object_id = m.object_id WHERE referenced_id = TYPE_ID(@fullObjectName) UNION ALL SELECT 'GO' UNION ALL SELECT CASE WHEN number = RowId THEN DEFINITION ELSE 'GO' END FROM sources s JOIN (SELECT DISTINCT number FROM master.dbo.spt_values) n ON n.number BETWEEN RowId AND RowId+1 END GO -- Invokes ap_FindReferences procedure and writes scripted result to .sql file CREATE PROC ap_WriteReferences @typeToFind VARCHAR(200) AS BEGIN DECLARE @sqlCmd VARCHAR(500) DECLARE @database VARCHAR(200) = 'test' DECLARE @outputFile VARCHAR(500) = 'c:\refences.sql' SET @sqlCmd = 'sqlcmd.exe -d '+@database+' -q "EXEC ap_FindReferences '''+ @typeToFind +'''" -o '+ @outputFile +' -h-1 -y0' EXEC xp_cmdshell @sqlCmd END
Please pay attention to these notes if you are going to use it:
- you must change your database name and file name in ap_WriteReferences procedure
- it uses sys.sql_modules view to find references, hence it may not have most current information. It worked for me so far but still…
- please use it carefully, it drops objects and it is intended to dedicated development databases only (and at your own risk )
- it works only on SQL Server 2008 and higher
- extended properties are not scripted, it will be forgotten related objects are dropped
If you know any better way how to avoid this maintenance issue with UDDT, surely let me know! Also let me know here if you want me to include it as feature to my “pocket” SSMS productivity add-in.
Tags: SQL Server, SSMS, t-sql