[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

script to handle version control of sql server databases

From: Matthew Herrmann
Subject: script to handle version control of sql server databases
Date: Mon, 4 Feb 2002 08:53:26 +1100

Hi All,

I've been working on an SQL Server database as part of a project, and I
created the following script to allow me to do all my development in SQL
Server, but still get cvs text-based version control without 20 mouse clicks
to generate scripts from sql server. It was originally in Python, but it's
COM support broke unexpectedly going from SQL7 to 2000 and I couldn't be
bothered working out why it happened, so I translated it to VBS.

I'm also using a script which generates batch files to read/write bcp text
files (but this is less pluggable). I saw some posts about this a while ago
and thought this would be of use to SQL Server developers. The version
control tracking is excellent from this, for example, you change a field
name, and when you regenerate the scripts, you'll just get something like :

<        first_date  datetime
>        initial_date  datetime

Please email me if you'd like additional help about doing this, we got it
working here really smoothly.


Matthew Herrmann
Far Edge Technology
Level 11, 80 Mount St
North Sydney NSW 2060

Ph: 02 9955 3640
Mob: 0404 852 537

' GenerateSQL.vbs
' (C)Copyright 2002 Matthew Herrmann, Far Edge Pty. Ltd.
' email: address@hidden
' Far Edge Pty. Ltd.
' A script to automate getting an entire database in scripted form from
' Microsoft SQL Server. Does more than the scripts that SQL Server generates
' in that it creates the DROP DATABASE, CREATE DATABASE commands. You should
' then have a script which populates your empty database with test data.
' Known problems : Doesn't script permissions for tables, this isn't here
' yet because you should never need it (!)
' Doesn't do user-defined types etc. since I don't use them.
' Doesn't script data values as of yet -- this would be very cool.

Const SQLDMOScript_DRI_Defaults = &H2000000
Const SQLDMOScript_Permissions = 34
Const SQLDMOScript_DRI_Checks =  &H1000000
Const SQLDMOScript_DRI_ForeignKeys = &H8000000
const SQLDMOScript_PrimaryObject = 4
const SQLDMOScript_NoDRI = &H200
Const SQLDMOScript_DRI_PrimaryKey = &H10000000
Const SQLDMOScript_DRI_UniqueKeys = &H4000000
Const SQLDMOScript_DRIIndexes = &H10000

Set FSO = CreateObject("Scripting.FileSystemObject")

' Utility Functions

' Removes extraneous whitespace and SET QUOTED... lines for the given SQL
Function CleanFile(inFilename, outFilename)

    Dim sText
        Set Stream = FSO.OpenTextFile(inFilename)

        sText = ""
    Do While Not Stream.AtEndOfStream
                sText = sText & Stream.ReadLine & vbCrLf


    ' Clean up garbage
    for i = 0 to 4
        sText = replace(sText, "SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS
ON " & vbcrlf & _
                                "GO" & vbcrlf & vbcrlf,"")
        sText = replace(sText, "SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS
ON " & vbcrlf & "GO" & _
                                vbcrlf & vbcrlf,"")
        sText = replace(sText, vbcrlf & vbcrlf & vbcrlf,vbcrlf & vbcrlf)

    Set Stream = FSO.CreateTextFile(outFilename)
    Stream.WriteLine sText
End Function

' Main Program

Dim Args
Set Args = WScript.Arguments

' Make sure we have command arguments
If Args.Count <> 5 Then
    Msgbox "Usage: outfile.sql server databasename username
password" & _
                vbcrlf & "To specify an empty password, use 'NONE'."
    Msgbox 1/0 ' To exit?
End If

' Get parameters
paramOutFile = Args(0)
paramServer = Args(1)
paramDatabase = Args(2)
paramUsername = Args(3)
paramPassword = Args(4)
if paramPassword = "NONE" Then
    paramPassword = ""
End If

Set Server = CreateObject("SQLDMO.SQLServer")
Server.Connect paramServer, paramUsername, paramPassword

Set DB = Server.Databases(paramDatabase)

Set Stream = FSO.CreateTextFile(paramOutFile)

' Write header
Stream.WriteLine "DROP DATABASE " & paramDatabase & vbCrLf & "GO"
Stream.WriteLine "CREATE DATABASE " & paramDatabase & vbCrLf & "GO"
Stream.WriteLine "USE " & paramDatabase & vbCrLf &  "GO" & vbCrLf & vbCrLf
& "GO" & vbCrLf

' Write logins
For Each Login In Server.Logins:
    If Login.GetUserName(paramDatabase) <> "" and Login.Name <> "sa" Then
        Stream.WriteLine login.Script
    End If

' Write users
For Each User In db.Users
    If user.Name <> "dbo" Then
        Stream.WriteLine user.Script
    End If

' Write roles
For Each Role In db.DatabaseRoles
    If Not Role.IsFixedRole Then
        Stream.WriteLine role.Script
    End If

' Write assignations
' No scripting facility to do this, so we have to do it by hand :(
For Each User in db.Users
    If user.Name <> "dbo" Then
        Set Members = user.ListMembers
        For i = 1 To members.Count
                        If members.Item(i) <> "public" Then
                                Stream.WriteLine "exec sp_addrolemember N'" & 
members.Item(i) & "', N'"
& _
                                        user.Name & "'"
                                Stream.WriteLine "GO" & vbCrLf
                        End If
        End If

' Write tables
For Each Table In db.Tables
    If Not table.SystemObject Then
        Stream.WriteLine table.Script(SQLDMOScript_PrimaryObject Or
    End If

' Write keys
For Each Table In db.Tables
    if not table.SystemObject Then
        flags = SQLDMOScript_DRI_PrimaryKey or SQLDMOScript_DRI_UniqueKeys
or _
                                SQLDMOScript_DRIIndexes '(sic)
        Stream.WriteLine table.Script(flags)
    End If

' Write constraints
For Each Table In db.Tables
    if not table.SystemObject Then
        Stream.WriteLine table.Script(SQLDMOScript_DRI_ForeignKeys or _
                                SQLDMOScript_DRI_Defaults or 
    End If

' Write views
For Each View in db.Views
    If not view.SystemObject Then
        Stream.WriteLine view.Script
        Stream.WriteLine view.Script(SQLDMOScript_Permissions)
    End If

' Write stored procedures
For Each proc In db.StoredProcedures
    If not proc.SystemObject Then
        Stream.WriteLine proc.Script
        Stream.WriteLine proc.Script(SQLDMOScript_Permissions)
    End If


' Now clean up
cleanFile paramOutFile, paramOutFile

reply via email to

[Prev in Thread] Current Thread [Next in Thread]