Where are stored the stored procedures?

Question

Where are stored the stored procedures?

Pietro-155175

Grasshopper

Points: 20
More actions
March 7, 2008 at 1:22 pm

#182987

Where "SQL Server" save the text of a stored procedure? In a table? what's the name of this or these tables?
I need to search all stored procedures that contain a determined word.
Could you help me?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SELECT object_name(id) FROM syscomments where [text] like ....

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 2

WOW, I just happened to be working on exactly that today. Since the syscomments table can only hold 4000 characters, some stored procedures will be split into multiple records. if this split happens to fall in the middle of a word you are searching for, it may be missed. This script reconstructs the text into a single record for searching. Just change the search_critera at the end of the script and you should be good to go. Unfortunately, until I make improvements, it will only search in your current database.

--script that puts sproc names/text in a table (#sprocfinal) so you can search them for a string.

declare @sprocs table(name varchar(500),id int identity)

CREATE table #sproctext (text varchar(4000),number int)

CREATE TABLE #sprocFinal (NAME VARCHAR(500),TEXT VARCHAR(MAX))

declare @sprocname varchar(500)

declare @max-2 int

declare @count int

declare @text varchar(max)

DECLARE @max2 INT

DECLARE @number INT

SELECT @text = ''

insert into @sprocs(name)

select name from sysobjects WHERE TYPE = 'p'

select @count = 1,@max= max(id) from @sprocs

while @count <= @max-2

BEGIN

select @sprocname = name from @sprocs where id = @count

DELETE #sproctext

INSERT INTO #sproctext(TEXT,number)SELECT TEXT,number FROM syscomments WHERE id = OBJECT_ID(@sprocname)

SELECT @max2 = MAX(number) FROM #sproctext

SELECT @number = 0

WHILE @number <= @max2

BEGIN

SELECT @text = @text + TEXT FROM [#sproctext] WHERE number = @number

SET @number = @number + 1

END

INSERT INTO #sprocfinal(NAME,TEXT)SELECT @sprocname,@text

SET @text = ''

select @count = @count + 1

END

DROP TABLE #sproctext

SELECT name FROM #sprocfinal WHERE TEXT LIKE '%SEARCH_CRITERIA%'

DROP TABLE #sprocfinal

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 3

...or... if you just want the names of the objects so you can go look at them...

--===== Find name of proc, view, function, etc containing text in DDL

SELECT DISTINCT so.NAME, so.XTYPE

FROM dbo.SYSOBJECTS so,

dbo.SYSCOMMENTS sc

WHERE so.ID = sc.ID

AND sc.TEXT LIKE '%searchwordhere%'

ORDER BY so.XTYPE,so.NAME

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TraderSam SSCrazy Points: 2931 More actions · Answer 4

Adam Angelini (3/7/2008)
Since the syscomments table can only hold 4000 characters, some stored procedures will be split into multiple records. if this split happens to fall in the middle of a word you are searching for, it may be missed.

I did not know this...very interesting because I am doing this alot right now in a database conversion. Fortunately, my SP's are relatively small.

Thanks for posting, Adam!

If it was easy, everybody would be doing it!;)

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 5

I missed that in Adam's post (too much of a hurry). He's absolutely correct.

Only problem is, this is an SQL Server 2k post... Adam's solution uses VARCHAR(MAX) which isn't available until 2k5.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

And if using SQL 2005, there's no need for code to reconstruct procedures from syscomments as you can seelct rather from sys.sql_modules, which does not split objects into 4000 character chunks

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 7

good point on the varchar(max). Also good info with the sys.sql_modules. I was not aware of that one.

Pietro-155175 Grasshopper Points: 20 More actions · Answer 8

Pietro-155175

Grasshopper

Points: 20

March 14, 2008 at 10:17 am

#789745

Thanks Adam,

you gave me the solution.

Bye