HawkeSoft SQL Tips and Tricks - Part 3 - Getting Excel Column Headers

It has been a while since we have posted SQL Server Tips and Tricks so today is a 2 for 1 special!

Sometimes database administrators and data architects need to paste table result sets into Microsoft Excel. This is easy to do but sometimes the column list is so long that re-typing it can be tedious. In SQL Server 2000 copying from the results window in Enterprise Manager would retain the column headers. For some reason this functionality appears to be removed from SQL Server 2005 SSMS.

The code below will generate the column header row for easy pasting into Excel, Word, etc... For best results run the script in grid output mode and make sure you have a large buffer for the output window (SQL Server 2005).

DECLARE @TableName varchar(255)
SET @TableName = 'TableName'

DECLARE @TableID int
SELECT @TableID = object_id
FROM sys.tables
WHERE [name] = @TableName

IF(@TableID IS NULL)
BEGIN
PRINT 'Table [' + @TableName + '] does not exists in database ' + DB_NAME() + '.'
RETURN
END

DECLARE @SQL varchar(max),
@MaxColumnID int
SET @SQL = 'SELECT '
-- Find the max column ID for the fencepost problem
SELECT @MaxColumnID = MAX(column_id)
FROM sys.columns
WHERE object_id = @TableID

SELECT @SQL =
CASE WHEN column_id <> @MaxColumnID
THEN @SQL + '''' + [name] + ''' AS [Column' + CAST(column_id AS varchar(4)) + '], '
ELSE @SQL + '''' + [name] + ''' AS [Column' + CAST(column_id AS varchar(4)) + ']'
END
FROM sys.columns
WHERE object_id = @TableID
ORDER BY column_id ASC

EXEC(@SQL)