Alter User Defined Type in SQL Server

Alter user defined type in SQL Server

This is what I normally use, albeit a bit manual:

/* Add a 'temporary' UDDT with the new definition */ 
exec sp_addtype t_myudt_tmp, 'numeric(18,5)', NULL 


/* Build a command to alter all the existing columns - cut and 
** paste the output, then run it */ 
select 'alter table dbo.' + TABLE_NAME + 
       ' alter column ' + COLUMN_NAME + ' t_myudt_tmp' 
from INFORMATION_SCHEMA.COLUMNS 
where DOMAIN_NAME = 't_myudt' 

/* Remove the old UDDT */ 
exec sp_droptype t_mydut


/* Rename the 'temporary' UDDT to the correct name */ 
exec sp_rename 't_myudt_tmp', 't_myudt', 'USERDATATYPE' 

Unable to Modify User-Defined Table Type

You have binding in SP_DoSomething stored procedure. The type you want to change is used in that stored procedure.

You need to save script of that procedure. Drop it. Change dbo.UserDefinedTableType and create procedure again.

There is a similar post here. Check is some of the answers can help you. Answer of @norlando seems promising.

Copying user-defined types from one database to another

Right click on the database in SQL Server Management Studio, choose Tasks > Generate Scripts.... Then check user defined data types and table types. Click Next etc up until Finish.

Once you have the script generated, execute it on the database you want to create the types in.

How To add composite unique key to user defined table Type

You Can't Alter UserDefined table types ,You need to drop and recreate again for any changes..

From MSDN..

User-defined types cannot be modified after they are created, because changes could invalidate data in the tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause.

Below is the way to create unique constraint on UserDefined Table Type

CREATE TYPE test AS TABLE 
( col1 VARCHAR(50)
, col2 INT ,
 unique (col1,col2)
);

Note:We can't name Constraints,so creating constraints like normal way is not valid..

Example below

 CREATE TYPE test AS TABLE 
    ( col1 VARCHAR(50)
    , col2 INT ,
    constraint test  unique (col1,col2)
    );

SQL Server 2012 - Cannot drop User defined (table) type while there are no active dependencies

Renaming is a dangerous pastime because it doesn't mesh well with SQL Server's limited implementation of dependency tracking, and the deferred name resolution of stored procedures. I couldn't actually reproduce this specific scenario on SQL Server 2017 (dependencies were correctly tracked if the UDT is used in the procedure's body) so it's possible this case has been improved. Certainly, if you use the UDT as a parameter it actually detects the problem and gives an appropriate error message:

CREATE TYPE BadUdt FROM NVARCHAR(10);
GO
CREATE PROCEDURE AreUdtsBadThough(@T BadUdt) AS BEGIN
    RETURN;
END;
GO
EXEC sp_rename 'BadUdt', 'GoodUdt';
GO
CREATE TYPE BadUdt FROM NVARCHAR(20);
GO
EXEC AreUdtsBadThough NULL

Msg 496, Level 16, State 1, Procedure AreUdtsBadThough, Line 6 [Batch
Start Line 16] The parameter "@T" is not the same type as the type it
was created with. Drop and recreate the module using a two-part name
for the type, or use sp_refreshsqlmodule to refresh its parameters
metadata.

Despite the name change, the parameter of AreUdtsBadThough is still linked to the old type by ID, which is why you can't drop that:

SELECT p.[name], t.[name]
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE [object_id] = OBJECT_ID('AreUdtsBadThough')

+------+---------+
| name |  name   |
+------+---------+
| @T   | GoodUdt |
+------+---------+

The error helpfully describes the general solution for dependency problems: invoke sp_refreshsqlmodule, which forces the stored procedure (or function, or trigger, or view) to effectively be recompiled and the dependencies to be updated:

EXEC sp_refreshsqlmodule 'AreUdtsBadThough'

SELECT p.[name], t.[name]
FROM sys.parameters p
JOIN sys.types t ON p.user_type_id = t.user_type_id
WHERE [object_id] = OBJECT_ID('AreUdtsBadThough')

+------+--------+
| name |  name  |
+------+--------+
| @T   | BadUdt |
+------+--------+

And now GoodUdt has no dependencies anymore and can be dropped.

The problem of dependencies not always getting updated correctly (which is not restricted to renaming) has prompted some people to come up with more permanent solutions. Aaron Bertrand has an article on automating the dependency refreshes for SQL Server 2008 (which should still work for later editions, even if it may do more work than necessary) due to improved dependency checks.

What permissions to I need to use User Defined Table Types

After doing a bit more digging the permission I needed to add was not execute it was control.

So instead of doing this;

grant execute on type::dbo.AFFECTEDSERVICELIST to subaccount

I actually needed to do this;

grant control on type::dbo.AFFECTEDSERVICELIST to subaccount

---

Related Topics