Update Script

[Movian]

Hey,
Due to a number of mishaps i shan't go into we are in the following situation.

We have a current up to date database on our development server. We have a number of clients with outdated databases based on previous iterations of our current DB with mostly the same table structure but with different fields and options and a few tables missing. No update scripts were generated during development and as mentioned the clients are all on different versions,

Is there a way to produce an update script based on our Current DB that can be used to update ALL our clients DBs on mass through an automated update system. Or will we have to compare each DB individually and make the alterations ourselves.... (a few hundred DB's)

I know it should have been done differently, however hindsight is 20/20 and i need a solution for the situation as it stands.... any help is appreciated.

We (and our clients) are running MS SQL express 2005.

[Movian]

Well had a little more time to research this, this morning....

Guess there are no tools currently for this. So i will write a program that will script each table and column with a if not exists and if it doesn't then it can be created.... should be easy enough to have something do that as it just needs to out put the result as a .txt file....

Downside is i wouldn't be able to drop any columns no longer needed. Still looking for input so if you have any concerns about my fix concept or have suggestions please let me know ^_^

[Movian]

Ok got my basic VBA code done to create the script. now onto testing...

If anyone is curious here is the sub i created. Creates a 0.99 MB .sql file with 24,715 Lines in roughly 2-3 seconds.

As mentioned this will only create tables & fields that don't exist, it will not drop tables/fields no longer in use.

[vba]Private Sub btnOutput_Click()
'Movian 5/9/2011
'Movian AT live DOT com
'Free for all to use as long as this comment section is left in the code un altered.

Dim myrs As New ADODB.Recordset, fldList As New ADODB.Recordset
Dim filenum As Integer, fldTextList As String

filenum = FreeFile()

myrs.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME ASC", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Open CurrentProject.Path & "\Update Script " & Format(Date, "MM-DD-YYYY") & ".sql" For Output As filenum
While Not myrs.EOF
fldList.Open "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '" & myrs("TABLE_NAME") & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
fldTextList = ""
While Not fldList.EOF
fldTextList = fldTextList & "[" & fldList("COLUMN_NAME") & "] [" & fldList("DATA_TYPE") & "], "
fldList.MoveNext
Wend

Print #filenum, "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" & myrs("TABLE_NAME") & "]') AND type in (N'U'))"
Print #filenum, ""
Print #filenum, " BEGIN"
Print #filenum, " CREATE TABLE [" & myrs("TABLE_NAME") & "](" & fldTextList & ")"
Print #filenum, " END"
Print #filenum, ""
Print #filenum, " ELSE"
Print #filenum, ""
Print #filenum, " BEGIN"

fldList.MoveFirst
While Not fldList.EOF
Print #filenum, " if not Exists(select * from sys.columns where Name = N'[" & fldList("COLUMN_NAME") & "]' and Object_ID = Object_ID(N'[" & myrs("TABLE_NAME") & "]'))"
Print #filenum, " BEGIN"
Print #filenum, " ALTER TABLE [" & myrs("TABLE_NAME") & "] ADD [" & fldList("COLUMN_NAME") & "] [" & fldList("DATA_TYPE") & "]"
Print #filenum, " END"
Print #filenum, ""
fldList.MoveNext
Wend

Print #filenum, " END"

fldList.Close
Set fldList = Nothing
myrs.MoveNext
Wend

Close #filenum
MsgBox "Complete", vbInformation, "Process Complete"
End Sub[/vba]

[Movian]

Made Some tweaks and improvements to the code, If anyone can think of a way to tell it to drop any tables/fields not present then im all ears ^_^

[VBA]Dim myrs As New ADODB.Recordset, fldList As New ADODB.Recordset
Dim filenum As Integer, fldTextList As String

filenum = FreeFile()

myrs.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME ASC", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Open CurrentProject.Path & "\Update Script " & Format(Date, "MM-DD-YYYY") & ".sql" For Output As filenum
While Not myrs.EOF
fldList.Open "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '" & myrs("TABLE_NAME") & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
fldTextList = ""
While Not fldList.EOF
fldTextList = fldTextList & "[" & fldList("COLUMN_NAME") & "] [" & fldList("DATA_TYPE") & "], "
fldList.MoveNext
Wend

Print #filenum, "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" & myrs("TABLE_NAME") & "]') AND type in (N'U'))"
Print #filenum, ""
Print #filenum, " BEGIN"
Print #filenum, " CREATE TABLE [" & myrs("TABLE_NAME") & "](" & fldTextList & ")"
Print #filenum, " END"
Print #filenum, ""
Print #filenum, " ELSE"
Print #filenum, ""
Print #filenum, " BEGIN"

fldList.MoveFirst

'
'
While Not fldList.EOF
Print #filenum, " IF col_length('" & myrs("TABLE_NAME") & "','" & fldList("COLUMN_NAME") & "') is null"
Print #filenum, " BEGIN"
Print #filenum, " ALTER TABLE [" & myrs("TABLE_NAME") & "] ADD [" & fldList("COLUMN_NAME") & "] [" & fldList("DATA_TYPE") & "]"
Print #filenum, " END"
Print #filenum, ""
fldList.MoveNext
Wend

Print #filenum, " END"

fldList.Close
Set fldList = Nothing
myrs.MoveNext
Wend

Close #filenum
MsgBox "Complete", vbInformation, "Process Complete"[/VBA]

[Movian]

Sorry to keep adding posts but i can't edit my original one for some reason.

I made some more changes, now the system will insert the default value when creating the table or adding fields. If anyone can assist with detecting Primary keys and adding them automatically to the table creation section that would be appreciated as this looks to be a little more complex.

[vba]Public Sub GenerateSQLUpdateScript()
'Richard Burgess - 2011 - Movian at live.com
'
'This script is free to use for both free and comercial applications as long as this initial header is kept in place and un altered.
'
'To do - structure system to detect primary keys and create primary keys correctly.
'Setup system to remove tables - fields not in sample structure.

Dim myrs As New ADODB.Recordset, fldList As New ADODB.Recordset
Dim filenum As Integer, fldTextList As String, tmp As String

filenum = FreeFile()

myrs.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME ASC", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Open CurrentProject.Path & "\" & InputBox("Version Number?", Version) & "-tables.sql" For Output As filenum
While Not myrs.EOF
fldList.Open "SELECT column_name, data_type, CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT FROM information_schema.columns WHERE table_name = '" & myrs("TABLE_NAME") & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
fldTextList = ""
While Not fldList.EOF

fldTextList = fldTextList & "[" & fldList("COLUMN_NAME") & "] " & fldList("DATA_TYPE")
If fldList("Data_Type") = "nvarchar" Then
If Not IsNull(fldList("CHARACTER_MAXIMUM_LENGTH")) Then
Select Case fldList("CHARACTER_MAXIMUM_LENGTH")
Case Is > 255
fldTextList = fldTextList & "(MAX)"
Case Is < 1
fldTextList = fldTextList & "(MAX)"
Case Else
fldTextList = fldTextList & "(" & fldList("CHARACTER_MAXIMUM_LENGTH") & ")"
End Select
End If
End If
If Not IsNull(fldList("COLUMN_DEFAULT")) And Not fldList("COLUMN_DEFAULT") = "" Then
fldTextList = fldTextList & " default " & fldList("COLUMN_DEFAULT")
End If
fldTextList = fldTextList & ", "
fldList.MoveNext
Wend

Print #filenum, "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" & myrs("TABLE_NAME") & "]') AND type in (N'U'))"
Print #filenum, ""
Print #filenum, " BEGIN"
Print #filenum, " CREATE TABLE [" & myrs("TABLE_NAME") & "](" & fldTextList & ")"
Print #filenum, " END"
Print #filenum, ""
Print #filenum, " ELSE"
Print #filenum, ""
Print #filenum, " BEGIN"

fldList.MoveFirst

'
'
While Not fldList.EOF
Print #filenum, " IF col_length('" & myrs("TABLE_NAME") & "','" & fldList("COLUMN_NAME") & "') is null"
Print #filenum, " BEGIN"
tmp = "ALTER TABLE [" & myrs("TABLE_NAME") & "] ADD [" & fldList("COLUMN_NAME") & "] "
If fldList("Data_Type") = "nvarchar" Then
If Not IsNull(fldList("CHARACTER_MAXIMUM_LENGTH")) Then
Select Case fldList("CHARACTER_MAXIMUM_LENGTH")
Case Is > 255
tmp = tmp & fldList("DATA_TYPE") & "(MAX)"
Case Is < 1
tmp = tmp & fldList("DATA_TYPE") & "(MAX)"
Case Else
tmp = tmp & fldList("DATA_TYPE") & "(" & fldList("CHARACTER_MAXIMUM_LENGTH") & ")"
End Select
Else
tmp = tmp & fldList("DATA_TYPE")
End If
Else
tmp = tmp & fldList("DATA_TYPE")
End If

If Not IsNull(fldList("COLUMN_DEFAULT")) And Not fldList("COLUMN_DEFAULT") = "" Then
tmp = tmp & " default " & fldList("COLUMN_DEFAULT")
End If

Print #filenum, tmp
Print #filenum, " END"
Print #filenum, ""
fldList.MoveNext
Wend

Print #filenum, " END"

fldList.Close
Set fldList = Nothing
myrs.MoveNext
Wend

On Error Resume Next
myrs.Close
Set myrs = Nothing
On Error GoTo 0

Close #filenum
MsgBox "Complete", vbInformation, "Process Complete"
End Sub[/vba]