| Author |
Topic |
|
billsox
Yak Posting Veteran
74 Posts |
 Posted - 2003-02-27 : 16:03:52
|
| Hi,I have a sproc that creates a temporary table. I want to be able to query the INFORMATION_SCHEMA.COLUMNS view in tempdb to return schema information on my temp table. My problem is that since SQL Server appends a unique string to the end of my table name, I can't think of any way of identifying my table for retrieval. Any ideas?Thanks in advance,Bill |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-27 : 16:10:13
|
| How about:SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'TABLE1%'Where TABLE1 is the name of your temp table.Brett8-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-27 : 16:12:31
|
| Why would you need to do this? When you created the temporary table, you have the table layout already in the CREATE statement. The INFORMATION_SCHEMA.COLUMNS will just give you the same information that your CREATE statement has.Anyway, I can't think of a way to do this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-27 : 16:13:23
|
| X002548,Wouldn't that return multiple tables though when this stored procedure is called by numerous clients? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-27 : 16:26:33
|
| Yup, It sure will...with out further requirements it's kind of hard to give a better solution. I do know that sysobjects has a column called refdate.billsox, what do you need these for.I agree with with Tara (tduggan) on this. You should already know the name. In addition local temp tables (they are local right?) are associated with a session, and hence will disappear when the session is over (I still believe you should drop it as a housekeeping procedure though).If it's a Global Temp Table you won't be able to creat more than 1 with the same name.Let us know and maybe we can help.ThanksBrett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-02-27 : 16:43:24
|
| Are you using SELECT INTO to create your temporary table? That's probably the only way I can think of where you might be creating temporary tables but then not knowing what the exact schema is because you don't have to specify it....- JeffEdited by - jsmith8858 on 02/27/2003 16:43:48 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-27 : 16:56:47
|
| Jeff,Que?I don't followBrett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-02-27 : 17:03:19
|
| I believe he's referring to SELECT * INTO Table2 FROM Table1. My only comment is that you will still know about the table layout because you know what Table1 (in this example) looks like. If you only select a few columns from Table1, you still know what data types they are. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-27 : 17:27:19
|
| Well, that's true if you're selecting from tables, but if you're selecting from a view, or a query with expressions in it, you won't necessarily be able to map them back to a column or datatype. Nor would user-defined datatypes map properly.As far as identifying the actual temp table name, you can use this:SELECT T.name FROM tempdb..sysobjects T WHERE T.id=OBJECT_ID('tempdb..#myTempTable')You could also use OBJECT_ID to join to tempdb..syscolumns to get the column info, and further to tempdb..systypes to get their datatypes, but if you get the table name you could just substitute it into tempdb.INFORMATION_SCHEMA.COLUMNS:SELECT TIC.* FROM tempdb.INFORMATION_SCHEMA.COLUMNS TICINNER JOIN tempdb..sysobjects T ON TIC.TABLE_NAME=T.name WHERE T.id=OBJECT_ID('tempdb..#myTempTable') |
|
|
|
|
help getting schema info from temp tables