1

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.

Jeff Hernandez
  • 355
  • 4
  • 13
  • 1
    INFORMATION_SCHEMA.ROUTINES is not necessarily the best solution because ROUTINE_DEFINITION is limited to 4000 characters. – NicVerAZ Nov 14 '18 at 23:16
  • Ah, I didn't know that. Thanks. We definitely have some large stored procedure in our DB. I probably can't rely on that. – Jeff Hernandez Nov 14 '18 at 23:19
  • Try encapsulation. Long stored procs are not a good thing. I have seen insanely long ones which were a nightmare to work on. – NicVerAZ Nov 15 '18 at 14:43

1 Answers1

2

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.

NicVerAZ
  • 409
  • 1
  • 4
  • 10