determine date type of a temp table

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 2008 Forums

Transact-SQL (2008)

determine date type of a temp table

Author

Topic

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-20 : 17:58:17

Is there a way to determine the data type of a column in a temporary table?


select data_type 
from information_schema.columns

works for regular tables but in

 
select data_type FROM tempdb.sys.columns

the data_type parameter does not exist in the temp table schema?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-20 : 18:27:44

It's not that data_type does not exist in the temp table schema, it's that data_type is not a valid column in sys.columns, no matter what database you're in.
Join to sys.types on sys.columns.user_type_id = sys.types.user_type_id and take a look at all the columns in sys.types.

--
Gail Shaw
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-21 : 03:30:58

or make use of information_schema.columns view

Madhivanan

Failing to plan is Planning to fail

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-02-21 : 10:27:34

I have never been able to successfully use informaton_schema.columns in tempdb probably because of the funny names that tempdb assigns to your temporary tables. So I have always done it the hard way that Gail described, as in

select 
	c.name as columnname
	,t.name as datatype
from 
	tempdb.sys.columns c
	inner join sys.types t 
		on  c.system_type_id = t.system_type_id
where 
	object_id('tempdb.dbo.#MyTempTableName') = object_id;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-24 : 03:01:15

or look at the second resultset

exec tempdb..sp_help '#MyTempTableName'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources