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


exec [tempdb]..sp_help [table]

 

Sample


exec [tempdb]..sp_help [#cache]

Output

 

Generate Table Create Statement

dbo.itvf_getTempTableCreateStatement

Procedure


use [master]
go

/*

    drop function [dbo].[itvf_getTempTableCreateStatement]

*/
if object_id('[dbo].[itvf_getTempTableCreateStatement]') is null
begin

    exec
    ('
        CREATE FUNCTION [dbo].[itvf_getTempTableCreateStatement]
        (	
        )
        RETURNS TABLE 
        AS
        RETURN 
        (
            -- Add the SELECT statement with parameter references here
            SELECT [shell] = 1/0
        )
    ')

end
go

ALTER FUNCTION [dbo].[itvf_getTempTableCreateStatement]
(
    @object sysname	
)
RETURNS TABLE 
AS
RETURN 
(

    with cteColumnTypesWithColumnLength
    (
        [type]
    )
    as
    (
        select 'char'
        union all
        select 'nchar'
        union all
        select 'varchar'
        union all
        select 'nvarchar'

    )
    select 
              [columnID]
                = tblSC.[column_id]

            , [column]
                 = tblSC.[name]

            , [maxLength]
                 = tblSC.[max_length]

            , [userType] 
                = tblST.[name]

            , [isNullable]
                 = tblSC.[is_nullable]

            , [sqlClause] =

                case ( tblSC.[column_id] )
                    when 1 then '  '
                    else ', '
                end
                                
                +  quoteName(tblSC.[name])

                + ' '

                + tblST.[name]

                + case
                    when (
                            tblST.[name] in
                                (
                                    select [type] 
                                    from  cteColumnTypesWithColumnLength
                                )	 
                        )
                        then 
                            '('
                            + cast(tblSC.[max_length] as varchar(4))
                            + ')'

                    else ' '

                    end
                                
                + ' '

                + case tblSC.[is_nullable]

                    when 1 then ' null '
                    when 0 then ' not null '
                    else ' '

                  end

            , [colList] =

                case ( tblSC.[column_id] )
                    when 1 then '  '
                    else ', '
                end
                                
                +  quoteName(tblSC.[name])

    
    from   [tempdb].sys.objects tblSO

    inner join [tempdb].sys.columns tblSC

        on tblSO.object_id = tblSC.object_id

    inner join [tempdb].sys.types tblST

        on tblSC.[user_type_id] = tblST.[user_type_id]

    where (

            ( 
                tblSO.object_id 
                    = object_id
                        (
                            'tempdb..' + @object
                        ) 
            )

        )

)

go

Sample


use [tempdb]
go

set xact_abort on
go

set nocount on
go

declare @table sysname

set @table = '#cache'

if object_id('tempdb..#cache') is not null
begin

	drop table #cache

end

select top 10 *
into   #cache
from   sys.messages tblM

--exec [tempdb]..sp_help #cache

select *
from   [master].[dbo].[itvf_getTempTableCreateStatement]
		(
			@table
		)

if object_id('tempdb..#cache') is not null
begin

	drop table #cache

end
go


Output

 

Summary

Both sp_help and object_id work equally well with temp objects.

In the case of sp_help, please make sure that you reference ( temdpb) as in tempd..sp_help.

And, in the case of object_id please make sure that you are cross-referencing the system tables in tempdb.

And, that you use the full object’s name, tempdb..[objectname], when you attempt to get the object_id.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s