Get structure of temporary table

I was struggling to get the structure of a temporary table.

The idea was to pull out the temporary database information “tempdb.sys.columns” , then format them in order to display the outcome in a form of a SQL query.

CREATE Procedure [dbo].[GetStructureOfTemporaryTable]
@TableInput NVARCHAR(100)
AS
BEGIN
DECLARE @TableOutput NVARCHAR(3000);
DECLARE @TableCol1 NVARCHAR(100);
SET @TableOutput = ‘Create table ‘ + @TableInput + ‘(‘ + char(13) ;
DECLARE TableLines CURSOR FOR
SELECT ‘[‘ + name + ‘]’ + ‘ ‘ + type_name(user_type_id) +
CASE WHEN type_name(user_type_id) = ‘decimal’ Or type_name(user_type_id) = ‘numeric’ THEN
‘(‘ + convert(varchar, Precision) + ‘, ‘ + convert(varchar, scale) + ‘) ‘
ELSE
CASE WHEN (type_name(user_type_id) IN (‘float’ , ‘char’ , ‘varchar’, ‘nchar’ , ‘nvarchar’ , ‘binary’ , ‘varbinary’ ))
THEN
‘(‘ + convert(varchar, max_length) + ‘)’
ELSE ” –”
END
END
+ CASE WHEN is_nullable = 1 THEN ‘ NULL ,’ ELSE ‘ NOT NULL , ‘
END As Col1
FROM tempdb.sys.columns WHERE object_id=OBJECT_ID(N’tempdb.dbo.’ + @TableInput ) ;
OPEN TableLines
FETCH NEXT FROM TableLines INTO @TableCol1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @TableOutput += ” + @TableCol1 + char(13) ;
FETCH NEXT FROM TableLines INTO @TableCol1
END
SET @TableOutput = LEFT(@TableOutput, LEN(@TableOutput) – 2)
SET @TableOutput += ‘)’;
PRINT @TableOutput ;
CLOSE TableLines;
DEALLOCATE TableLines;
END

Result :

Create table #TMP_TABLE (
limite int NULL ,
ComponentProductNo nvarchar(320) NOT NULL ,
PHASE nvarchar(160) NULL ,
MPPHASE nvarchar(488) NULL ,
TotalQtyToWeigh decimal(15, 6) NULL ,
WeighedQuantity decimal(38, 6) NULL ,
ActualPotencyPercent decimal(28, 10) NULL ,
WeighAccuracy decimal(32, 14) NULL ,
SourceContainerCount int NULL ,
ReceivingContainerCount int NULL ,
WeighingLine int NULL ,
ManualWeighingLine int NOT NULL ,
IntroType nvarchar(960) NULL ,
UsageType smallint NULL ,
WeigherCount int NULL ,
EquipmentCount int NULL ,
NumOfWorkstations int NULL ,
Deviation varchar(1) NOT NULL ,
NumOfWeighingErrors int NOT NULL ,
cancelledflag int NULL )

Advertisements
This entry was posted in T-SQL / SQL and tagged , , , , . Bookmark the permalink.

2 Responses to Get structure of temporary table

  1. If you desire to increase your experience simply keep visiting this site and be updated
    with the newest news posted here.

  2. Have you ever considered about including a little bit more
    than just your articles? I mean, what you say
    is fundamental and all. However just imagine if you
    added some great photos or video clips to give your posts more, “pop”!
    Your content is excellent but with pics and video clips, this blog
    could undeniably be one of the best in its field.

    Good blog!

Leave a Reply

Fill in your details below or click an icon to log in:

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