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
Friday, June 13, 2008
VS2008 & .Net 3.5 SP1 Beta
This morning I installed the .Net Framework 3.5 Service Pack 1 Beta and the Visual Studio 2008 Service Pack 1 Beta. The Framework beta required a reboot after install, but I expected it would. The VS beta required my original VS media CD. I've said before that an SP should never require the original CD. The second install took quite a bit longer then the first. Scott Guthrie blogged details on what all the goodies in the Service Packs and some warnings you should look at before install. As always, this is beta software and should not be installed on a production machine. Microsoft is targeting a late summer release of both SPs.
Thursday, June 12, 2008
Speaking tonight Utah .Net User Group
I'm speaking tonight at the Utah .NET User Group. The presentation will part of the Microsoft Community Launch and will be geared to "What's New in C# 3.0" and "LINQ". I had some earlier requests to cover round tripping WPF between Visual Studio 2008 and Expression Blend and What's New in VB 9.0. If there is still interest tonight on those topics, we'll breifly cover them.
NOTE THE LOCATION CHANGE FOR TONIGHT
The meeting starts at 6:00 at the Microsoft offices, 123 Wright Brothers Drive, Salt Lake City. This is in the International Center, just west of the airport.
NOTE THE LOCATION CHANGE FOR TONIGHT
The meeting starts at 6:00 at the Microsoft offices, 123 Wright Brothers Drive, Salt Lake City. This is in the International Center, just west of the airport.
Monday, June 02, 2008
Desert Code Camp Followup
Last Saturday I was in Tempe for Desert Code Camp. It was a great experience to be there and talk to developers from the Phoenix area. As promised, here is the sample code from my LINQ session. The Continuous Integration whitepaper can be found here. Thanks to Lorin and everyone there for making me feel welcome!
Subscribe to Posts [Atom]