Published on

November 15, 2008

Improving Efficiency with SQL Server and Excel

As a database professional, I often find myself needing to present data from SQL Server tables and views in a clear and organized manner. Over the years, I have developed two utilities that have become 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 on their “business key” values.

Generating Table/View Column Names

Excel is a great tool for formatting data extracted from tables or views. However, for a long time, I didn’t realize that SQL Server Management Studio (SMSS) has an option to include column headers when copying data into an Excel spreadsheet. To address this, I created a stored procedure that generates table/view column names. This procedure is useful if you don’t want to enable the SMSS “column headers” option all the time, and it also demonstrates the power of SQL Server’s schema and metadata.

Starting with SQL Server 2005, Microsoft implemented a set of ANSI-standard views that provide a simple and intuitive look into the schema of your database. One of these views, INFORMATION_SCHEMA.COLUMNS, contains a column-by-column listing of various properties for each column in user-defined tables and views. By querying this view, we can retrieve all the column names in our tables and views, listed in declaration order.

Here is an example query that retrieves the column names for all tables and views:

SELECT
    TABLE_NAME,
    COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION

Using this query, we can build a stored procedure that, given the name of a table or view, returns a single row with the same schema as the specified object, where each column contains its own name. This row can be easily pasted into the first row of an Excel spreadsheet, providing column headers for the data.

Coloring Alternating Sets of Rows in Excel

Once we have the column headers and the data in an Excel sheet, it can be overwhelming to navigate through large amounts of data. To make it easier for readers to group related rows, I have developed an Excel macro that automatically colors alternating sets of rows based on the values in a specified column.

The macro works by collecting the starting row number and the column name from the user. It then computes the “used range” of the active workbook and colors the rows in alternating colors based on the changing values in the specified column.

Here is an example of how to use the macro:

1. Open your spreadsheet.
2. Open "Tools|Macros|Visual Basic Editor".
3. Right-click on "This Workbook" and select "View Code".
4. Copy the entire code provided into the page that opens.
5. Close the "Visual Basic Editor".
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.

The macro will then color the rows in alternating colors based on the changing values in the specified column, making it easier to visually group related rows.

Summary

By utilizing these two utilities, I have significantly improved my efficiency in presenting data from SQL Server tables and views. With just a few simple steps, I can generate a well-formatted Excel table with column headers and color-coded rows. These small improvements have had a huge impact on my productivity.

Please note that the stored procedure requires SQL Server 2005 or higher, while the Excel macro was implemented using Excel 2003 and may not be compatible with other versions.

Author: Dave Ziffer

Contact: You can reach me via the “Contact” link on my company’s website at www.ProjectPro.com. You can also find me on LinkedIn. To stay updated on my upcoming articles and projects, join the LinkedIn “Rapid Application Prototype” group.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.