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

Matt Pavey

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 third party tool, you can query the system catalog directly 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

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

It is easily customizable to fit your needs.

One note from the present day: sysobjects, syscolumns, and systypes are backward compatibility views left over from SQL Server 2000. They still work, but the modern equivalents are sys.objects, sys.columns, and sys.types. The same table query written against those looks like this:

SELECT 'TableName'  = o.name,
       'ColumnName' = c.name,
       'DataType'   = t.name
FROM   sys.columns c
JOIN   sys.objects o ON c.object_id = o.object_id
JOIN   sys.types   t ON c.user_type_id = t.user_type_id
WHERE  o.type = 'U'
AND    c.name = 'MyFieldName'
ORDER BY o.name