Basic Logics · Real Scenario Logic's

To View Structure of Temp Table

Introduction

Many of us will use common methods like Keyboard Shortcut – ‘Alt+F1‘ or will use ‘SP_HELPTEXT‘ Command (so many other methods are also there) to view the Structure of Physical Table. As we all know, Viewing the Structure of Temp Table is not as common as Viewing the Structure of Physical Table. In this Article we are going to see, how to view the Structure of Temp Table easily in SQL Server. The below mentioning methods are applicable at both Azure SQL DB and On-Premises.

Demo SQL Script

IF OBJECT_ID('TempDB..#TempTable') IS NOT NULL
	DROP TABLE #TempTable;

SELECT 1 AS ID,'Arul' AS Names
INTO 
#TempTable;

SELECT * FROM #TempTable;

METHOD 1 – Using SP_HELP

SQL Script-

EXEC TempDB..SP_HELP #TempTable;

Image Pic-

Note-

In the Table Structure, the Table Name shows something like ‘#TempTable__________________________________________________________________________________________________________0000000004CB’. Actually, the total length of each and every Temp Table name will be 128 . To handle the Same Temp Table name in Multiple Sessions differently, SQL Server will automatically add some underscores in between and alphanumeric’s at end.

METHOD 2 – Using SP_COLUMNS

SQL Script-

EXEC TempDB..SP_COLUMNS '#TempTable';

Image Pic-

METHOD 3 – Using System Tables like INFORMATION_SCHEMA.COLUMNS, SYS.COLUMNS, SYS.TABLES

SQL Script-

SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (
SELECT NAME FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable')
);
GO

SELECT * FROM TempDB.SYS.COLUMNS WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable');
GO

SELECT * FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#TempTable');
GO

Image Pic-

Conclusion

Thus, we saw some of the many methods to View our Temporary Table Structure easily in SQL Server. All of the above Scripts are Unit tested in both Azure SQL Db and On-Premises.

Thanks for reading!

FOLLOW BLOG for Interesting Upcoming Posts!

Advertisement

5 thoughts on “To View Structure of Temp Table

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 )

Connecting to %s