Here are a few useful SQL statements I use occasionally, so occasionally that I have to keep searching to dig them out of their “safe place”.
All these scripts have been written with MSSQL in mind, the where clause can easily be modified to change how matches are found, match exactly, match containing etc.
/*************************************************
List all stored procedures containing given text
*************************************************/
--method 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so on sc.id=so.id WHERE sc.text LIKE '%amr%'
--method 2
SELECT o.name,o.type
FROM sys.sql_modules sm
INNER JOIN sys.objects o ON
o.object_id = sm.object_id
WHERE sm.definition LIKE '%amr%'
/*************************************************
List all tables with partially matching columns
*************************************************/
select so.name, sc.name from syscolumns sc
INNER JOIN sysobjects so on sc.id=so.id WHERE sc.name like '%turnover%'
/*************************************************
Use built in views to get schema information
*************************************************/
SELECT * FROM information_schema.tables
ORDER BY table_name
SELECT * FROM information_schema.columns
where column_name like '%amr%'
ORDER BY table_name, ordinal_position
