查找SQL Server服务器数据类型的依赖项

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?

是否有一个命令或一组表我可以查看以确定SQL Server服务器2005中的哪些表,存储过程和视图具有某种用户定义的数据类型?

2 个解决方案

#1

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

表相对容易,sys.columns和sys.types允许您将列链接到类型。下面的查询将解决此问题。

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.

编辑:正如G Mastros上面所示,您可以获得具有类似查询的参数。

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'

#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'

#1