T-SQL Searching through the schema

For SQL Server 2005:

---

Some examples:
select * from sys.objects
select * from sys.sql_modules <-- Stored Procedures and Views
select * from sys.columns
select * from sys.triggers


Searching the text of all stored procedures:
select o.name from sys.sql_modules m
inner join sys.objects o
on m.object_id = o.object_id
where m.definition like '%string you are looking for%'

Searching for tables that contain the column (field) you want:
select a.name
from sys.objects a
inner join sys.columns b
on a.object_id = b.object_id
where b.name='column name you are looking for'

Another way:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION     FROM INFORMATION_SCHEMA.ROUTINES     WHERE ROUTINE_DEFINITION LIKE '%foobar%'     AND ROUTINE_TYPE='PROCEDURE'

For SQL Server 2000:

---

Searching the text of all stored procedures:
select object_name(id)
from syscomments
where objectproperty(id,'IsProcedure') = 1
and [text] like '%string you are looking for%'

---

To search across multiple DATABASES for a particular TABLE:


sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%tablenameyouarelookingfor%'''


or if that undocumented proc is not avail, try this:

DECLARE @SQL NVARCHAR(max)

SET @SQL = stuff((

            SELECT '

UNION

SELECT ' + quotename(NAME, '''') + ' as Db_Name, Name collate SQL_Latin1_General_CP1_CI_AS as Table_Name

FROM ' + quotename(NAME) + '.sys.tables WHERE NAME LIKE ''%'' + @TableName + ''%'''

            FROM sys.databases

            ORDER BY NAME

            FOR XML PATH('')

                ,type

            ).value('.', 'nvarchar(max)'), 1, 8, '')

--PRINT @SQL;

EXECUTE sp_executeSQL @SQL

    ,N'@TableName varchar(30)'

    ,@TableName = 'items'










To find out the dates of last modified and created for Stored Procs

SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC