[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.
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
[Prev in Thread] |
Current Thread |
[Next in Thread] |
- script to handle version control of sql server databases,
Matthew Herrmann <=