Transact SQL – Retrieving the structure of Temp Tables

Background

Out of laziness, I sometimes create temporary tables on the fly.

Sample

Select/Into


/*
	Create Temp Table (Scoped to Session )
*/
select 
		  [dbid] = tblSD.database_id
		, [dbname] = tblSD.[name]
		, [createDate] = tblSD.[create_date]
		, [defaultLanguage]=tblSD.default_language_name
		, [databaseOwner] = suser_sname(tblSD.owner_sid)
into #listofDatabase
from   sys.databases tblSD


 

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


set nocount on
set XACT_ABORT on
go

/*
	Declare Variables
*/
declare @objectNameSession sysname
declare @objectIDSession int

declare @objectNameGlobal sysname
declare @objectIDGlobal int

declare	@dbname	sysname
declare @no varchar(35)
declare @yes varchar(35)
declare @none varchar(35)
declare @precscaletypes nvarchar(150)

/*
	Init Variables
*/
set @precscaletypes = N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,'
select @no = 'no', @yes = 'yes', @none = 'none'

set @objectNameSession = '#listofDatabase'
set @objectNameGlobal = '##listofDatabaseGlobal'

/*
	Clean up, if previous Temp Objects Exist
*/
if object_id('tempdb..#listofDatabase') is not null
begin

	print 'Drop table #listofDatabase'
	drop table #listofDatabase

end


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

	print 'Drop table ##listofDatabaseGlobal'
	drop table tempdb..##listofDatabaseGlobal

end


/*
	Create Temp Table (Scoped to Session )
*/
select 
		  [dbid] = tblSD.database_id
		, [dbname] = tblSD.[name]
		, [createDate] = tblSD.[create_date]
		, [defaultLanguage]=tblSD.default_language_name
		, [databaseOwner] = suser_sname(tblSD.owner_sid)
into #listofDatabase
from   sys.databases tblSD


/*
	Create Temp Table (Scoped Globally )
*/
select *
into   ##listofDatabaseGlobal
from   #listofDatabase


select @objectIDSession = ( select top 10 tblSO.id from tempdb..sysobjects tblSO where type = 'U' and name like @objectNameSession + '%' )
print '@objectIDSession ' + cast(isNull(@objectIDSession, -1) as varchar(60))

select @objectIDGlobal = ( select top 1 tblSO.id from tempdb..sysobjects tblSO where type = 'U' and name = @objectNameGlobal )
print '@objectIDGlobal ' + cast(isNull(@objectIDGlobal, -1) as varchar(60))

--exec tempdb..sp_help ##listofDatabaseGlobal
--- INFO FOR EACH COLUMN
print ' '

select
	  [tempTableScope]
		= case
			when (tblAO.name like '##%') then 'Global'
			else 'Session'
		  end
	, [objectID] = tblAO.[object_id]
	, [schema] = tblSS.[name]	
	, [objectName] = tblAO.name
	, [objectCreateDate] = tblAO.create_date

	, [columnName] = tblAC.name

	, [Type] = type_name(user_type_id)

	, [Computed] 
			= case 
				when ColumnProperty(tblAC.object_id, tblAC.name, 'IsComputed') = 0 then @no 
				else @yes 
			  end

	, [Length] 	 
			= convert(int, max_length)

	-- for prec/scale, only show for those types that have valid precision/scale
	-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
	, [Prec]	= case 
						when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
							then convert(char(5),ColumnProperty(tblAC.object_id, tblAC.name, 'precision'))
						else '     ' 
				  end

	, [Scale]  
		=  case 
				when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
					then convert(char(5),OdbcScale(system_type_id,scale))
				else '     ' 
		   end

	, [Nullable] 
		= case 
			when is_nullable = 0 then @no 
			else @yes 
		  end

	, [TrimTrailingBlanks]	
		= case ColumnProperty(tblAC.object_id, tblAC.name, 'UsesAnsiTrim')
				when 1 then @no
				when 0 then @yes
				else '(n/a)' 
		  end

	, [FixedLenNullInSource] 
			= case
				when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
					then '(n/a)'
				when is_nullable = 0 then @no else @yes 
			  end

	, [collationName] = collation_name

from [tempdb].sys.all_columns  tblAC

inner join [tempdb].sys.objects tblAO

	on tblAC.object_id = tblAO.[object_id]

inner join [tempdb].sys.schemas tblSS

	on tblAO.schema_id = tblSS.[schema_id]

where  tblAO.[type] = 'U'

and    tblAO.object_id in 
			( 
				   @objectIDSession
				 , @objectIDGlobal 
			)

order by
		tblAO.[create_date] desc



Output


Script Temp Tables

Stored Procedure

script.sp_TableTempStructure

Code



use [master]
go

if schema_id('script') is null
begin

	exec('create schema [script] authorization [dbo] ');

end
go


if object_id('[script].[sp_TableTempStructure]') is null
begin

	exec('create procedure [script].[sp_TableTempStructure] as print ''shell'' ');

end
go

alter procedure [script].[sp_TableTempStructure] 
(
	  @objectName sysname
	, @debug	bit =0
)
as
begin

	set nocount on;
	set XACT_ABORT on;
	set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

	declare @objectID int
	declare @tempTableType tinyint

	declare @tblTable TABLE
	(

		  [id]					 smallint not null identity(1,1)
		, [columnID]			 smallint not null
		, [columnName]			 sysname  not null

		, [columnTypeID]		 smallint not null
		, [columnType]			 sysname  not null
		
		, [computedAsVarchar]    varchar(10) not null
		, [computedAsBit] as
		       case ( [computedAsVarchar] )
					when 'yes' then 1
					else 0
			   end		
			   
		, [length]				int not null

		, [lengthAsString] 	 
				as case
	
						when
						(
							([columnType] in ('varchar', 'nvarchar') )
						)
							then 
								case 
									when ([length] = -1) then 'max'
									else cast([length] as varchar(10))
								end

						when
						(
							([columnType] in ('numeric') )
						)
							then 
							    cast([length] as varchar(10))

						else ''

				  end
		, [precision]			int null		 			    
		, [scale]				int null		 			    

		, [nullabityAsVarchar]    varchar(10) not null
		, [nullabityAsBit] as
		       case ( [nullabityAsVarchar] )
					when 'yes' then 1
					else 0
			   end		

		, [collation]    varchar(60) null
								
		, [columnClause]
			as 
				quoteName([columnName])

					+ ' '
					+ replicate
						(
							   ' '
							 , 30 - len([columnName])

						)

					+ [columnType]	

					-- length
					+  case
							when
							(
								([columnType] in ('varchar', 'nvarchar') )
							)
								then
									 '(' 
									+ 
										case 
											when ([length] = -1) then 'max'
											else cast([length] as varchar(10))
										end
									+ ') '

							when
							(
								(
									[columnType] 
										in 
											(
												  'numeric'
												, 'float'
											) 
								)

							)
								then 
									 '(' 
									+ 
									+ cast([length] as varchar(10))
									+ ','
									+ cast
										(
											isNull
											(
												  [scale]
												, 0
											)	 
											as varchar(10))
									+ ')' 


						else ''

					  end

				  -- isNull
				  + case ( [nullabityAsVarchar] )
						when 'yes' then ' not null '
						else ' null '
					end		




	)

	
	declare @id				smallint
	declare @columnName		sysname
	declare	@idMax			smallint
	declare @columnClause	varchar(120)


	declare @no varchar(35)
	declare @yes varchar(35)
	declare @none varchar(35)
	declare @precscaletypes nvarchar(150)

	declare @sqlBuffer	nvarchar(max)

	declare @VARCHAR_SQLBUFFER_HEADER varchar(30)
	declare @VARCHAR_SQLBUFFER_FOOTER varchar(30)

	declare @CHAR_TAB	varchar(10)
	declare @CHAR_CRLF	varchar(10)
	declare @CHAR_COMMA varchar(10)
	declare @CHAR_SPACE varchar(10)

	declare @CHAR_BRACKET_OPENING char(1)
	declare @CHAR_BRACKET_CLOSING char(1)

	declare @VARCHAR_LABEL_OBJECTNOTFOUND varchar(100)

	declare @errorLog varchar(600)

	/*
		Init Variables
	*/
	set @precscaletypes = N'tinyint,smallint,decimal,int,bigint,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,'
	set @no = 'no'
	set @yes = 'yes'
	set @none = 'none'

	set @CHAR_TAB = char(9)
	set @CHAR_CRLF = char(13) + char(10)
	set @CHAR_COMMA = ','
	set @CHAR_SPACE = ' '
	set @CHAR_BRACKET_OPENING = '('
	set @CHAR_BRACKET_CLOSING = ')'

	set @VARCHAR_SQLBUFFER_HEADER = 'create table '
	set @VARCHAR_SQLBUFFER_FOOTER = ' '

	set @VARCHAR_LABEL_OBJECTNOTFOUND = 'Object %s not found in Tempdb'

	if (@objectName like '##%')
	begin
		set @tempTableType = 2

		select @objectID = tblSO.[object_id]
		from   [tempdb].sys.objects tblSO
		where  tblSO.name = @objectName 

	end
	else if (@objectName like '#%')
	begin

		set @tempTableType = 1
	
		select @objectID = tblSO.[object_id]
		from   [tempdb].sys.objects tblSO
		where  tblSO.name like @objectName + '%'

	end
	else
	begin
		set @tempTableType = 0
	end


	if (@objectID is null)
	begin

		/*
		exec master.dbo.xp_sprintf
				  @errorLog output
				, @VARCHAR_LABEL_OBJECTNOTFOUND
				, @objectName

		*/
		raiserror
		(
			  @VARCHAR_LABEL_OBJECTNOTFOUND
			, 10
			,1
			, @objectName
		)

		return -1
	end


	if (@debug = 1)
	begin

		select

				tblAC.[column_id]

			,  [columnName] = tblAC.name

			, [columnTypeID]
					= tblAC.user_type_id

			, [Type] 
					= type_name(user_type_id)

			, [Computed] 
					= case 
						when ColumnProperty(tblAC.object_id, tblAC.name, 'IsComputed') = 0 then @no 
						else @yes 
					  end

			, [Length] 	 
					= convert(int, max_length)

			-- for prec/scale, only show for those types that have valid precision/scale
			-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
			, [Prec]	= case 
								when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
									then convert(char(5),ColumnProperty(tblAC.object_id, tblAC.name, 'precision'))
								else '     ' 
						  end

			, [Scale]  
				=  case 
						when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
							then convert(char(5),OdbcScale(system_type_id,scale))
						else '     ' 
				   end

			, [Nullable] 
				= case 
					when is_nullable = 0 then @no 
					else @yes 
				  end

			, [TrimTrailingBlanks]	
				= case ColumnProperty(tblAC.object_id, tblAC.name, 'UsesAnsiTrim')
						when 1 then @no
						when 0 then @yes
						else '(n/a)' 
				  end

			, [FixedLenNullInSource] 
					= case
						when type_name(system_type_id) not in ('varbinary','varchar','binary','char')
							then '(n/a)'
						when is_nullable = 0 then @no else @yes 
					  end

			, [collationName] = collation_name

		from [tempdb].sys.all_columns  tblAC with (nolock)

		inner join [tempdb].sys.objects tblAO  with (nolock)

			on tblAC.object_id = tblAO.[object_id]

		inner join [tempdb].sys.schemas tblSS  with (nolock)

			on tblAO.schema_id = tblSS.[schema_id]

		where  tblAO.[type] = 'U'

		and    tblAO.object_id = @objectID

		order by

			tblAC.[column_id]

	end --if (@debug = 1)


	insert into @tblTable
	(

		  [columnID]
		, [columnName]

		, [columnTypeID]				
		, [columnType]			
		
		, [computedAsVarchar]   
			   
		, [length]				
		, [precision]			
		, [scale]				

		, [nullabityAsVarchar]   

		, [collation]  
	)
	select 
	      [columnID]
		  	= tblAC.[column_id]

		, [columnName] 
			= tblAC.name

		, [typeID]
			= tblAC.[user_type_id]

		, [Type] = type_name(tblAC.[user_type_id])

		, [Computed] 
				= case 
					when ColumnProperty(tblAC.object_id, tblAC.name, 'IsComputed') = 0 then @no 
					else @yes 
				  end

		, [length] 	 
				= convert
					(
						  int
						, tblAC.[max_length]
					)

		-- for prec/scale, only show for those types that have valid precision/scale
		-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
		, [precision]	= case 
							when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
								then convert(char(5),ColumnProperty(tblAC.object_id, tblAC.name, 'precision'))
							else '     ' 
					  end

		, [scale]  
			=  case 
					when charindex(type_name(tblAC.system_type_id) + ',', @precscaletypes) > 0
						then convert(char(5),OdbcScale(tblAC.system_type_id, tblAC.scale))
					else '     ' 
			   end

		, [Nullable] 
			= case 
				when tblAC.is_nullable = 0 then @no 
				else @yes 
			  end

		, [collation] = tblAC.collation_name

	from [tempdb].sys.all_columns  tblAC  with (nolock)

	inner join [tempdb].sys.objects tblAO  with (nolock)

		on tblAC.object_id = tblAO.[object_id]

	inner join [tempdb].sys.schemas tblSS with (nolock)

		on tblAO.schema_id = tblSS.[schema_id]

	-- select * from [tempdb]..systypes
    inner join [tempdb].sys.types tblST  with (nolock)
	    --on tblAC.user_type_id = tblST.[xtype]
	    on tblAC.user_type_id = tblST.[user_type_id]

	where  tblAO.[type] = 'U'

	and    tblAO.object_id = @objectID

	order by
	
		tblAC.[column_id]

	set @idMax = @@ROWCOUNT

	set @id = 1
	set @sqlBuffer = ''
	while (@id <= @idMax)
	begin

		select 
			@columnClause = [columnClause]

		from @tblTable

		where [id] = @id

		set @sqlBuffer = isNull(@sqlBuffer, '')
							+ @CHAR_CRLF 
							+ @CHAR_TAB
							+ case
								when (@id = 1) then ' '
								else @CHAR_COMMA
							  end
							+ @CHAR_SPACE
							+ @columnClause

		set @id = isNull(@id, 1) + 1

	end

	if (@sqlBuffer != '')
	begin

		set @sqlBuffer = @VARCHAR_SQLBUFFER_HEADER + @objectName  + @CHAR_CRLF + @CHAR_BRACKET_OPENING + @sqlBuffer
		set @sqlBuffer = @sqlBuffer + @CHAR_CRLF + @CHAR_BRACKET_CLOSING + @CHAR_CRLF + @VARCHAR_SQLBUFFER_FOOTER

	end


	if (@debug = 1)
	begin

		select *

		from   @tblTable

	end -- if @debug =1

	print @sqlBuffer

end
go


grant execute on [script].[sp_TableTempStructure] to [public]
go


Invocation


	declare @objectName sysname

	set @objectName =  '#listofDatabase'

	exec [script].[sp_TableTempStructure]
		@objectName = @objectName

 

Output

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s