Ever needed to figure out how many tables or stored procedures were going to be affected by some design change in one of your tables, like a field being renamed?
Aside from using a 3rd party tool, you can simply query various system tables to get this information.
For tables:
select 'TableName' = o.name,
'ColumnName' = c.name,
'DataType' = t.name
from syscolumns c
join sysobjects o on c.id = o.id
join systypes t on c.xusertype = t.xusertype
where o.xtype = 'U'
and c.name = 'MyFieldName'
order by o.name
'ColumnName' = c.name,
'DataType' = t.name
from syscolumns c
join sysobjects o on c.id = o.id
join systypes t on c.xusertype = t.xusertype
where o.xtype = 'U'
and c.name = 'MyFieldName'
order by o.name
For stored procedures:
select o.name,
c.text
from sysobjects o
join syscomments c on o.id = c.id
where o.xtype = 'P'
and o.category = 0
and c.text like '%MyFieldName%'
order by o.name
c.text
from sysobjects o
join syscomments c on o.id = c.id
where o.xtype = 'P'
and o.category = 0
and c.text like '%MyFieldName%'
order by o.name
It's easily customizable to fit your needs.
You can find more information on the sysobjects table here:
0 comments: