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.