automatically save a copy of a procedure

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

Transact-SQL (2008)

automatically save a copy of a procedure

Author

Topic

CoolMoon2011
Starting Member

31 Posts

Posted - 2012-01-16 : 16:26:36

I want to write codes to automatically save a copy of a procedure to a text files as a backup using BCP function?
(Not the output data of the Stored Procedure, but the Stored Procedure itself.)

Please help! Thank you

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-16 : 17:18:09

Why when you'll have the prior versions in your source control system? (you do use source control, don't you?)

DDL trigger for ALTER PROCEDURE.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-16 : 17:44:39

PRINT @SQL

Water Closet

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-17 : 19:03:50

exec sp_helptext 'MyProcedureName'

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

Kristen
Test

22859 Posts

Posted - 2012-01-18 : 03:49:53

quote:
Originally posted by Bustaz Kool

exec sp_helptext 'MyProcedureName'

Although that will "break" overly long line

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-01-18 : 06:58:13

howzabout


select 
	ROUTINE_DEFINITION 
from 
	INFORMATION_SCHEMA.ROUTINES 
where 
	ROUTINE_NAME = 'MyStoredProcName'

Does that break with overly long procs?

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-18 : 07:06:54

Yes, and it cuts off at 8000 characters (or is it 4000)?

--
Gail Shaw
SQL Server MVP

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-18 : 11:35:32

quote:

Although that will "break" overly long line

[CODE]select Definition
from sys.sql_modules
where object_id = Object_ID('MyProcedure')[/CODE]Aha! The quoter has become the quotee!!!

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

Kristen
Test

22859 Posts

Posted - 2012-01-18 : 13:03:19

quote:
Originally posted by Bustaz Kool

quote:

Although that will "break" overly long line
[CODE]select Definition
from sys.sql_modules
where object_id = Object_ID('MyProcedure')[/CODE]Aha! The quoter has become the quotee!!!

Although have to do that form an APP, or some sort of SQL tool - SSMS has an 8,000 character limit on a single column

My preference is to use sp_HelpText because the line-break is in a predictable position, and just scrolling down the code its easy enough to see lines that are long enough to have been split and remove the extra line break.

If your code is narrow then no problem, if your code is very wide then you probably ought to bust it up onto multiple lines anyway. Simples!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 13:11:22

Has anyone evaluated change management and control products specifically for SQL Server? Something such as "DB Ghost" or others?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-18 : 13:36:50

http://www.red-gate.com/products/sql-development/sql-source-control/

--
Gail Shaw
SQL Server MVP

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 13:38:07