SQL Server – Temp Table Structure

Background Created a temporary table on the fly. But, before wrapping things up, I wanted to review the temp table's structure.   SQL sp_help Syntax   Sample Output   Generate Table Create Statement dbo.itvf_getTempTableCreateStatement Procedure Sample Output   Summary Both sp_help and object_id work equally well with temp objects. In the case of sp_help, please … Continue reading SQL Server – Temp Table Structure

Transact SQL – Drop Temp Table if it it exists

Background Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL. Code Original Code SQL Explanation When the temp table does not exist, an error is raised. Because the drop table is enclosed in a try/catch block the … Continue reading Transact SQL – Drop Temp Table if it it exists

Transact SQL – Retrieving the structure of Temp Tables

Background Out of laziness, I sometimes create temporary tables on the fly. Sample Select/Into   Metadata Let us get metadata info on our temp table columns Outline Checked the code for sp_help using sp_helptext 'sp_help' and used that stolen code here. Code Output Script Temp Tables Stored Procedure script.sp_TableTempStructure Code Invocation   Output

Transact SQL – Create Object with different Structure based on runtime decisions

Background Based on the version of SQL Server being targeted one might need to create a slightly different table structure. Here is the original code that fails. And, slight variations. Implementation Original Code Output Revised ( Add Go Statement ) Premise: We add a go to ensure object is in fact created. Code Revised ( … Continue reading Transact SQL – Create Object with different Structure based on runtime decisions