Background
We can use a user-defined table type to declare table-valued parameters for stored procedures, functions or in a batch. However, we cannot alter/modify the type once it is created. As per Microsoft
In the restriction section, it says: ‘The user-defined table type definition cannot be modified after it is created’.
But sometimes we need to modify or alter the user defined types.
Problem with the traditional approach
A usual approach for this purpose is to delete the type and recreate along with the changes. But there is a problem when you have references. You can’t delete a type unless all the references are deleted. So one popular way to solve this is to find all the references, delete the references, delete the type, then deploy the modified type and finally restore the references from backup.
Here are the steps:
1. First step is to find out all the references:
DECLARE @typeName varchar(500) = 'myType'
SELECT
referencing_entity_name AS spName
FROM sys.dm_sql_referencing_entities('dbo.'+@typeName, 'TYPE')
Using this script we can find all the references of ‘myType’. List-out the reference names and find out the backup scripts which are required to recreate them. Now the next step is to delete all the references.
2. To delete all the references we can use a cursor. Here is the script:
DECLARE dropCursor CURSOR FOR
SELECT
referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.'+@typeName, 'TYPE')
OPEN dropCursor
FETCH NEXT FROM dropCursor INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('drop procedure [' + @procName + ']')
FETCH NEXT FROM dropCursor INTO @procName
END
CLOSE dropCursor
DEALLOCATE dropCursor
3. Drop the Type and recreate with the modified script.
4. Recreate all the references from the backup script.
This is troublesome and manual attention is needed to check all these things.
Alternative automatic approach
We can apply a slight modified technique to reduce all these hassles and do all the backend staffs automatically. Here are the steps:
1. We will follow the step 1 to find out all the reference names along with the definitions. Then we will put them in a temporary table.
DECLARE @typeName varchar(500) = 'myType'
IF TYPE_ID(@typeName) IS NOT NULL
---------Find refereced objects and put them in a temporary table---------
SELECT
pr.name AS spName,
mod.definition AS spText
INTO #spBackupRestore
FROM sys.procedures pr
INNER JOIN sys.sql_modules mod
ON pr.object_id = mod.object_id
WHERE pr.Is_MS_Shipped = 0
AND pr.name IN (SELECT
referencing_entity_name AS spName
FROM sys.dm_sql_referencing_entities('dbo.'+@typeName, 'TYPE'))
-----------End-----------------
2. Drop the references
DECLARE @procName varchar(500);
DECLARE @sql nvarchar(max)
DECLARE dropCursor CURSOR FOR
SELECT
spName
FROM #spBackupRestore
OPEN dropCursor
FETCH NEXT FROM dropCursor INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('drop procedure [' + @procName + ']')
FETCH NEXT FROM dropCursor INTO @procName
END
CLOSE dropCursor
DEALLOCATE dropCursor
3. Drop the Type.
DROP TYPE myType
4. Recreate the type with modifications
CREATE TYPE myType as TABLE(
Param1 datatype
Param2 datatype
)
5. Restore the references:
DECLARE @sqlRestore nvarchar(max)
DECLARE restoreCursor CURSOR FOR
SELECT
spText
FROM #spBackupRestore
OPEN restoreCursor
FETCH NEXT FROM restoreCursor INTO @sqlRestore
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlRestore = REPLACE(@sqlRestore, '''', '''''')
SET @sqlRestore = 'EXEC(''' + @sqlRestore + ''')'
EXEC (@sqlRestore)
FETCH NEXT FROM restoreCursor INTO @sqlRestore
END
CLOSE restoreCursor
DEALLOCATE restoreCursor
6. Drop the temporary table.
DROP TABLE spBackupRestore
Now putting these all together here is one single script to do all these:
DECLARE @typeName varchar(500) = 'myType'
IF TYPE_ID(@typeName) IS NOT NULL
BEGIN
---------Find referenced objects and put them in a temporary table---------
SELECT
pr.name AS spName,
mod.definition AS spText
INTO #spBackupRestore
FROM sys.procedures pr
INNER JOIN sys.sql_modules mod
ON pr.object_id = mod.object_id
WHERE pr.Is_MS_Shipped = 0
AND pr.name IN (SELECT
referencing_entity_name AS spName
FROM sys.dm_sql_referencing_entities('dbo.'+@typeName, 'TYPE'))
-----------End-----------------
---------Drop the referenced objects-----------------
DECLARE @procName varchar(500);
DECLARE @sql nvarchar(max)
DECLARE dropCursor CURSOR FOR
SELECT
spName
FROM #spBackupRestore
OPEN dropCursor
FETCH NEXT FROM dropCursor INTO @procName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('drop procedure [' + @procName + ']')
FETCH NEXT FROM dropCursor INTO @procName
END
CLOSE dropCursor
DEALLOCATE dropCursor
-----------End-----------------
END
-----------Drop user defined type-----------------
BEGIN
DROP TYPE myType
CREATE TYPE myType as TABLE(
Param1 datatype
Param2 datatype
)
END
-----------End-----------------
-----------Restore referenced objects from temporary table-----------------
BEGIN
DECLARE @sqlRestore nvarchar(max)
DECLARE restoreCursor CURSOR FOR
SELECT
spText
FROM #spBackupRestore
OPEN restoreCursor
FETCH NEXT FROM restoreCursor INTO @sqlRestore
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlRestore = REPLACE(@sqlRestore, '''', '''''')
SET @sqlRestore = 'EXEC(''' + @sqlRestore + ''')'
EXEC (@sqlRestore)
FETCH NEXT FROM restoreCursor INTO @sqlRestore
END
CLOSE restoreCursor
DEALLOCATE restoreCursor
END
------Drop temporary table------------
DROP TABLE #spBackupRestore
GO
Now we can use this script as a template for any user type only modifying the section described in steps 3 & 4.
Add new comment