how to get column names from a hash table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

how to get column names from a hash table?

Author

Topic

greeny122229
Starting Member

25 Posts

Posted - 2010-05-17 : 05:19:29

Hi Guys,

Any one know how to retrieve column names from a hash table?.

this is linked to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144608

P.S. Hash tables don't appear to be logged in INFORMATION_SCHEMA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-05-17 : 05:45:01

You mean temporary tables? (hash tables are a completely different thing, used internally by SQL to do some joins)
You can query the system views in TempDB. Beware, the name you use when creating the temp table is not exactly the name it will have in tempdb.sys.tables.

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-17 : 05:53:16

You need to reference the OBJECT_ID as the name is stored internally as a unique name. (it adds hidden characters).

Here's something you can run to see what I mean.


CREATE TABLE #foo (
	[colA] INT
	, [colB] INT
	, [colC] VARCHAR(255)
	)

-- This won't work
SELECT * FROM tempDb.information_Schema.columns WHERE [table_name] = '#foo'

-- This does work
SELECT * FROM tempDb.sys.columns WHERE [object_ID] = OBJECT_ID('tempDb..#foo')

DROP TABLE #foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

greeny122229
Starting Member

25 Posts

Posted - 2010-05-17 : 05:59:27

Cool, thanks guys, turns out i didn't need this in the end but have bookmarked for future ref.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources