Re: Fwd: Backing up stored procedures using T-SQL

Hi, James--

A list of stored procedures can be obtained by looking at
the sysobjects
table for rows with a type of 'p'.

The text of each stored procedure is then contained in the
syscomments table
where syscomments.id = sysobjects id and syscomments.uid= id
of object owner
in the sysusers table.

So you could probably put together something quick &
dirty without a lot of
effort that would make backup copies of all stored
procedures. Easier
probably to do it in VB or C# but possible to do it soley in
T-SQL.

My questions are:
1) why not backup and restore the entire database? Is this
for testing?
2) do you need to worry about other types of programmable
objects in SQL
such as functions, views, triggers, etc.?

-Joe


On Dec 18, 2007 12:53 PM, James Macdiarmid
<jmacdiarmidcaci.com> wrote:

>
> Hi Joe,
>
> No. I've been asked to write scripts that do the
following:
>
> Backup :           Rename the original stored
procedure
> Implement:        Create a new procedure
> Rollback:           Rename the stored procedure, (which
was renamed in
> backup script) back to original name.
> Cleanup:           Drop the stored procedures created
in backup script.
>
> and I was just trying to find some examples of doing
these types of
> things.
>
> Thanks for your reply,
>
>
> Jim MacDiarmid
> CACI
> 4795 Meadow Wood Lane
> Chantilly, VA. 20151-2222
> Voice (703) 679-5454
>
>
>  *"Joe Lynds" <jljlion.com>*
> Sent by: jlyndsgmail.com
>
> 12/18/2007 11:28 AM
>   To
> jmacdiarmidcaci.com  cc
>   Subject
> Fwd: [aspnet-databases] Backing up stored procedures
using T-SQL
>
>
>
>
> Hi, James--
>
> Are you looking for some kind of source code control
type thing? I have
> had a similar problem and put this together to address
it
> **http://www.codeple
x.com/sqlautodoc *<http://www.cod
eplex.com/sqlautodoc>
>
> -Joe
>
> ---------- Forwarded message ----------
> From: *James Macdiarmid* <*jmacdiarmidcaci.com*
<jmacdiarmidcaci.com>>
> Date: Dec 18, 2007 10:07 AM
> Subject: [aspnet-databases] Backing up stored
procedures using T-SQL
> To: *aspnet-databasesaspadvice.com*
<aspnet-databasesaspadvice.com>
>
>
> I'm not a DBA but I've been asked to provide scripts to
backup, rollback,
> implement and delete a stored procedure. Could anyone
point me to samples
> on how to do this type of thing using T-SQL?  I've been
attempting to
> google  Backup Stored Procedure Scripts samples but I
wasn't getting any
> hits.
>
> Thanks in advance!
>
> Jim MacDiarmid
> CACI
> 4795 Meadow Wood Lane
> Chantilly, VA. 20151-2222
> Voice (703) 679-5454
>
>
> Need SQL Advice? *http://sqladvice.com *
<http://sqladvice.com/>
> Need RegEx Advice? *http://regexadvice.com*
<http://regexadvice.com/&g
t;
> Need XML Advice? *http://xmladvice.com*
<http://xmladvice.com/>
>
>
>
> --
> *
> **http://www.jlion.com*
<http://www.jlion.com/>
>
>


-- 

http://www.jlion.com



Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com