Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How search in stored procedures...

Author  Topic 

mrsaif
Starting Member

43 Posts

Posted - 2008-04-14 : 01:41:55
Is there any way to search in stored procedures? If in one of the stored procedure there is an a query like "delete from myTable...."
and i want to get all those procedures that contained this query text.......

Muhammad Saifullah

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-04-14 : 02:31:54
Try this
select * from syscomments where [text] like '%delete from myTable%'
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-14 : 04:08:38
This script will search all objects for any given text you specify (it's a slightly altered version of this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65666)

set nocount on

DECLARE
@string varchar(1000),
@ShowReferences char(1)

set @string = 'your search term'
set @ShowReferences = 'N'


declare @errnum int ,
@errors char(1) ,
@rowcnt int ,
@output varchar(255)

select @errnum = 0 ,
@errors = 'N' ,
@rowcnt = 0 ,
@output = ''

DECLARE @Results table
(
Name varchar(55),
Type varchar(12),
DateCreated datetime,
ProcLine varchar(4000)
)


IF (@ShowReferences = 'N')
BEGIN
insert into @Results
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select distinct
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END
ELSE
BEGIN
insert into @Results
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = text
from sysobjects SO
join syscomments SC on SC.id = SO.id
where SC.text like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
where SO.name like '%' + @string + '%'
union
select
'Name' = convert(varchar(55),SO.name),
'Type' = SO.type,
crdate,
'Proc Line' = ''
from sysobjects SO
join syscolumns SC on SC.id = SO.ID
where SC.name like '%' + @string + '%'
order by 2,1
END


IF (@ShowReferences = 'N')
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated
from @Results
order by 2,1
END
ELSE
BEGIN
select Name,
'Type' = Case (Type)
when 'P' then 'Procedure'
when 'TR' then 'Trigger'
when 'X' then 'Xtended Proc'
when 'U' then 'Table'
when 'C' then 'Check Constraint'
when 'D' then 'Default'
when 'F' then 'Foreign Key'
when 'K' then 'Primary Key'
when 'V' then 'View'
else Type
end,
DateCreated,
ProcLine
from @Results
order by 2,1
END


--
Lumbago
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 04:18:09
SysComment still saves code in chunks, so if the phrase you search is stretched over two chunks you will not find it.
Try this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99053



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-14 : 04:51:41
To be honest I prefer the one I have. Your script doesn't search in the object names themselves (RoutineName) and it doesn't search for column names in tables. And I can't remember ever having an object larger than 4k characters so this fact dosn't bother me too much.

Edit: actully I do have a few objects larger than 4k characters but I'll just have to live with that I guess. I still prefer the one I posted

--
Lumbago
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-14 : 04:55:03
or script out to text file and do search
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/13/script-out-procedures-and-functions-part-2.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-14 : 09:19:09
Geez, I have lots of sprocs that exceed 4k.

e4 d5 xd5 Nf6
Go to Top of Page

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-14 : 11:53:22
I just did this recently. Script all stored procs to a single text file, open notepad and do a search on the string you're looking for. I have hundreds of stored procs and it took all of about five minutes to complete. BTW - I run SQL2K so I did the scripting via EM. I assume SQL2K5 has a similar feature.

Terry
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-04-14 : 12:30:39
Yeah, but the odd thing is that scripting in 2005 takes for FREAKIN' EVER to run.
It literally takes about a 30 to 60 seconds per object. 100 tables, sprocs, views and functions....100 minutes.

e4 d5 xd5 Nf6
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-04-15 : 02:20:35
Why would you want to script out everything when pesos or "my" (actually "datagod"s) script will do the same thing in a matter of seconds?? I don't get it...I have this script as a template in SSMS and I run a global search in a database with more than a thousand objects in less than a minute.

--
Lumbago
Go to Top of Page
   

- Advertisement -