September 23, 2008 at 6:46 am
Hi,
I would like to find the equivalent command to describe the property of a temp table.
You can use sp_help tablename where tablename is permanent...
But get an error message when using sp_help on a temptable.
Any advice
nb. Is there an equivalent Oracle 'Desc ' in SQL Server
September 23, 2008 at 7:41 am
Hi,
I use the following script to show the columns in temporary tables. It can easily be wrapped in to a store procedure or function to produce similar repeatable results.
SELECTso.xtype, so.id, so.name as TableName,
sp.value AS description,
sc.name as ColumnName,
UPPER(st.name) as DataType,
sc.length,
CASE sc.isnullable WHEN 1 THEN 'Yes' ELSE 'No' END AS isnullable,
CASE WHEN scom.text IS NULL THEN 'No' ELSE 'Yes' END as isComputed,
CASE WHEN scom.text IS NULL THEN '' ELSE scom.text END as formula,
CASE WHEN sik.keyno IS NULL THEN '' ELSE si.name END AS [index],
ISNULL(CAST(sik.keyno AS VARCHAR), '') AS [idx_order]
FROM syscolumns sc
JOIN sysobjects so ON sc.id = so.id
JOIN systypes st ON sc.xtype = st.xtype
LEFT JOIN sysindexes si ON so.id = si.id AND si.indid = 1
LEFT JOIN sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid AND sc.colid = sik.colid
LEFT JOIN sysproperties sp ON sc.id = sp.id AND sc.colid = sp.smallid
LEFT JOIN syscomments scom ON sc.id = scom.id AND sc.iscomputed = scom.colid
WHERE so.type = 'U'
AND so.name LIKE '#YourTempTablename%'
ORDER BY so.xtype, so.name, sc.colorder
October 22, 2015 at 8:37 am
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')
;
--Jeff Moden
Change is inevitable... Change for the better is not.
December 10, 2015 at 3:34 pm
The post is now even older, but this command would easier to remember and place in code:
EXEC tempdb..sp_help #JBMTest
No need for dynamic SQL.
Oh, the beauty of system procedures!
🙂
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply