' - Require that all variables be declared
Option Explicit
dim strSQL, aryWriteTableData, conDBCurrentTableRead
dim objColumn, intRecreateTableName, strConnectionString
dim objColumnValue, objColumnActualSize, intNumRecords
dim strCreateString, strInsertString, conDBCurrentTableWrite
dim intQueryStatus, strDBName, aryStoredProcedures
dim intStoredProcedure, strText, objProperty
dim objPropertyName, objPropertyValue, strProperties
dim objPropertyType, objPropertyProperty, strPropertyType
dim aryWriteTableDefinitions, intWriteTableDefinition, aryNonIdentityColumns
dim strNonIdentityColumns, intNonIdentityColumn, strPrimaryKeyColumn
dim intRowsAffected
dim intRecordNumber, intDatabaseType, objWriteFile, strWriteFilename
dim strFilenamePrefix
set conDBCurrentTableRead=CreateObject("adodb.recordset")
strConnectionString="Provider=SQLOLEDB;uid=websitedbuser;pwd=websitedbuser01;server=desktop;database=websitedb;"
if wscript.arguments.length=0 then
wscript.echo "Format: cscript <scriptname> FilenamePrefix"
wscript.quit
end if
strFilenamePrefix = wscript.arguments.item(0)
' - define tables to read
aryWriteTableData=array("USStates")
' - define tables to define only
aryWriteTableDefinitions=array()
' - define stored procedures to read
aryStoredProcedures=array("CheckPermissions")
for intDatabaseType=0 to 1 ' - 0=SQL Server, 1=MySQL
'intDatabaseType=0
strWriteFilename=strFilenamePrefix& "."
if intDatabaseType=0 then
strWriteFilename=strWriteFilename & "sqlserver.tables"
elseif intDatabaseType=1 then
strWriteFilename=strWriteFilename & "mysql.tables"
end if
strWriteFilename=strWriteFilename & ".sql"
wscript.echo "Writing """ & strWriteFilename & """"
set objWriteFile=createobject("scripting.filesystemobject").createtextfile(strWriteFilename)
if intDatabaseType=0 then
objWriteFile.write "-- Generated SQL Server table code " & now() & ";" & vbcrlf
elseif intDatabaseType=1 then
objWriteFile.write "-- `Generated MySQL table code " & year(now()) & "-" & right("0"&month(now()), 2) & "-" & right("0"&day(now()), 2) & " " & right("0"&hour(now()), 2) & ":" & right("0"&minute(now()), 2) & ":" & right("0"&second(now()), 2) & "`;" & vbcrlf
else
wscript.echo "-- Unknown database type " & intDatabaseType & ";"
wscript.quit
end if
objWriteFile.write "" & vbcrlf
' - drop tables, create tables, then insert data
for intRecreateTableName=0 to ubound(aryWriteTableData)
aryNonIdentityColumns=array()
objWriteFile.write "-- Delete table """ & aryWriteTableData(intRecreateTableName) & """" & ";" & vbcrlf
if intDatabaseType=0 then
objWriteFile.write "if exists (select * from dbo.sysobjects where id = object_id(N'[" & aryWriteTableData(intRecreateTableName) & "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)" & vbcrlf
objWriteFile.write "drop table [" & aryWriteTableData(intRecreateTableName) & "];" & vbcrlf
objWriteFile.write "go" & vbcrlf
elseif intDatabaseType=1 then
objWriteFile.write "drop table if exists `" & aryWriteTableData(intRecreateTableName) & "`;" & vbcrlf
end if
objWriteFile.write "" & vbcrlf
' - retrieve the primary key column name for this table, if there is one
strPrimaryKeyColumn=""
strSQL="select index_col(so.name, indid, 1) as PrimaryKeyColumn from sysindexes si "
strSQL=strSQL& "join sysobjects so on si.id=so.id "
strSQL=strSQL& "join information_schema.tables I on object_id(I.table_name)=si.id "
strSQL=strSQL& "where "
strSQL=strSQL& "I.table_name='" & aryWriteTableData(intRecreateTableName) & "' and "
strSQL=strSQL& "indid > 0 and indid < 255 "
strSQL=strSQL& "and so.type = 'U' "
strSQL=strSQL& "and (si.status & 64) = 0 "
strSQL=strSQL& "and (si.status & 8388608) = 0 "
strSQL=strSQL& "and (si.status & 16777216)= 0 "
' wscript.echo strSQL
' wscript.quit
conDBCurrentTableRead.open strSQL, strConnectionString
if not(conDBCurrentTableRead.bof) and not(conDBCurrentTableRead.eof) then
strPrimaryKeyColumn=conDBCurrentTableRead("PrimaryKeyColumn")
' wscript.echo strPrimaryKeyColumn
' wscript.quit
end if
conDBCurrentTableRead.close
objWriteFile.write "-- Create table """ & aryWriteTableData(intRecreateTableName) & """" & ";" & vbcrlf
strSQL="select "
strSQL=strSQL& "I.table_catalog as DatabaseName, "
strSQL=strSQL& "I.table_name as TableName, "
strSQL=strSQL& "SC.name as ColumnName, "
strSQL=strSQL& "SC.xusertype as DataType, "
strSQL=strSQL& "SC.isnullable as AllowNulls, "
strSQL=strSQL& "SC.length as MaxLength, "
strSQL=strSQL& "ident_seed(I.table_name)*SC.Colstat as IdentitySeed, "
strSQL=strSQL& "ident_incr(I.table_name)*SC.Colstat as IdentityIncrement, "
strSQL=strSQL& "*, "
strSQL=strSQL& "object_id(I.table_name) as [ObjectID] "
strSQL=strSQL& "from information_schema.tables I "
strSQL=strSQL& "join syscolumns SC on object_id(table_name)=SC.id "
strSQL=strSQL& "join sysindexes SI on SC.id=SI.id "
strSQL=strSQL& "and SI.indid<2 "
strSQL=strSQL& "and I.table_name='" & aryWriteTableData(intRecreateTableName) & "' "
strSQL=strSQL& "order by "
' strSQL=strSQL& "SI.indid*SC.colstat desc, ident_seed(I.table_name)*SC.Colstat desc, lower(SC.name) "
strSQL=strSQL& "SC.colorder "
' wscript.echo strSQL
' wscript.quit
conDBCurrentTableRead.open strSQL, strConnectionString
if intDatabaseType=0 then
strCreateString="create table [" & aryWriteTableData(intRecreateTableName) & "] (" & vbcrlf
elseif intDatabaseType=1 then
strCreateString="create table `" & aryWriteTableData(intRecreateTableName) & "` (" & vbcrlf
end if
while not(conDBCurrentTableRead.bof) and not(conDBCurrentTableRead.eof)
if intDatabaseType=0 then
strCreateString=strCreateString& "[" & conDBCurrentTableRead("ColumnName") & "] "
elseif intDatabaseType=1 then
strCreateString=strCreateString& "`" & conDBCurrentTableRead("ColumnName") & "` "
end if
if conDBCurrentTableRead("DataType")=35 then
if intDatabaseType=0 then
strCreateString=strCreateString& "text"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "blob /* SQL Server text=MySQL blob, but MySQL text (65535 chars) might be enough */"
end if
elseif conDBCurrentTableRead("DataType")=36 then
if intDatabaseType=0 then
strCreateString=strCreateString& "uniqueidentifier"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "varchar(250) /* SQL Server uniqueidentifier=MySQL varchar(250) roughly */"
end if
elseif conDBCurrentTableRead("DataType")=48 then
if intDatabaseType=0 then
strCreateString=strCreateString& "tinyint"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "tinyint"
end if
elseif conDBCurrentTableRead("DataType")=56 then
if intDatabaseType=0 then
strCreateString=strCreateString& "int"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "int"
end if
elseif conDBCurrentTableRead("DataType")=59 then
if intDatabaseType=0 then
strCreateString=strCreateString& "real"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "double /* SQL Server double=MySQL real */"
end if
elseif conDBCurrentTableRead("DataType")=60 then
if intDatabaseType=0 then
strCreateString=strCreateString& "money"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "float /* SQL Server money=MySQL float */"
end if
elseif conDBCurrentTableRead("DataType")=61 then
if intDatabaseType=0 then
strCreateString=strCreateString& "datetime"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "datetime"
end if
elseif conDBCurrentTableRead("DataType")=62 then
if intDatabaseType=0 then
strCreateString=strCreateString& "float"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "float"
end if
elseif conDBCurrentTableRead("DataType")=99 then
if intDatabaseType=0 then
strCreateString=strCreateString& "ntext"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "blob /* SQL Server ntext=MySQL blob, but MySQL text (65535 chars) might be enough */"
end if
elseif conDBCurrentTableRead("DataType")=104 then
if intDatabaseType=0 then
strCreateString=strCreateString& "bit"
elseif intDatabaseType=1 then
' - note: I don't think MySQL processes the constraint correctly
if conDBCurrentTableRead("AllowNulls")=false or conDBCurrentTableRead("AllowNulls")=0 then
' strCreateString=strCreateString& "int check (`" & conDBCurrentTableRead("ColumnName") & "` in (0, 1)) /* SQL Server bit (nulls not allowed)=MySQL int with constraints */"
strCreateString=strCreateString& "enum('0','1') /* SQL Server bit (nulls not allowed)=MySQL enum('0','1') not nulls */"
else
' strCreateString=strCreateString& "int check (`" & conDBCurrentTableRead("ColumnName") & "` in (null, 0, 1)) /* SQL Server bit (nulls allowed)=MySQL int with constraints */"
' strCreateString=strCreateString& "enum(null, '0','1') /* SQL Server bit (nulls allowed)=MySQL enum(null, '0','1') */"
strCreateString=strCreateString& "enum('0','1') /* SQL Server bit (nulls allowed)=MySQL enum('0','1')*/"
end if
end if
elseif conDBCurrentTableRead("DataType")=165 then
if intDatabaseType=0 then
strCreateString=strCreateString& "varbinary"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "bigint /* SQL Server varbinary=MySQL bigint (I think ...) */"
end if
elseif conDBCurrentTableRead("DataType")=167 then
if intDatabaseType=0 then
strCreateString=strCreateString& "varchar(" & conDBCurrentTableRead("MaxLength") & ")"
elseif intDatabaseType=1 then
if conDBCurrentTableRead("MaxLength")>255 then
strCreateString=strCreateString& "text /* SQL Server varchar(" & conDBCurrentTableRead("MaxLength") & "), length>255=MySQL text */"
else
strCreateString=strCreateString& "varchar(" & conDBCurrentTableRead("MaxLength") & ")"
end if
end if
elseif conDBCurrentTableRead("DataType")=175 then
if intDatabaseType=0 then
strCreateString=strCreateString& "char(" & conDBCurrentTableRead("MaxLength") & ")"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "char(" & conDBCurrentTableRead("MaxLength") & ")"
end if
elseif conDBCurrentTableRead("DataType")=231 then
if intDatabaseType=0 then
strCreateString=strCreateString& "nvarchar(" & conDBCurrentTableRead("MaxLength")/conDBCurrentTableRead("TypeStat") & ")"
elseif intDatabaseType=1 then
strCreateString=strCreateString& "varchar(" & conDBCurrentTableRead("MaxLength")/conDBCurrentTableRead("TypeStat") & ") /* SQL Server nvarchar(length)=MySQL varchar(length) */"
end if
else
wscript.echo aryWriteTableData(intRecreateTableName) & "." & conDBCurrentTableRead("ColumnName")
wscript.echo "has an unknown datatype value of " & conDBCurrentTableRead("DataType")
wscript.quit
end if
strCreateString=strCreateString& " "
' if conDBCurrentTableRead("PrimaryKey")=true or conDBCurrentTableRead("PrimaryKey")>0 then
' strCreateString=strCreateString& "primary key "
' end if
if lcase(conDBCurrentTableRead("ColumnName"))=lcase(strPrimaryKeyColumn) then
if intDatabaseType=0 then
strCreateString=strCreateString& "primary key "
elseif intDatabaseType=1 then
strCreateString=strCreateString& "primary key "
end if
end if
if ("0"&conDBCurrentTableRead("IdentitySeed"))>0 then
if intDatabaseType=0 then
strCreateString=strCreateString& "identity(" & conDBCurrentTableRead("IdentitySeed") & ", " & conDBCurrentTableRead("IdentityIncrement") & ") "
elseif intDatabaseType=1 then
if ("0"&conDBCurrentTableRead("IdentitySeed"))=1 and ("0"&conDBCurrentTableRead("IdentityIncrement"))=1 then
strCreateString=strCreateString& "auto_increment primary key "
else
wscript.echo "Unable to convert "
wscript.echo aryWriteTableData(intRecreateTableName) & "." & conDBCurrentTableRead("ColumnName")
wscript.echo "from identity(" & conDBCurrentTableRead("IdentitySeed") & ", " & conDBCurrentTableRead("IdentityIncrement") & ")"
wscript.echo "to MySQL auto_increment because both the IdentitySeed and IdentityIncrement must be exactly 1."
wscript.echo "IdentitySeed=" & conDBCurrentTableRead("IdentitySeed")
wscript.echo "IdentityIncrement=" & conDBCurrentTableRead("IdentityIncrement")
wscript.quit
end if
end if
else
aryNonIdentityColumns=split(join(aryNonIdentityColumns, "\\")&"\\"&conDBCurrentTableRead("ColumnName"), "\\")
end if
if conDBCurrentTableRead("AllowNulls")=false or conDBCurrentTableRead("AllowNulls")=0 then
if intDatabaseType=0 then
strCreateString=strCreateString& "not null "
elseif intDatabaseType=1 then
strCreateString=strCreateString& "not null "
' if conDBCurrentTableRead("DataType")<>104 then ' - don't add an additional constraint if it's already constrained as a bit field
' strCreateString=strCreateString& "not null "
' end if
end if
end if
strCreateString=strCreateString& ", " & vbcrlf
strCreateString=replace(strCreateString, " , " & vbcrlf, ", " & vbcrlf)
conDBCurrentTableRead.movenext
wend
' wscript.echo strCreateString
if instr(strCreateString, ",")>0 then
strCreateString=left(strCreateString, instrrev(strCreateString, ",")-1)
end if
strCreateString=strCreateString& ");" & vbcrlf
if intDatabaseType=0 then
strCreateString=strCreateString& "go"
end if
objWriteFile.write strCreateString & vbcrlf
conDBCurrentTableRead.close
' wscript.quit
strSQL="select count(*) as NumRecords from " & aryWriteTableData(intRecreateTableName)
' wscript.echo strSQL
conDBCurrentTableRead.open strSQL, strConnectionString
intNumRecords=conDBCurrentTableRead("NumRecords")
conDBCurrentTableRead.close
objWriteFile.write "" & vbcrlf
if intNumRecords>0 then
if intNumRecords=1 then
objWriteFile.write "-- Insert 1 record into """ & aryWriteTableData(intRecreateTableName) & """" & ";" & vbcrlf
else
objWriteFile.write "-- Insert " & intNumRecords & " records into """ & aryWriteTableData(intRecreateTableName) & """" & ";" & vbcrlf
end if
strSQL="select "
for intNonIdentityColumn=1 to ubound(aryNonIdentityColumns)
' wscript.echo aryNonIdentityColumns(intNonIdentityColumn)
strSQL=strSQL& "[" & aryNonIdentityColumns(intNonIdentityColumn) & "]"
if intNonIdentityColumn<ubound(aryNonIdentityColumns) then
strSQL=strSQL& ", "
end if
next
strSQL=strSQL& " from " & aryWriteTableData(intRecreateTableName)
' wscript.echo strSQL
conDBCurrentTableRead.open strSQL, strConnectionString
intRecordNumber=0
while not(conDBCurrentTableRead.bof) and not(conDBCurrentTableRead.eof)
intRecordNumber=intRecordNumber+1
objWriteFile.write "-- """ & aryWriteTableData(intRecreateTableName) & """ record " & intRecordNumber & " of " & intNumRecords & ";" & vbcrlf
if intDatabaseType=0 then
strInsertString="insert into [" & aryWriteTableData(intRecreateTableName) & "] ( " & vbcrlf
elseif intDatabaseType=1 then
strInsertString="insert into `" & aryWriteTableData(intRecreateTableName) & "` ( " & vbcrlf
end if
for each objColumn in conDBCurrentTableRead.fields
if intDatabaseType=0 then
strInsertString=strInsertString& "[" & objColumn.name & "], " & vbcrlf
elseif intDatabaseType=1 then
strInsertString=strInsertString& "`" & objColumn.name & "`, " & vbcrlf
end if
next
strInsertString=left(strInsertString, instrrev(strInsertString, ",")-1)
strInsertString=strInsertString& vbcrlf
strInsertString=strInsertString& ") values (" & vbcrlf
for each objColumn in conDBCurrentTableRead.fields
objColumnValue=objColumn.value
if not(isnull(objColumnValue)) then
objColumnValue=replace(objColumnValue, "'", "''")
objColumnValue=replace(objColumnValue, chr(7), "")
end if
' wscript.echo "got here.1"
' wscript.echo objColumn.type
' strInsertString=strInsertString& " "
if objColumn.type=3 or objColumn.type=4 or objColumn.type=5 or objColumn.type=6 or objColumn.type=131 then
' - handle numbers
if isnull(objColumnValue) then
strInsertString=strInsertString& "null"
else
strInsertString=strInsertString& objColumnValue
end if
' - handle boolean
elseif objColumn.type=11 then
' wscript.echo objColumn.name & ": " & objColumnValue
if isnull(objColumnValue) then
strInsertString=strInsertString& "null"
elseif objColumnValue="True" or objColumnValue=true then
if intDatabaseType=0 then
strInsertString=strInsertString& "1"
elseif intDatabaseType=1 then
strInsertString=strInsertString& "'1'"
end if
elseif objColumnValue="False" or objColumnValue=false then
if intDatabaseType=0 then
strInsertString=strInsertString& "0"
elseif intDatabaseType=1 then
strInsertString=strInsertString& "'0'"
end if
else
wscript.echo objColumnValue
wscript.quit
end if
' - handle datetime
elseif objColumn.type=135 then
if isnull(objColumnValue) then
strInsertString=strInsertString& "null"
else
if intDatabaseType=0 then
strInsertString=strInsertString& "'" & objColumnValue & "'"
elseif intDatabaseType=1 then
strInsertString=strInsertString& "'" & year(objColumnValue) & "-" & month(objColumnValue) & "-" & day(objColumnValue) & " " & hour(objColumnValue) & ":" & minute(objColumnValue) & ":" & second(objColumnValue) & "'"
end if
end if
' - handle strings
elseif objColumn.type=200 or objColumn.type=201 or objColumn.type=202 or objColumn.type=203 then
if isnull(objColumnValue) then
strInsertString=strInsertString& "null"
else
strInsertString=strInsertString& "'" & objColumnValue & "'"
end if
else
wscript.echo aryWriteTableData(intRecreateTableName) & "." & objColumn.name
wscript.echo "has an unknown column type of " & objColumn.type
wscript.echo "Data: " & objColumnValue
wscript.quit
end if
strInsertString=strInsertString& ", " & vbcrlf
next
strInsertString=left(strInsertString, instrrev(strInsertString, ",")-1)
strInsertString=strInsertString& ");" & vbcrlf
if intDatabaseType=0 then
strInsertString=strInsertString& "go"
end if
objWriteFile.write strInsertString & vbcrlf
' wscript.quit
objWriteFile.write "" & vbcrlf
conDBCurrentTableRead.movenext
wend
objWriteFile.write "" & vbcrlf
conDBCurrentTableRead.close
end if
next
objWriteFile.close
set objWriteFile=nothing
if intDatabaseType=0 then
strWriteFilename=strFilenamePrefix & ".sqlserver.storedprocedures.sql"
