Wednesday, June 25, 2008

Documenting a SQL Server Database

I recently had a need to document the schema of a SQL Server database. The requirement was to have one worksheet per table and match the formatting done on similar project. The database I actually had to document contains over 300 tables. I worked up some Visual FoxPro code to do this automatically. 
* Document SQL tables into Excel

LOCAL oXl AS Excel.Application
LOCAL oSheet AS EXCEL.Sheets
LOCAL oActiveSheet AS EXCEL.Worksheet


oXl = CREATEOBJECT("Excel.Application")
oXl.Visible = .T.
oWkb = oXl.Workbooks.Add()
oActiveSheet = NULL

lnHandle = SQLSTRINGCONNECT("Driver=SQL Native Client;Server=.;database=Northwind;Trusted_Connection=yes")
SQLEXEC(lnHandle, "Exec sp_tables", "cTables")
SELECT cTables
INDEX ON Table_Type TAG TableType DESCENDING
SCAN FOR Table_Type = "TABLE"
  oSheet = oXl.Sheets.Add()
  loActiveSheet = oXl.ActiveSheet
 
  SQLEXEC(lnHandle, "Exec sp_columns '" + ALLTRIM(cTables.Table_Name)
    +  "'", "cColumns")

  * Add the top two rows with table information after adding the column stuff
  * so that the columns will autosize properly
  XLAddColumn(loActiveSheet, "cColumns")
  loActiveSheet = XLAddTable(loActiveSheet, cTables.Table_Name)
ENDSCAN

SQLDISCONNECT(lnHandle)
oSheet = NULL
oWkb = NULL
oXl = NULL

*********************************

FUNCTION XLAddTable(toActiveSheet, tcTableName)
  LOCAL lcTable
  LOCAL toActiveSheet AS EXCEL.Worksheet
 
  lcTable = ALLTRIM(tcTableName)
  * Handle the sheet title (table name)
  toActiveSheet.Name = lcTable
  toActiveSheet.Range("A1") = ALLTRIM(tcTableName) + " Table"
  toActiveSheet.Range("A1").Font.Name = "Arial"
  toActiveSheet.Range("A1").Font.Size = 14
  toActiveSheet.Range("A1", "F1").Interior.Color = 12632256
  toActiveSheet.Rows(1).RowHeight = 24.75
  toActiveSheet.Range("A1:F1").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A1:F1").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A1:F1").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A1:F1").Borders(10).Color = RGB(0, 0, 0)

  * Column Titles
  toActiveSheet.Range("A2") = "Column Name"
  toActiveSheet.Range("A2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("A2").Borders(10).Color = RGB(0, 0, 0)
 
  * Data type
  toActiveSheet.Range("B2") = "Data Type"
  toActiveSheet.Range("B2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("B2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("B2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("B2").Borders(10).Color = RGB(0, 0, 0)

  * Precision
  toActiveSheet.Range("C2") = "Precision"
  toActiveSheet.Range("C2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("C2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("C2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("C2").Borders(10).Color = RGB(0, 0, 0)

  * Length
  toActiveSheet.Range("D2") = "Length"
  toActiveSheet.Range("D2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("D2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("D2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("D2").Borders(10).Color = RGB(0, 0, 0)

  * Scale
  toActiveSheet.Range("E2") = "Scale"
  toActiveSheet.Range("E2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("E2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("E2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("E2").Borders(10).Color = RGB(0, 0, 0)

  * Comments
  toActiveSheet.Range("F2") = "Comments"
  toActiveSheet.Range("F2").Borders(7).Color = RGB(0, 0, 0)
  toActiveSheet.Range("F2").Borders(8).Color = RGB(0, 0, 0)
  toActiveSheet.Range("F2").Borders(9).Color = RGB(0, 0, 0)
  toActiveSheet.Range("F2").Borders(10).Color = RGB(0, 0, 0)

  * Format the cells
  toActiveSheet.Range("A2", "F2").Font.Bold = .T.
  toActiveSheet.Range("A2:F2").VerticalAlignment = 2
  toActiveSheet.Range("A2", "F2").Interior.Color = 8454143
  toActiveSheet.Rows(2).RowHeight = 26.25

  toActiveSheet.Columns("B").Autofit()
  toActiveSheet.Columns("C").Autofit()
  toActiveSheet.Columns("D").Autofit()
  toActiveSheet.Columns("E").Autofit()
  toActiveSheet.Columns("F").Autofit()
  
  RETURN loActiveSheet
ENDFUNC

*********************************

FUNCTION XLAddColumn(toSheet, tcColCursor)
  LOCAL lnWorkArea
  LOCAL toSheet AS EXCEL.Worksheet
 
  lnWorkArea = SELECT()
  tcCell = ""

  SELECT (tcColCursor)
  SCAN
    * Column name
    tcCell = "A" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = ALLTRIM(Column_Name)
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)

    * Data Type
    tcCell = "B" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = ALLTRIM(Type_Name)
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
  
    * Precision
    tcCell = "C" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = Precision
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
  
    * Length
    tcCell = "D" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = Length
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
  
    * Scale
    tcCell = "E" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell) = Scale
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)

    * Comments
    tcCell = "F" + ALLTRIM(STR(RECNO() + 2))
    toSheet.Range(tcCell).Borders(7).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(8).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(9).Color = RGB(0, 0, 0)
    toSheet.Range(tcCell).Borders(10).Color = RGB(0, 0, 0)
  ENDSCAN

  toSheet.Columns("A").Autofit()
 
  SELECT (lnWorkArea)
ENDFUNC

Labels: ,


// posted by Craig Berntson @ 4:06:00 PM
//  4 Comments // Links to this post

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]