/* User-defined data types are a well instrument to defined database width a uniform definition for columns with the same data content and meaning; like it's used in AdventureWorks sample database. But you can redefine a UDT only if it's not used, so you have to check the usage before. This Transact-SQL script list all objects using a user defined data type which the system base type to give a quick overview. It lists columns of tables/views as well as parameter of stored procedures. Works with SQL Server 2005 and higher versions in all editions. Link: sys.column_type_usages: http://msdn.microsoft.com/en-us/library/ms174977.aspx sys.parameter_type_usages: http://msdn.microsoft.com/en-us/library/ms187995.aspx */ -- Usage of User-Defined Datatypes ;WITH udt AS (SELECT CTU.object_id AS ObjectID ,CTU.column_id AS ColumnID ,CTU.user_type_id AS UserTypeID FROM sys.column_type_usages AS CTU UNION ALL SELECT PTU.object_id AS ObjectID ,PTU.parameter_id AS ColumnID ,PTU.user_type_id AS UserTypeID FROM sys.parameter_type_usages AS PTU) ,col AS (SELECT COL.object_id AS ObjectID ,COL.column_id AS ColumnID ,COL.name AS Name FROM sys.columns AS COL UNION ALL SELECT PAR.object_id AS ObjectID ,PAR.parameter_id AS ColumnID ,PAR.name AS Name FROM sys.parameters AS PAR) SELECT SCH.name + N'.' + OBJ.name AS ObjectName ,OBJ.type_desc AS ObjectType ,col.name AS ColumnName ,UTP.name AS UserTypeName ,STP.name AS SystemTypeName FROM udt INNER JOIN sys.objects AS OBJ ON udt.ObjectID = OBJ.object_id INNER JOIN sys.schemas AS SCH ON OBJ.schema_id = SCH.schema_id INNER JOIN col ON udt.ObjectID = col.ObjectID AND udt.ColumnID = col.ColumnID INNER JOIN sys.types AS UTP ON udt.UserTypeID = UTP.user_type_id INNER JOIN sys.types AS STP ON UTP.system_type_id = STP.system_type_id AND UTP.system_type_id = STP.user_type_id ORDER BY ObjectName ,ObjectType ,ColumnName;
