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.

    Requires Free Membership to View

    Login

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 first published in October 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top