SQL Server Portal

Temporary table is one of the frequently used objects in SQL Server and whenever we need to place data temporarily somewhere, we prefer to use the temporary table. Recently, I was working on temporary table  in SQL Server 2012 and wanted to view the metadata of the temporary table using sp_describe_first_result_set (a new system stored procedure shipped with SQL Server 2012) . Ooopps….. there’s an error, let me explain the error in detail. This error message is available in SQL Server 2012 and above.

Message Number: 11525

Severity : 16

Error Message: The metadata could not be determined because statement ‘%.*ls’ uses a temp table..

Error Generation:
Let me create a temporary table to demonstrate this error.

USE tempdb
GO
CREATE TABLE #temp_Sample
(
[ID] INT,
[NAME] VARCHAR(32)
)
GO
SELECT * FROM #temp_Sample
GO
--OUTPUT

You can see that I have created a temporary table and browsed it as well. Now I would like to view the metadata of the above temporary table. Given below is the script to view the metadata using sp_describe_first_result_set

USE tempdb
GO
Sp_describe_first_result_set @tsql =N'Select * from #temp_Sample'
GO
--OUTPUT

Msg 11525, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement ‘Select * from #temp_Sample’ uses a temp table.

Ooopps…… I am unable to view it.

Resolution:
The reason behind this error is, you cannot generate the metadata of temporary table. So what to do ?Very simple guys, just convert temporary table to global temporary table and view the metadata.
Given below is the correct script.

-- Convert the temporary table to global temporary table.
SELECT * into ##temp_Sample FROM #temp_Sample

-- View the metadeta
Sp_describe_first_result_set @tsql =N'Select * from ##temp_Sample'
GO
--OUTPUT

Conclusion :
Remember, whenever you use temporary table and would like to view the metadata of that query using sp_describe_first_result_set,  always convert temporary table to global temporary table.