Q
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.LoginSecure=True

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

Next
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

This was last published in October 2003

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close