December 5, 2008 at 10:15 am
How do you programmatically export stored procedures to text files in SQL Server 2005?
This seems like a simple task, but I haven't found any decent solutions. So far, the only solutions I've found are:
1. Use the GUI Export Wizard
Not programmatic.
2. Query routine_definition from information_Schema.routines.
Only gives you the first 4000 characters.
3. Use sqlcmd to execute sp_helptext
Gives you everything, but returns the lines as a "query" by breaking up long lines into 256 lines characters. You can't reliably stitch them together because there's no 100% accurate way to tell if a 256 character line is truncated or is supposed to be 256 characters.
December 5, 2008 at 10:37 am
If you want to program the extraction, say using C#, get the SMO (SQL Server Management Objects) loaded into your code and you can generate create procedures for any object programatically.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 5, 2008 at 11:01 am
Suggest using SSMS - change query output to "File" and then invoking sys.sql_modules. Although not completely programmatic. Sys.sql_modules could be invoked via C#, VB, dot net routines and then print from within that code. By the way in sys.sql_modules the definition (i.e.., the T-SQL text is a nvarchar(Max) column)
December 5, 2008 at 3:13 pm
There was an article in SQL Server Magazine last month detailing cmd line scripting utility you can find the article here, you have to be a subscriber http://www.sqlmag.com/Article/ArticleID/100140/sql_server_100140.html but the utility is available at http://www.valinor.co.il/tools-sqlscripter.asp
Andrew
December 5, 2008 at 3:48 pm
Powershell/SMO/DMO have various scripting methods.
Also http://www.sqlservercentral.com/articles/Administering/2896/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply