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
kemiller2002
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
ConcernedOfTunbridgeWells
source
to share
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
G mastros
source
to share