Clever Geek Handbook

Finding a SQL Server Data Type Dependency

Is there a command or set of tables that I can look at which tables, stored procedures, and views in SQL Server 2005 have a specific UDT?

0


source to share


2 answers


Tables are relatively simple, sys.columns and sys.types allow you to associate columns with types. The next request will get this.

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 showed 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'
+1


source


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'
+3


source






More articles:


All Articles