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
// posted by Craig Berntson @ 4:06:00 PM 
Comments:
Links to this post:
<< Home
Hi Craig,
Great routine!
Just thought you should know that it needs a small tweak (what routine doesn't!).
* Handle the sheet title (table name)
toActiveSheet.Name = LEFT(lcTable, 31) && or your favorite name shortener UDF
Unlike SQL Server, Excel sheet names as limited to 31 characters. I discovered this when I ran your routine against the AdventureWorks Database in SQL Server 2005.
Great routine!
Just thought you should know that it needs a small tweak (what routine doesn't!).
* Handle the sheet title (table name)
toActiveSheet.Name = LEFT(lcTable, 31) && or your favorite name shortener UDF
Unlike SQL Server, Excel sheet names as limited to 31 characters. I discovered this when I ran your routine against the AdventureWorks Database in SQL Server 2005.
# posted by 
: 12:07 AM
: 12:07 AM
Could have been done with less effort and less code with SQL Server Integration Services...
KG
KG
# posted by : 12:02 AM
: 12:02 AM
KG, there are a couple of issues with that. First, I don't know IS and don't know if I would have had time to install and learn enough about it to do the task. I was on an extremely short time line. Second, there was additional, proprietary information in my original task that I pulled out of the code I posted. Not sure if IS could handle that.
Hi Craig,
Ditto to Rick Bean's comments. I've also made a few modifications. The main one was to include if the table column allows nulls. I also wrapped it in a try..catch and left the Excel object invisible until it finishes. Not as much fun to watch but certainly finishes faster.
Thanks for doing the heavy lifting...
Richard Kaye
Post a Comment
Ditto to Rick Bean's comments. I've also made a few modifications. The main one was to include if the table column allows nulls. I also wrapped it in a try..catch and left the Excel object invisible until it finishes. Not as much fun to watch but certainly finishes faster.
Thanks for doing the heavy lifting...
Richard Kaye
Links to this post:
<< Home
Subscribe to Posts [Atom]