Friday, January 18, 2008

[Code] some quick & dirty code to find things

I still wind up using these all the time, even under 2005.

--Look for any code that references a phrase of code or table name
select * from sysobjects
inner join syscomments
on sysobjects.id = syscomments.id
and text like '%phrase of code%'

--Same as above, but for all databases.
--(sp_msforeachdb is an amazing piece of code, and you should use it)
sp_msforeachdb
'select ''?'' as dbname, *
from [?].dbo.sysobjects sysobjects
inner join [?].dbo.syscomments syscomments
on sysobjects.id = syscomments.id
and text like ''%phrase of code%'''

--look for that phrase of code, but in your job steps
select *
from msdb.dbo.sysjobs
where job_id in
(
select job_id
from msdb.dbo.sysjobsteps
where command like '%phrase of code%'
)

--look in each database for a field with a particular data type and name.
sp_msforeachdb
'select *
from [?].information_schema.columns
where data_type = ''int''
and column_name like ''%sequence_number%'''

--Search DTS package names and descriptions
SELECT * FROM msdb.dbo.sysdtspackages WHERE NAME LIKE '%%' OR description LIKE '%%'

No comments: