SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How search in stored procedures...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrsaif
Starting Member

43 Posts

Posted - 04/14/2008 :  01:41:55  Show Profile  Send mrsaif a Yahoo! Message  Reply with Quote
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 - 04/14/2008 :  02:31:54  Show Profile  Visit ayamas's Homepage  Reply with Quote
Try this
select * from syscomments where [text] like '%delete from myTable%'
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 04/14/2008 :  04:08:38  Show Profile  Reply with Quote
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

Sweden
30218 Posts

Posted - 04/14/2008 :  04:18:09  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Norway
3271 Posts

Posted - 04/14/2008 :  04:51:41  Show Profile  Reply with Quote
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

Edited by - Lumbago on 04/14/2008 04:53:20
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 04/14/2008 :  04:55:03  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/14/2008 :  09:19:09  Show Profile  Reply with Quote
Geez, I have lots of sprocs that exceed 4k.

e4 d5 xd5 Nf6
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 04/14/2008 :  11:53:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/14/2008 :  12:30:39  Show Profile  Reply with Quote
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

Norway
3271 Posts

Posted - 04/15/2008 :  02:20:35  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000