Problem solve Get help with specific problems with your technologies, process and projects.

Exporting T-SQL scripts from command line

I would like to export a bunch of T-SQL scripts to a file (each) in a command line procedure, NOT with the GUI. would like also to know when the scripts have been updated, and I thought I could do it with a trigger but these are not allowed on system tables.

I presume when you say scripts you are referring to stored procedures. If you don't want to script objects using the GUI the best way is to do this using the SCRIPT method of SQL-DMO. The technical reference is here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_0n1g.asp.

An example of this would be:

Dim server
Dim sp

SET server=CreateObject("SQLDMO.SQLServer")

server.Connect "."

For Each sp in server.Databases("Northwind").StoredProcedures

 If Not sp.SystemObject then

  sp.script 68,"c:\" & sp.name & "_script.sql"
 End If

To determine if the SP has been changed since last time you scripted it will be a little more difficult. You will need to check and retain the values of two columns from sysobjects table. The crdate column will tell you the last time the stored procedure was dropped and recreated. The schema_ver column will change every time the stored procedure is ALTER'd. You will need to track both for each stored procedure.

For More Information

Dig Deeper on SQL-Transact SQL (T-SQL)

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.