Sql-server – Check if any table has a user defined type

sql server

I've seen on sys.table_types that I have 8 user-defined table types on my db.

How can I check if there is any table (1000+) on my db that are currently using any of these user-defined table types before dropping them.

Best Answer

How can I check if there is any table (1000+) on my db that are currently using any of these user-defined table types before dropping them.

First things first

A table type is a type from which table variables or table-valued parameters could be declared

Source

This means that table types can be used by table variables / TVP's and these can be used in parameter calls. An example of this is a stored procedure calling a TVP.

Table types are not permanent tables or used by permanent tables.

sys.parameter_type_usages

You can use the sys.parameter_type_usages dmv to get the parameters that are mapped to user defined types, these include Table Valued Parameters.

Returns one row for each parameter that is of user-defined type.

Source

SELECT OBJECT_NAME(ptu.object_id),tt.name
FROM sys.parameter_type_usages ptu
INNER JOIN sys.table_types tt
on ptu.user_type_id = tt.user_type_id;

Testing

CREATE TYPE LocationTableType 
   AS TABLE
      ( LocationName VARCHAR(50)
      , CostRate INT );
GO
/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo. usp_InsertProductionLocation
   @TVP LocationTableType READONLY
      AS
      SET NOCOUNT ON
      INSERT INTO AdventureWorks.Production.Location
         (
            Name
            , CostRate
            , Availability
            , ModifiedDate
         )
      SELECT *, 0, GETDATE()
      FROM @TVP;
GO

test statements from here

Running the query at the top gives us both the procedure & the table type used 

(No column name)    name
usp_InsertProductionLocation    LocationTableType

The call to the procedure simply uses the table type and a self defined table variable:

DECLARE @LocationTVP AS LocationTableType;
/* Add data to the table variable. */
INSERT INTO @LocationTVP (LocationName, CostRate)
   SELECT Name, 0.00
   FROM AdventureWorks.Person.StateProvince;

/* Pass the table variable data to a stored procedure. */
EXEC usp_InsertProductionLocation @LocationTVP;

Statements also from here

Related Question