info-cvs
[Top][All Lists]
Advanced

[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.

Regards,

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

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
file
Function CleanFile(inFilename, outFilename)

    Dim sText
        Set Stream = FSO.OpenTextFile(inFilename)

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

    Stream.Close

    ' 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)
    next

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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''
' Main Program
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''

Dim Args
Set Args = WScript.Arguments

' Make sure we have command arguments
If Args.Count <> 5 Then
    Msgbox "Usage: GenerateSQL.py 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
Stream.WriteLine "SET QUOTED_IDENTIFIER  ON    SET ANSI_NULLS  ON" & 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
Next

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

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

' 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
        Next
        End If
Next

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

' 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
Next

' 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 
SQLDMOScript_DRI_Checks)
    End If
Next

' 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
Next

' 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
Next

Stream.Close

' Now clean up
cleanFile paramOutFile, paramOutFile




reply via email to

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