Search Stored Procedures, Functions & Triggers for Keyword

Question

I'm getting the error:

Invalid column name 'Reserved'

I'm not sure where in my database code this is, and have 100s of stored procedures, functions & triggers which I would have to manually look through to find this reference to an unknown column.

Is there a way of quickly searching for which objects contain this keyword?

Carlos Quintanilla · Accepted Answer · 2011-07-18T15:55:58.733

5

Try this:

SELECT o.name, t.TABLE_NAME, c.text 
  FROM syscomments c 
  JOIN sysobjects o 
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.Tables t
    ON  c.text LIKE '%reserved%'

Or if you looking for tables with a column called Reserved you do:

select o.name 
from sys.objects o 
inner join sys.columns c 
on o.object_id = c.object_id
where o.type = 'u' and c.name = 'Reserved'

If you want to filter your search on certain type of objects such as procedures, views, or tables you can do it adding 'p' for stored procedures, 'v' for views, 'u' for user defined tables, 'tr' for triggers ex:

AND o.type = 'p'

edited Jul 18 '11 at 15:55

answered Jul 18 '11 at 15:33

Carlos Quintanilla

12,937
3
22
25

Valid for ***`views, stored procedures, user defined tables, triggers`***, and for _**functions, Constraints, Rules, Defaults**_ ? – Kiquenet Aug 12 '16 at 08:44
How filter a column name like ***[NumeroTotal]*** eg: `([NumeroUsadas]<=[NumeroTotal] AND [NumeroTotal]>=(0))`. For me this `c.text LIKE '%[NumeroTotal]%'` not works, neither `'%[[NumeroTotal]]%'` – Kiquenet Aug 12 '16 at 09:02
`JOIN INFORMATION_SCHEMA.Tables t` ***without*** `on t.xxx = c.yyy` ? only `ON c.text` ? IMHO Better: `SELECT o.name, o.type, o.xtype, c.text , USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name' FROM syscomments c JOIN sysobjects o ON c.id = o.id where c.text LIKE '%NumeroTotal%' or c.text LIKE '%NumeroUsadas%'` – Kiquenet Aug 12 '16 at 09:49
_Just be aware that the syscomments table stores the values in 8000-character chunks, so if you are unlucky enough to have the text you're searching for split across one of these boundaries, you won't find it with this method_ via http://stackoverflow.com/a/4222372/206730 – Kiquenet Aug 12 '16 at 09:55

score 0 · Answer 2 · edited May 20 '20 at 14:51

0

Using RedGate's SQL Search extension might be able to help as well

edited May 20 '20 at 14:51

KyleMit

30,350
66
462
664

answered Jul 18 '11 at 15:35

Permas

228
6
11

Search Stored Procedures, Functions & Triggers for Keyword

2 Answers2