Finding a dependency of a SQL Server server data type

Question

Is there a command, or a set of tables I can look at to determine which tables, stored procedures and views in SQL Server server 2005 have a certain user defined data type?

Answer 1

Tables are relatively easy, sys.columns and sys.types allow you to link columns to types. The query below will get this out.

select s.name
      ,o.name
      ,c.name
      ,t.name
  from sys.schemas s
  join sys.objects o
    on o.schema_id = s.schema_id
  join sys.columns c
    on c.object_id = o.object_id
  join sys.types t
    on c.user_type_id = t.user_type_id
 where t.name = 'Foo'

EDIT: as G Mastros has shown above, you can get parameters with a similar query.

select s.name
      ,o.name
      ,p.name
      ,t.name
  from sys.schemas s
  join sys.objects o
    on o.schema_id = s.schema_id
  join sys.parameters p
    on p.object_id = o.object_id
  join sys.types t
    on p.user_type_id = t.user_type_id
 where t.name = 'Foo'

Answer 2

For tables and views:

Select * 
From   Information_Schema.Columns 
Where  DOMAIN_NAME = 'YourUserDefinedTypeName'

For procedures and functions:

Select * 
From   Information_Schema.PARAMETERS 
Where  USER_DEFINED_TYPE_NAME = 'YourUserDefinedTypeName'