Friday, September 14, 2007

Query to retrieve a list of SQL objects (tables or stored procedures)

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' =,
'ColumnName' =,
'DataType' =
from        syscolumns c
join          sysobjects o on =
join          systypes t on c.xusertype = t.xusertype
where       o.xtype = 'U'
and = 'MyFieldName'
order by
For stored procedures:
from       sysobjects o
join        syscomments c on =
where     o.xtype =
and        o.category = 0
and        c.text like
order by
It's easily customizable to fit your needs.
You can find more information on the sysobjects table here: