I actually found a good way to get the real Primary Key for a given table, TABLENAME:
EXECUTE SP_PKEYS 'TABLENAME' This will return a recordset with: TABLE_QUALIFIER TABLE_OWNER TABLE_NAME COLUMN_NAME (This is what I wanted) KEY_SEQ PK_NAME Thanks for all of the insight! -----Original Message----- From: Scott Van Vliet [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 17, 2002 11:26 PM To: CF-Talk Subject: RE: Dynamically Obtaining IDENTITY Column This works as well, however, using SQL functions can be faster. SELECT COL_NAME(OBJECT_ID('#tablename#'),1) As IDColumn This does the job in one line (rather than the query, and then the list function. ^_^ SV2 -----Original Message----- From: Claudia Hoag [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 17, 2002 7:21 PM To: CF-Talk Subject: RE: Dynamically Obtaining IDENTITY Column Ooops I meant <cfset yourcolumn = listfirst(firstquery.columnlist)> At 10:18 PM 2/17/2002 -0500, you wrote: >Well, if you just want to know the name of the first column in your table, > >why don't you do a "select * " and then get the listfirst of > query.columnlist ? > >such as > ><cfquery name="firstquery" datasource="dsn" maxrows="1"> >select * from yourtable ></cfquery> > ><cfset yourcolumn = listlast(firstquery.columnlist)> > ><cfquery name="secondquery" datasource="dsn"> >select #yourcolumn# from yourtable ></cfquery> > > >At 12:47 PM 2/17/2002 -0800, you wrote: > >I have come up with a hack solution: > > > >DECLARE @IDCOLUMN varchar(50) > >DECLARE @OBJ varchar(50) > >DECLARE @OBJID int > >DECLARE @STATEMENT varchar(50) > > > >SET @OBJ = 'PRODUCTS' > >SET @OBJID = OBJECT_ID(@OBJ) > >SET @IDCOLUMN = COL_NAME(@OBJID,1) > > > >SELECT @IDCOLUMN As IDColumn > > > >Given that the identity column is the first column in the table (which > >most of my tables are), this will work (you can change this in the > >COL_NAME function). It can be shortened to: > > > ><cfset tablename = "PRODUCTS"> > > > ><CFQUERY NAME="qry1" DATASOURCE="dsn"> > >SELECT COL_NAME(OBJECT_ID('#tablename#'),1) As IDColumn > ></CFQUERY> > > > >This could be used in another query, such as: > > > ><CFQUERY NAME="qry2" DATASOURCE="dns"> > >SELECT #qry1.IDColumn# As IDColumn > >FROM #tablename# > ></CFQUERY> > > > >Thanks for all the input! If anyone knows a better way, please let me > >know! > > > > > > > >-----Original Message----- > >From: Jeff Beer [mailto:[EMAIL PROTECTED]] > >Sent: Sunday, February 17, 2002 11:42 AM > >To: CF-Talk > >Subject: RE: Dynamically Obtaining IDENTITY Column > > > >Scott - do you mean the identity value of a newly inserted record, or to > >select the ID value when you don't know the name of the identity column? > > > >-----Original Message----- > >From: Scott Van Vliet [mailto:[EMAIL PROTECTED]] > >Sent: Sunday, February 17, 2002 2:22 PM > >To: CF-Talk > >Subject: Dynamically Obtaining IDENTITY Column > > > > > >Hey All. > > > >Does anyone out there know of a way to dynamically grab the identity > >column from a table in a select statement? > > > >Example: > > > ><CFQUERY NAME="queryname" DATASOURCE="dsn"> > >SELECT @@IDENTITY_COLUMN > >FROM TABLE_NAME > ></CFQUERY> > > > >Where "@@IDENTITY_COLUMN" would be the function to obtain the value of > >the identity column. > > > >TIA. > > > >---- > >SCOTT VAN VLIET > >BRD.WRKS INTERACTIVE > >[EMAIL PROTECTED] > > > > > > > > > > > ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
