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 |
|
macsterling
Yak Posting Veteran
56 Posts |
 Posted - 2008-11-09 : 19:44:58
|
| There is a function called COL_NAME that will give the Column name if you provide the Table id and the Column IDCOL_NAME(4,3) will give the name of the third column in table 4. How do you find out what the table_ID is ( I need to interrogate a table based on the column name. The above function works as I have gotten column names, but I was testing it by just tossing numbers in. We have over 100 tables in this databse, and trial and error will tke a long time. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-09 : 20:51:58
|
| Are you looking for this one?SELECT TABLE_SCHEMA + '.' + TABLE_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME = 'yourdesiredcolumn' |
 |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2008-11-09 : 22:48:07
|
Read about "OBJECT_ID"From BOL : Returns the database object identification number of a schema-scoped object."There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!" |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-09 : 23:09:43
|
| [code]SELECT * from sys.objects where object_name(object_id)='YourTabelName Here'[/code]and if sql 2000 use sysobjects instead of sys.objects |
|
|
|
macsterling
Yak Posting Veteran
56 Posts |
Posted - 2008-11-09 : 23:32:35
|
| GOT IT!! had to run the select COL_NAME with the ID from your select and the column number, but it worked!!Thanks a million (which is what I am going to charge the client - he has been trying to figure out how to do this for days). I will be able to code the solution and get it back by tomorrow. I won't tell him I got help, just hand him the code. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-10 : 00:32:18
|
Cheers  |
|
|
|
|
|
|
finding the table ID