Vbscript SQL select table generator for automated testing

Question

I would like to create a VBScript that read a SQL server database and generate a SQL simple query for each table of one shema of the database and store this SQL into a separate file on disk.

Example :

table A :

ID
field1
field2
field3

table B :

ID
field4
field5

Would generate 2 SQL files :

File 1 : tableA.SQL

SELECT ID, field1, field2, field3 FROM table A ORDER BY ID

File 2 : tableB.SQL

SELECT ID, field4, field5 FROM table B ORDER BY ID

Purpose of this request: to have an automated testing suite running with all these queries on two copy of the database to find difference on structure and/or data, using NUnit + ORAYLIS BI.Quality http://biquality.codeplex.com/

Ekkehard.Horner Ekkehard.Horner · Accepted Answer · 2014-02-13T13:08:53

Given

the decision to use ADOX instead of .OpenSchema
a valid connection to your database (oConn)
a valid path to the folder to store the .sql files in (sDDir)
a global FileSystemObject (goFS)

this

Sub genTS(oConn, sDDir)
  Dim oCatalog : Set oCatalog   = CreateObject( "ADOX.Catalog" )
  Set oCatalog.ActiveConnection = oConn
  Dim oTable
  For Each oTable In oCatalog.Tables
      If "TABLE" = oTable.Type Then
         WScript.Echo oTable.Name
         ReDim aColumns(oTable.Columns.Count - 1)
         Dim i : i = 0
         Dim oColumn
         For Each oColumn In oTable.Columns
             WScript.Echo " ", oColumn.Name
             aColumns(i) = oColumn.Name
             i           = i + 1
         Next
         Dim sSQL : sSQL = Join(Array( _
               "SELECT" _
             , "[" & Join(aColumns, "], [") & "]" _
             , "FROM [" & oTable.Name & "]" _
             , "ORDER BY [" & aColumns(0) & "]" _
         ), " ")
         WScript.Echo " ", sSQL
         goFS.CreateTextFile(goFS.BuildPath(sDDir, oTable.Name & ".sql")).WriteLine sSQL
      End If
  Next
End Sub

should work in principle. Output:

Alpha
  Id
  StartDate
  EndDate
  Value
  SELECT [Id], [StartDate], [EndDate], [Value] FROM [Alpha] ORDER BY [Id]
...

type ..\data\21751835\Alpha.sql
SELECT [Id], [StartDate], [EndDate], [Value] FROM [Alpha] ORDER BY [Id]

You may have to tinker with the way to specify the order column.

Update:

Maybe the decision (1) was wrong. There may be a way to get a schema name from an ADOX table object, but I can't find it at the moment. So let's use .OpenSchema:

Sub genTS(oConn, sDDir)
  Const adSchemaTables  = 20
  Const adSchemaColumns =  4
  Dim rsTables : Set rsTables = oConn.OpenSchema(adSchemaTables, Array(Empty, <YourSchemaName>, Empty, "TABLE"))
  Do Until rsTables.EOF
     Dim sTName : sTName = rsTables.Fields("TABLE_NAME").Value
     WScript.Echo sTName, rsTables.Fields("TABLE_SCHEMA").Value
     Dim rsColumns : Set rsColumns = oConn.OpenSchema(adSchemaColumns, Array(Empty, Empty, sTName))
     ReDim aColumns(-1)
     Do Until rsColumns.EOF
        Dim sFName : sFName = rsColumns.Fields("COLUMN_NAME").Value
        WScript.Echo " ", sFName
        ReDim Preserve aColumns(UBound(aColumns) + 1)
        aColumns(UBound(aColumns)) = sFName
        rsColumns.MoveNext
     Loop
     Dim sSQL : sSQL = Join(Array( _
           "SELECT" _
         , "[" & Join(aColumns, "], [") & "]" _
         , "FROM [" & sTName & "]" _
         , "ORDER BY [" & aColumns(0) & "]" _
     ), " ")
     WScript.Echo " ", sSQL
     goFS.CreateTextFile(goFS.BuildPath(sDDir, sTName & ".sql")).WriteLine sSQL
     rsColumns.Close
     rsTables.MoveNext
  Loop
  rsTables.Close
End Sub

Vbscript SQL select table generator for automated testing

1 Answers