I realize that this is a really old post that I'm answering but, hopefully, it will help make someone's life a bit easier in the future.

Here's the code to make my "standard" test table in TempDB as a Temp Table. Right after that is a very small bit of dynamic SQL to make it possible and easy to use sp_help on a Temp Table. Note that I've not tested it on SQL Server 7 or 2000 but will likely work on those as well as all version from 2005 and later.

--===== Conditionally drop the test table to make reruns easier in SSMS.

IF OBJECT_ID('tempdb..#JBMTest','U') IS NOT NULL

DROP TABLE #JBMTest

;

GO

--===== Create a substantial test table with the following columns and values.

-- SomeID = Unique Integers starting at 1 up thru the number of rows generated.

-- SomeDate = Random Integers 1 thru 50,000

-- SomeLetters2 = Random letters "AA" thru "ZZ"

-- SomeDecimal = Random Decimal amounts from 0.00 up to and not including 100,000

-- SomeDate = Random Datetime from 2010-01-01 up to and not including 2020-01-01

-- SomeHex = Random hexidecimal characters with random locations of dashes.

SELECT TOP (1000000)

SomeID = IDENTITY(INT,1,1)

,SomeInt = ABS(CHECKSUM(NEWID()))%50000+1

,SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

,SomeDecimal = CAST(RAND(CHECKSUM(NEWID()))*100000 AS DECIMAL(9,2))

,SomeDate = RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2010','2020')+CAST('2010' AS DATETIME)

,SomeHex = SUBSTRING(LEFT(NEWID(),36),ABS(CHECKSUM(NEWID()))%37,ABS(CHECKSUM(NEWID()))%37)

INTO #JBMTest

FROM sys.all_columns t1

CROSS JOIN sys.all_columns t2

;

--===== Add the PK

ALTER TABLE #JBMTest

ADD PRIMARY KEY CLUSTERED (SomeID)

;

--===== Here's the simple command to use sp_help on the temp table.

EXEC ('USE tempdb; EXEC sp_help #JBMTest')

;