--
--Search words in all DBs
Declare @searchSTR varchar(4000), @database varchar(255), @sql nvarchar(4000),@sql2 nvarchar(4000)
--Set the string to look for here
Set @searchSTR = 'YOUR SEARCH TERM HERE'
IF OBJECT_ID('tempdb..#ProcList') IS NOT NULL
Drop Table #ProcList
Create table #ProcList(
ObjectID Varchar(255),
[Database] varchar(255),
[ObjectType or JobName] varchar(255),--[JobName] varchar(255),
[StepName] varchar(255),
ProcName varchar(255),
CreationDate datetime,
ModifiedDate datetime,
Code nvarchar(max)
)
declare DB cursor fast_forward for
SELECT name FROM master..sysdatabases (nolock)
open DB
fetch next from DB into @database
while @@fetch_status = 0
begin
Set @sql =
N'Use ['+@database+']
Insert into #ProcList
SELECT Object_ID,'''+@database+''',''SQL_STORED_PROCEDURE'',NULL,Name,Create_Date,modify_date,ltrim(OBJECT_DEFINITION(OBJECT_ID))
FROM sys.procedures (nolock)
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%'+@searchSTR+'%''
Insert into #ProcList
SELECT ID,'''+@database+''',''VIEW'',NULL,sv.Name,sv.Create_date,sv.Modify_date, ltrim(sc.[text] )
from sys.sysComments sc (nolock)
inner join sys.views sv (nolock)
on sv.Object_id = sc.id
where sc.[text] like ''%'+@searchSTR+'%''
Insert into #ProcList
Select Object_ID,'''+@database+''',Type_Desc,NULL,Name, Create_date,Modify_date, NULL
from sys.Objects
where name Like ''%'+@searchSTR+'%'''
Print @sql
BEGIN TRY
exec sp_executesql @sql
END TRY
BEGIN CATCH
Insert into #ProcList
SELECT '',@database,NULL,NULL,NULL,getdate(),getdate(),ERROR_MESSAGE() +' With ErrorCode = '+ convert(varchar,ERROR_NUMBER())
GOTO Next1
END CATCH
Next1:
fetch next from DB into @database
end
close DB
deallocate DB
Set @sql2 = 'Insert into #ProcList
SELECT a.Job_ID,Database_Name,Name,Step_Name,NULL,Date_Created,Date_Modified, ltrim(Command)
FROM msdb..sysjobs a (nolock) INNER JOIN
msdb..sysjobsteps b (nolock) ON a.job_id = b.job_id
WHERE b.command LIKE ''%'+@searchSTR+'%'''
BEGIN TRY
Print @sql2
exec sp_executesql @sql2
END TRY
BEGIN CATCH
Insert into #ProcList
SELECT '',@database,NULL,NULL,NULL,getdate(),getdate(),ERROR_MESSAGE() +' With ErrorCode = '+ convert(varchar,ERROR_NUMBER())
END CATCH
Select * from (
SelecT *,
Row_Number() over (Partition By ObjectID order by Code Desc) rn
from #ProcList
) a
Where rn = 1
--And CreationDate > '2011-08-01' --ENTER A CREATION TIME FILTER HERE IF NEEDED
--and [ObjectType or JobName] in ('USER_TABLE') --ENTER OBJECT TYPE HERE IF NEEDED
Order by [ObjectType or JobName],[Database],CreationDate
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)