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.
| Author |
Topic |
|
cosmic_Nomad
Starting Member
3 Posts |
 Posted - 2006-03-07 : 02:20:09
|
| Hi,I have a requirement wherein I need to list the COLUMNs of a #table created in a session. This #table is created using a Stored SQL in a database table and hence would be different for different processes/situations.I need to identify the column name of this #table so that based on the COLUMN NAME, i'll apply different business logic on the content of the #table.Information.schema_columns doesn't help ... sysobjects,syscolumns doesn't help either.Hope that someone can help me !Thanks in Advance.- Mahesh Gopal |
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2006-03-07 : 02:26:54
|
| how about sp_help #table_nameWhen u create a temp table it is stored in sysobjects as table_name_____some_value u definately can find the table in sysobjectsuse tempdbcreate table #table2 (a int,b int)select name from syscolumns where id in(select id from sysobjects where name like '#table2%') |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-07 : 02:36:27
|
sp_help #table only works if you are in the tempdb.select name from tempdb..syscolumns where id = object_id('tempdb..#table')----------------------------------'KH' |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-03-07 : 02:43:43
|
to use INFORMATION_SCHEMA you have to be in that database. For this case, tempdb.use tempdbselect * from tempdb.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = object_name(object_id('#table'))temp table #table is not keep as '#table' but '#table_________________xxxx' so you have to use convert to id first then convert back to name in order to equate it with TABLE_NAME column.----------------------------------'KH' |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-07 : 03:06:47
|
| or1 select * from tempdb.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '#table%'2 tempdb..sp_columns '#table'MadhivananFailing to plan is Planning to fail |
|
|
|
cosmic_Nomad
Starting Member
3 Posts |
Posted - 2006-03-07 : 03:26:25
|
| The below query will list all the #world concurrently created in different sessions.... hence not an ideal solution.select * from tempdb.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '#world%'However this satisfies my requirement.select name from tempdb..syscolumns where id = object_id('tempdb..#world')Thanks for your valuable inputs !- MG |
|
|
|
|
|
|
how to list the columns of a #table ?