I am often asked to produce presentations containing data from database tables and views. Over the years I have developed two utilities that are indispensable time-savers:
- a SQL-Server stored procedure that generates table/view column names, and
- an Excel macro that colors alternating sets of rows based upon their "business key" values.
Emit a row full of column names
Excel is a great tool for formatting data that you've extracted from tables or views. You can copy such data into an Excel spreadsheet straight from SQL Management Studio's output grid. For years I didn't realize that there's an option to make SQL Management Studio also grab the column headers (see "Include Column Headers from SMSS"), so I created a stored procedure that would do this. The procedure remains useful if you don't want to have the SMSS "column headers" option turned on all the time, and it is also an interesting example of what you can do with SQL Server's schema - describing metadata.
Starting with SQL Server 2005, Microsoft implemented a set of ANSI-standard views that can help solve problems like this one. Using SQL Management Studio you can see them in every database under the "System Views" folder:
These views provide a simple, intuitive look into the schema of your database. In particular, INFORMATION_SCHEMA.COLUMNS provides a column-by-column listing of many interesting properties of each column in every user-defined table and view. Using just three of the columns in this view, you can query all of the column names in all of your tables and views, with the column names listed in declaration order:
select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME, ORDINAL_POSITION
We can use this query to build a stored procedure which, given the name of a table or view, returns a single row having the same schema as the passed-in table or view, in which each column contains its own name. The procedure uses a cursor that steps through the rows of the output of the above query, constructing a command by tacking on one column name with each iteration:
if object_id('ColumnHeaders', 'P') is not null drop procedure ColumnHeaders go create procedure ColumnHeaders @TableName varchar(128) as -- test to ensure the argument is the name of a real table if @TableName not in (select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS) begin declare @ErrorMessage varchar(max) set @ErrorMessage = 'There is no table or view named ''' + @TableName + '''.' raiserror(@ErrorMessage, 16, 1) end else begin -- set up cursor declare #c cursor local for select COLUMN_NAME + '=''' + COLUMN_NAME + ''',' ColumnAssignment from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName order by ORDINAL_POSITION open #c -- construct an execution string declare @ExecString varchar(max) set @ExecString = 'select ' declare @ColumnAssignment varchar(100) fetch next from #c into @ColumnAssignment while @@fetch_status = 0 begin set @ExecString = @ExecString + @ColumnAssignment fetch next from #c into @ColumnAssignment end set @ExecString = left(@ExecString, len(@ExecString)-1) -- tear down cursor close #c deallocate #c -- execute execution string exec(@ExecString) end go
We invoke this procedure by passing it the name of a table or view:
exec ColumnHeaders 'TBgeoSTATEPROVINCE' -- supply your own table name as an argument
The output looks like this:
It consists of a single row having the same column names as the columns in the specified table, with each column containing its own name. This row can be pasted into the first row of an Excel spreadsheet. Subsequently you can query contents of the table or view itself, producing rows having the same schema, and append that query's output to the column-header row in your Excel sheet.
For example it takes only a few seconds to copy and paste the outputs of these two statements:
-- supply your own table name as an argument exec ColumnHeaders 'TBgeoSTATEPROVINCE' -- query some subset of your own table or view select * from TBgeoSTATEPROVINCE where StateProvinceName like 'A%'
to produce a spreadsheet that looks like this:
StateProvinceId StateProvinceName StateProvinceCode CountryId Notes CreatedById CreationDate AuditDate AuditUserId AuditStatus 1 Alabama AL 222 NULL 1 9/5/2008 13:50 9/5/08 13:50 1 I 2 Alaska AK 222 NULL 1 9/5/2008 13:50 9/5/08 13:50 1 I 3 Arizona AZ 222 NULL 1 9/5/2008 13:50 9/5/08 13:50 1 I 4 Arkansas AR 222 NULL 1 9/5/2008 13:50 9/5/08 13:50 1 I
Color alternate sets of rows in a spreadsheet
Once you've gotten your column headers and query output into an Excel sheet, the sheer amount of data is often mind-numbing. Your data will generally contain groups of rows having the same business key. I like to paint such sets of rows in alternating colors so that readers can easily group the related rows. Coloring groups of rows by hand can be very tedious and error-prone, so here is some code that does it automatically.
First we have a comment. I strongly recommend keeping this at the top of your copy of this code because it tells everyone exactly how to incorporate the code into an Excel workbook:
' Automatically color alternating sets of rows of the current sheet, based on the data in one of the columns. ' This macro automatically figures out how wide and tall your data is. ' Instructions: ' 1. Open your spreadsheet. ' 2. Open "Tools|Macros|Visual Basic Editor" ' 3. Right click on "This Workbook" and select "View Code", which should open a page on the right. ' 4. Copy the entire contents of this file into that page. ' 5. Close the "Visual Basic Editor" by killing the whole interface that came up when you did step 3. ' 6. The macro will now operate on any page of your workbook: ' * copy your data into an empty sheet ' * select "Tools|Macros" ' * select "ColorAlternateDataRows" from the list ' * enter the requested row and column numbers ' Option Explicit
Next is a function that returns the numeric index of a column, given its alphabetic name. This allows us to convert a user-specified column name into an index, so that we can invoke Excel library functions that take column indices as arguments:
' Return the 1-based index of a column, given its name. ' We presume fewer than 2**31 - 1 columns. Private Function ColumnIndex(ColumnName As String) As Integer Dim ReturnValue As Integer ReturnValue = 0 Dim CharIndex As Integer CharIndex = 1 Do While CharIndex <= Len(ColumnName) Dim ThisChar As String ThisChar = Mid(ColumnName, CharIndex, 1) If ThisChar >= "A" And ThisChar <= "Z" Then ReturnValue = ReturnValue * 26 + (Asc(ThisChar) - Asc("A") + 1) ElseIf ThisChar >= "a" And ThisChar <= "z" Then ReturnValue = ReturnValue * 26 + (Asc(ThisChar) - Asc("a") + 1) Else ReturnValue = -1 GoTo Ret End If CharIndex = CharIndex + 1 Loop Ret: ColumnIndex = ReturnValue End Function
The following function tries to collect a positive integer from the user, cycling until it gets one or until the user cancels. This is used to collect the starting row number for the coloring (the user may have header rows that should not be colored):
' Retrieve a positive (long) integer from the user. Private Function GetPositiveInteger(Text As String, Optional AllowColumnNames As Boolean = False) As Long Dim Data As Long Data = -1 Dim FirstIteration As Boolean FirstIteration = True Do While Data < 0 Dim S As String If FirstIteration Then S = InputBox(Text) Else If AllowColumnNames Then S = InputBox(Text & " We need a positive integer.") Else S = InputBox(Text & " We need a column name or a positive integer.") End If End If If Trim(S) = "" Then GoTo Ret ' cancel ElseIf IsNumeric(S) Then ' See if it's an integer directly. If CInt(S) > 0 Then Data = CInt(S) End If ElseIf AllowColumnNames Then ' See if it's a column name. Dim ColNdx As Integer ColNdx = ColumnIndex(S) If ColNdx > 0 Then Data = ColNdx End If End If FirstIteration = False Loop Ret: GetPositiveInteger = Data End Function
This function is the actual macro that the user invokes. It does the following:
- collects the desired starting row from the user
- collects the column name of the (single) column whose changing value will cause a change in row color
- computes the total "used range" (i.e. span of cells that are occupied) of the currently active workbook
- colors the rows in alternating colors
' Color alternate data rows based upon business-key information provided ' interactively by the user. Sub ColorAlternateDataRows() Dim FirstDataRow As Long Dim TestDataInColumn As Integer Dim S As Worksheet Set S = ThisWorkbook.ActiveSheet If Not (S Is Nothing) Then FirstDataRow = GetPositiveInteger("Enter the NUMBER of the FIRST ROW containing data to be colored.") If FirstDataRow <= 0 Then Exit Sub TestDataInColumn = GetPositiveInteger("Enter the COLUMN NAME of the column containing the changing data.", True) If TestDataInColumn <= 0 Then Exit Sub Dim CurrentData As String CurrentData = "" Dim Width As Integer Width = S.UsedRange.Columns.Count Const ColorIndex1 As Integer = 36 ' light yellow Const ColorIndex2 As Integer = 34 ' light blue Dim ColorIndex As Integer ColorIndex = ColorIndex2 Dim RowIndex As Long Dim LastRow As Long LastRow = S.UsedRange.Rows.Count For RowIndex = FirstDataRow To LastRow ' change the color if necessary If CurrentData <> CStr(S.Cells(RowIndex, TestDataInColumn)) Then If ColorIndex = ColorIndex1 Then ColorIndex = ColorIndex2 Else ColorIndex = ColorIndex1 End If CurrentData = CStr(S.Cells(RowIndex, TestDataInColumn)) End If ' apply the color S.Range(S.Cells(RowIndex, 1), S.Cells(RowIndex, Width)).Select With Selection.Interior .ColorIndex = ColorIndex .Pattern = xlSolid End With Next End If End Sub
You can copy and paste all the above code into a single file that can be incorporated into any desired Excel workbook.
Now let's see what this looks like when we run it. We'll start with a trivial Excel sheet containing some data that came from a SQL Server table or view (header row colored by hand):
Number Letter 1 A 2 A 3 B 4 B 5 C
We execute the macro and it pops up this dialog. We've already colored the header row by hand, so to avoid having the macro re-color it we tell the macro to start coloring with row 2:
Up pops another dialog. Here we tell the macro that it should change row color every time the value in column A changes.
After hitting OK the macro produces this:
Number Letter 1 A 2 A 3 B 4 B 5 C
We can now rerun the macro, telling it to change colors on changes in column B instead, and we get this:
Number Letter 1 A 2 A 3 B 4 B 5 C
Note that the macro assumes only a single-column key, which I have found to be sufficient for the vast majority of my uses. The macro can of course be modified to accept a range of columns instead of just one. I leave this as an exercise for the reader.
Using these two macros I have quickly become the favored documenting person of almost every team in which I've worked. In a matter of seconds I can generate a well-colored Excel table that has column headers. It may seem like a small thing, but for me it's had a huge effect.
The stored procedure requires that you be running SQL Server 2005 or higher. The Excel macro was implemented using Excel 2003; it has not been tested in other Excel versions.
You can contact me (Dave Ziffer) via the "Contact" link on my company's web site at www.ProjectPro.com. I'm also on LinkedIn. To keep abreast of my upcoming articles and projects, join the LinkedIn "Rapid Application Prototype" group.