Dumping stored procedures using boo. – Jay R. Wren

A while ago David Cumps posted about Extracting stored procedure content via SQL.

I liked this post and I knew I wanted to do something with it. We want to keep a copy of all of our stored procedures in Subversion, our version control system. This little boo script connects a database server, iterates through all of the stored procedures in all of the databases and outputs them to a directory named after the database.


import System
import System.Data from "System.Data"
import System.Data.SqlClient from "System.Data"
import System.IO


def Main(argv as (string)):

    Exit = System.Environment.Exit
    mkdir = System.IO.Directory.CreateDirectory

    if(null!=argv and argv.Length>0):
        databasehost = argv[0]
    else:
        databasehost = "MyDefaultDbServer"

    ignoreDatabases= ["master", 'model', 'msdb', 'Northwind', 'pubs', 'tempdb']
    databases=[]

    connectionString = string.Format("server={0};database=master;Integrated Security=True",databasehost)
    print "using connection string:"+connectionString
    conn = SqlConnection(connectionString)
    cmd = conn.CreateCommand()
    cmd.CommandText="SELECT name FROM master.dbo.sysdatabases ORDER BY name"
    conn.Open()
    reader = cmd.ExecuteReader()
    while reader.Read() :
        if ( not ignoreDatabases.Contains( reader.GetString(0) )):
            databases.Add(reader.GetString(0))
    reader.Close()

    print databases

    for database in databases:
        spNames=[]
        mkdir(database)
        cmd = conn.CreateCommand()
        cmd.CommandText = "use ${database};SELECT name FROM sysobjects WHERE type = 'P' AND category = 0 ORDER BY name"
        reader = cmd.ExecuteReader()
        while reader.Read() :
            spNames.Add(reader.GetString(0))
        reader.Close()

        print "${database}: ${spNames}"
        for spName in spNames:
            cmd = conn.CreateCommand()
            cmd.CommandText = "use ${database}; SELECT text FROM syscomments WHERE id=(SELECT id FROM sysobjects WHERE name='${spName}') ORDER BY colid"
            reader = cmd.ExecuteReader()
            outputf = File.CreateText( Path.Combine(database, "${spName}.sql") )
            while reader.Read():
                outputf.WriteLine(reader.GetString(0))
            reader.Close()
            outputf.Close()
    conn.Close()