Content OF Stored Procedure ?? - 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 2005 Forums

Transact-SQL (2005)

Content OF Stored Procedure ??

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-03-31 : 03:59:01

Hi ,

Cay anyone tell me where is the content of Stored Procedure
stored in the database.. in which table

Thanks & Regards

Arv

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-31 : 05:01:09

syscomments table of your database.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-31 : 05:25:52

use object_definition function instead of syscomments.
object_definition doesn't split large sprocs in more than one row since it returns nvarchar(max).

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-31 : 05:25:58

in sql 2005 you should use "sys.sql_modules" table not syscomments.

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-04-02 : 03:55:52

quote:
Originally posted by PeterNeo

in sql 2005 you should use "sys.sql_modules" table not syscomments.

Hi,

Thanks for ur information... but how can i get the
content of a sp from another database ????

Regards

Arv

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 03:57:37

exec mydb..sp_helptext 'myproc'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-04-02 : 04:02:38

quote:
Originally posted by nr

exec mydb..sp_helptext 'myproc'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hi,

I want the content to store some temp table ...
which i have to run from any other database ...

Thanks & Regards

ARV

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 04:06:11

create table #a (t text)
insert #a (t)
exec mydb..sp_helptext 'myproc'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-04-02 : 04:09:23

quote:
Originally posted by nr

create table #a (t text)
insert #a (t)
exec mydb..sp_helptext 'myproc'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks nr..... i got it

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-04-02 : 04:12:33

Hi All,

But I want to fetch all the procs content from another database...
how could i run the query and get result from sys tables ??

Regards

Arv

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 04:32:29

May be this:-

create table #a (t text)

DECLARE @SP_Name varchar(100)

SELECT @SP_Name=MIN(name)
FROM sysobjects
where type='p'

WHILE @SP_Name IS NOT NULL
BEGIN

insert #a (t)
exec mydb..sp_helptext @SP_Name

SELECT @SP_Name=MIN(name)
FROM sysobjects
where type='p'
AND name>@SP_Name

END

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 04:37:53

insert #a (t)
select definition from mydb.sys.sql_modules

You could also create a text file from each SP by running a .vbs file
dim svr
dim db
Dim obj
set svr = createobject("SQLDMO.SQLServer")
svr.loginsecure = true
svr.Connect "Myserver\MyInstance"
Set db = svr.Databases("MyDb")

For Each obj In db.StoredProcedures
if obj.SystemObject = 0 then
obj.script 69, obj.Name & ".txt"
end if
Next

svr.disconnect
set svr = nothing

visakh16 that needs to be mydb..sysobjects

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 05:26:24

quote:
Originally posted by aravindt77

Hi All,

But I want to fetch all the procs content from another database...
how could i run the query and get result from sys tables ??

Regards

Arv

Script out and run them in different database
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources