sqlt-sqluser-defined-data-types

Identifying all stored procedures and tables using a User-Defined Data Type in T-Sql

I'm using SSMS and I have a User-Defined Data Type which was created in the early 2000 with a rule object attached to it.

Many tables and stored procedures use this User-Defined Data Type as a type. I want to alter these tables and stored procs to take out this UDT so I can replace them with check constraints, but I'm having trouble identifying all the tables and stored procs in which this UDT is used as a type.

I've been looking at old scripts and using sp_help table_name to seek out these instances, but I was wondering if there's a way to find all the tables/columns and stored procs which use a certain user-defined data type.

Thank you.

EDIT: I figured out how to find all the uses of user-defined data types on tables

SELECT TABLE_NAME, COLUMN_NAME, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DOMAIN_NAME = 'UDT_name'

For stored procedures, I removed my discovered method because NicVerAZ linked a better way to accomplish this below.

Solution

  • Refer to the article below on how to properly search for a string in a stored procedure definition:

    Search text in stored procedure in SQL Server

    As I posted in a comment above, ROUTINE_DEFINITION is an NVARCHAR(4000) and longer stored procedures have their definition truncated.

    Your second method is not bad, it gets it done but yes your first is more correct.