|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Contents of a SPHi all,
How can I select the contents of a SP to a text file Thanks Robert Robert 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, likethis: SELECT OBJECT_DEFINITION(OBJECT_ID('ProcedureName')) 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: All the more reason to use sp_helptext instead of selecting from system > 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. tables. Aaron Bertrand [SQL Server MVP] wrote:
> > In SQL Server 2000, you can query the syscomments table, like this: Of course; I forgot about sp_helptext.> > 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. Razvan 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: Show quote > Robert 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('ProcedureName')) > > 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} -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "Razvan Socol" <rso***@gmail.com> wrote in message news:1151582710.965580.171600@d56g2000cwd.googlegroups.com... > Robert 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('ProcedureName')) > > 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: Show quote > 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" <rso***@gmail.com> wrote in message news:1151582710.965580.171600@d56g2000cwd.googlegroups.com... > > Robert 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('ProcedureName')) > > > > 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 > > > ------=_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="mailto:rso***@gmail.com"><FONT face=Arial > size=2>rso***@gmail.com</FONT></A><FONT face=Arial size=2>> wrote in message > </FONT><A > href="news:1151582710.965580.171***@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 Bravery > 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_DEFINITION > function, like<BR>> this:<BR>> SELECT > OBJECT_DEFINITION(OBJECT_ID('ProcedureName'))<BR>> <BR>> In SQL Server > 2000, you can query the syscomments table, like this:<BR>> SELECT text FROM > syscomments WHERE id=OBJECT_ID('ProcedureName')<BR>> but if the procedure > 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<BR>> > 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.) -- Show quoteArnie Rowland, YACE* "To be successful, your heart must accompany your knowledge." *Yet Another Certification Exam "SQL Menace" <denis.g***@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: >> 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" <rso***@gmail.com> wrote in message >> news:1151582710.965580.171600@d56g2000cwd.googlegroups.com... >> > Robert 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('ProcedureName')) >> > >> > 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 >> > >> ------=_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="mailto:rso***@gmail.com"><FONT face=Arial >> size=2>rso***@gmail.com</FONT></A><FONT face=Arial size=2>> wrote in >> message >> </FONT><A >> href="news:1151582710.965580.171***@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 >> Bravery >> 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_DEFINITION >> function, like<BR>> this:<BR>> SELECT >> OBJECT_DEFINITION(OBJECT_ID('ProcedureName'))<BR>> <BR>> In SQL >> Server >> 2000, you can query the syscomments table, like this:<BR>> SELECT text >> FROM >> syscomments WHERE id=OBJECT_ID('ProcedureName')<BR>> but if the >> procedure >> 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<BR>> >> 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 I'd agree. I very rarely see procedures that exceed 2k, except when I am > 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.) 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  |
|||||||||||||||||||||||
Other interesting topics