Temporary Table Scripting

New version: here

A new job on our servers started failing with an odd message. Essentially, the problems boiled down to an INSERT INTO causing more problems than it was worth.

The temp table in question that was being created had 60+ columns and ever the exceptional (read: lazy) developer I wanted a script to create the table.

SELECT
', [' + COLUMN_NAME + '] ' + DATA_TYPE +
CASE
WHEN DATA_TYPE = 'CHAR' THEN '(' + CAST( CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + ')'
WHEN DATA_TYPE = 'VARCHAR' THEN '(' + CAST( CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + ')'
WHEN DATA_TYPE = 'nvarchar' THEN '(' + CAST( CHARACTER_MAXIMUM_LENGTH AS VARCHAR(MAX) ) + ')'
ELSE ''
END + ' ' +
CASE
WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL'
WHEN IS_NULLABLE = 'YES' THEN 'NULL'
ELSE ''
END
, *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE '#T_TempTableName%'
ORDER BY ORDINAL_POSITION

Some obvious flaws are:

  • data types like numeric, which can contain precision values.
  • doesn’t create the entire create table syntax.
  • the first line of output adds a comma that needs to be manually removed.
  • if more than one temp table of the same name exists (in different spids) you have to adjust the table name temporarily / use the exact name of the temp table in tempdb instead of the like statement.
  • Limitations aside, it’s a quick and dirty fix for the lazy coder.

    This entry was posted in SQL Server Development and tagged , , , . Bookmark the permalink.

    Leave a Reply

    Your email address will not be published. Required fields are marked *