where are stored procs stored? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Administration (2000)

where are stored procs stored?

Author

Topic

jhilb007
Starting Member

17 Posts

Posted - 2003-05-28 : 12:12:25

I am using syscomments to pull out stored proc text. But I am only getting the first 4000 chars. Where can I go to to retrieve the entire stored proc text?

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-28 : 12:17:09

I'm assuming you're doing this in QA.

Got to Menu Item Tools>Options
and select the results tab. You can change it there.

But what are you doing?

Brett

8-)

jhilb007
Starting Member

17 Posts

Posted - 2003-05-28 : 12:29:08

Actually I am writing a VB app to pull out the stored procs. I only seem to be getting the first 4000 chars. I am grabbing the text in syscomments. But syscomments is a derived table I think, so the full stored proc text must be stored in some other table?

macka
Posting Yak Master

162 Posts

Posted - 2003-05-28 : 12:41:29

Not sure why you are doing it like this - can't you use SQL-DMO for this ?

Anyway, to answer your question - the entire stored proc is held in syscomments, but only 4000 unicode characters per row. If the proc exceeds this limit additional rows will be inserted with an incremented colid value and with the same id.

macka.

--
There are only 10 types of people in the world - Those who understand binary, and those who don't.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-28 : 12:42:54

Well,

If you do sp_help syscomments, you'll see the column is defined as ntext, which is only 4000 bytes on single char data (it store 1 extra byte per so the real size is 8000)

How about:

USE Northwind
GO

CREATE TABLE x (col1 varchar(8000))
GO

INSERT INTO x(Col1) EXEC sp_helptext SalesByCategory
GO

SELECT * FROM x
GO

DROP TABLE x
GO

Just Curious. What are you doing?

Brett

8-)

jhilb007
Starting Member

17 Posts

Posted - 2003-05-28 : 13:06:46

I am trying to create an app that'll go out to a database, search for procs with 'Find' text in them. Then replace the text with the 'Replace' text.

It creates one big script then to put back into QA to run. Yes I could do this manually, but that would slow me down. Lot's of procs to change.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-28 : 13:27:04

Why not script all the sprocs then do a find on the word "FIND".

But, hold on, What's "FIND" in transact SQL?

And even if you do locate a find and replace it with REPLACE, what's the replacement value?

Try this:

USE Northwind
GO

CREATE TABLE x (col1 varchar(8000))
GO

INSERT INTO x(Col1) EXEC sp_helptext SalesByCategory
GO

SELECT * FROM x
GO

SELECT * FROM x WHERE col1 LIKE '%1998%'
GO

UPDATE x Set col1 = REPLACE(col1,'1998','2003') WHERE col1 LIKE '%1998%'
GO

SELECT * FROM x
GO

DROP TABLE x
GO

You might want to add an identity column to ensure what foes comes out the same way, because I believe there is no gaurentee.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources