Search all tables, stored procs for a misspelled word 1

[warelock]

Morning ! This is perhaps a dumb question.

We have mispelled a word - sometimes - in SOME table columns and in SOME stored procedures. (It is 'milage' rather than 'mileage'.) We become aware of this when we run a job and failure occurs because of a mismatch between the table and the stored proc. Ouch.

What we would like to do is to search every table in a given database and check whether any column in any table contains the misspelled word. We'd also like to be able to do this with all stored procedures in a database. We can do this by hand but ... thought SQL Server might be able to help do it with less chance of human error ;-)

I realize this is a strange request - I really don't know whether this is possible at all. Appreciate any advice you can give !!

thanks,

ethan

 

[ptheriault]

This will find your column.

select * from information_schema.columns where column_name = 'milage'

I'm not sure how to find it in the procedure.


- Paul
- Database performance looks fine, it must be the Network!

 

[warelock]

Wow. Thanks, Paul, that really helped !

 

[ptheriault]

This might find your procedure.

select *
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_DEFINITION like 'milage'

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

I forgot the wild card

select *
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_DEFINITION like '%milage%'

- Paul
- Database performance looks fine, it must be the Network!

 

[warelock]

Again ... wow. Thanks, man. Where can I learn Stuff like this ? MS sites ?

 

[ptheriault]

I get a lot of information from these sites.

http://www.sswug.org/Default.asp?

http://www.sqlservercentral.com/default.asp

http://www.databasejournal.com/

And I've learned a ton of stuff from this site. The experts here like gmmastros, bborissov, SQLDenis and mrDenny really know their stuff!

And if all else fails. I google my question.

- Paul
- Database performance looks fine, it must be the Network!