Hi all,
How can I select the contents of a SP to a text file
Thanks
RobertRobert Bravery wrote:
> How can I select the contents of a SP to a text file
In SQL Server 2005, you can use the OBJECT_DEFINITION function, like
this:
SELECT OBJECT_DEFINITION(OBJECT_ID('ProcedureNa
me'))
In SQL Server 2000, you can query the syscomments table, like this:
SELECT text FROM syscomments WHERE id=OBJECT_ID('ProcedureName')
but if the procedure text is longer than 4K, it will be split across
multiple rows.
To store the result in a file, either use copy/paste (if it's a
one-time job) or a command-line utility like BCP or OSQL/SQLCMD.
Razvan|||> In SQL Server 2000, you can query the syscomments table, like this:
> SELECT text FROM syscomments WHERE id=OBJECT_ID('ProcedureName')
> but if the procedure text is longer than 4K, it will be split across
> multiple rows.
All the more reason to use sp_helptext instead of selecting from system
tables.|||Another one way in SQL Server 2005 is by using the sys.sql_modules
catalog view
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('ProcedureName')
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Razvan Socol wrote:
> Robert Bravery wrote:
> In SQL Server 2005, you can use the OBJECT_DEFINITION function, like
> this:
> SELECT OBJECT_DEFINITION(OBJECT_ID('ProcedureNa
me'))
> In SQL Server 2000, you can query the syscomments table, like this:
> SELECT text FROM syscomments WHERE id=OBJECT_ID('ProcedureName')
> but if the procedure text is longer than 4K, it will be split across
> multiple rows.
> To store the result in a file, either use copy/paste (if it's a
> one-time job) or a command-line utility like BCP or OSQL/SQLCMD.
> Razvan|||And of course there is always INFORMATION_SCHEMA.ROUTINES that works EQUALLY
well in SQL 2000 and SQL 2005.
SELECT
ROUTINE_NAME
, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = {MySprocName}
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Razvan Socol" <rsocol@.gmail.com> wrote in message news:1151582710.965580.171600@.d56g2000cw
d.googlegroups.com...
> Robert Bravery wrote:
>
> In SQL Server 2005, you can use the OBJECT_DEFINITION function, like
> this:
> SELECT OBJECT_DEFINITION(OBJECT_ID('ProcedureNa
me'))
>
> In SQL Server 2000, you can query the syscomments table, like this:
> SELECT text FROM syscomments WHERE id=OBJECT_ID('ProcedureName')
> but if the procedure text is longer than 4K, it will be split across
> multiple rows.
>
> To store the result in a file, either use copy/paste (if it's a
> one-time job) or a command-line utility like BCP or OSQL/SQLCMD.
>
> Razvan
>|||But if your proc is longer than 4000 characters then only the first
4000 character will be returned, the rest will be truncated
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Arnie Rowland wrote:
> And of course there is always INFORMATION_SCHEMA.ROUTINES that works EQUAL
LY well in SQL 2000 and SQL 2005.
> SELECT
> ROUTINE_NAME
> , ROUTINE_DEFINITION
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_NAME = {MySprocName}
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message news:1151582710.965580.
171600@.d56g2000cwd.googlegroups.com...
> --=_NextPart_000_0E97_01C69B57.2E222ED0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2519
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>And of course there is always
> INFORMATION_SCHEMA.ROUTINES that works EQUALLY well in SQL 2000 and SQL
> 2005.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face="Courier New" size=2>SELECT </FONT></DIV>
> <DIV><FONT face="Courier New" size=2>
> ROUTINE_NAME</FONT></DIV>
> <DIV><FONT face="Courier New" size=2> ,
> ROUTINE_DEFINITION</FONT></DIV>
> <DIV><FONT face="Courier New" size=2>FROM
> INFORMATION_SCHEMA.ROUTINES</FONT></DIV>
> <DIV><FONT face="Courier New" size=2>WHERE ROUTINE_NAME =
> {MySprocName}</FONT></DIV>
> <DIV><BR><FONT face=Arial size=2>-- <BR>Arnie Rowland, YACE* <BR>"To be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>*Yet Another certification Exam</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>"Razvan Socol" <</FONT><A
> href="http://links.10026.com/?link=mailto:rsocol@.gmail.com"><FONT face=Arial
> size=2>rsocol@.gmail.com</FONT></A><FONT face=Arial size=2>> wrote in me
ssage
> </FONT><A
> href="http://links.10026.com/?link=news:1151582710.965580.171600@.d56g2000cwd.googlegroups.com"><FONT
> face=Arial
> size=2>news:1151582710.965580.171600@.d56g2000cwd.googlegroups.com</FONT></
A><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> Robert Brav
ery
> wrote:<BR>>> How can I select the contents of a SP to a text
> file<BR>> <BR>> In SQL Server 2005, you can use the OBJECT_DEFINITIO
N
> function, like<BR>> this:<BR>> SELECT
> OBJECT_DEFINITION(OBJECT_ID('ProcedureNa
me'))<BR>> <BR>> In SQL Serv
er
> 2000, you can query the syscomments table, like this:<BR>> SELECT text
FROM
> syscomments WHERE id=OBJECT_ID('ProcedureName')<BR>> but if the procedu
re
> text is longer than 4K, it will be split across<BR>> multiple rows.<BR>
> <BR>> To store the result in a file, either use copy/paste (if it's a<B
R>>
> one-time job) or a command-line utility like BCP or OSQL/SQLCMD.<BR>>
> <BR>> Razvan<BR>></FONT></BODY></HTML>
> --=_NextPart_000_0E97_01C69B57.2E222ED0--|||Quite true. I should have added that as a 'proviso'. (My rule of thumb is
that if the sproc exceeds 4k chars, then it is probably not very ATOMIC and
most likely is a candidate for re-enginering. -it doesn't always work, but
is good to have as a goal.)
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"SQL Menace" <denis.gobo@.gmail.com> wrote in message
news:1151596104.047640.163330@.j72g2000cwa.googlegroups.com...
> But if your proc is longer than 4000 characters then only the first
> 4000 character will be returned, the rest will be truncated
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
> Arnie Rowland wrote:
>|||> Quite true. I should have added that as a 'proviso'. (My rule of thumb is
> that if the sproc exceeds 4k chars, then it is probably not very ATOMIC
> and most likely is a candidate for re-enginering. -it doesn't always work,
> but is good to have as a goal.)
I'd agree. I very rarely see procedures that exceed 2k, except when I am
working on them for other reasons than size (e.g. they are slow, or do
stupid things). I have inherited a few doozies in the past but there are
certainly none that large in any of the systems I currently maintain (never
mind develop).
A|||Aaron Bertrand [SQL Server MVP] wrote:
> All the more reason to use sp_helptext instead of selecting from system
> tables.
Of course; I forgot about sp_helptext.
Razvan
