I recently ran into a situation where I needed to get the primary key of a table, knowing only the table name. Using the following code, you can pass it a table name, and it will return the primary key:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
<cfcomponent>
<cffunction name="getPrimaryKeyName" output="false">
<cfargument name="Table_Name">
<cfquery name="getPrimaryKeyName" datasource="#request.maindsn#">
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = <cfqueryparam cfsqltype="cf_sql_text" value="#UCase(arguments.Table_Name)#">
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position
</cfquery>
<cfreturn getPrimaryKeyName.column_name>
</cffunction>
</cfcomponent>
1<cfcomponent>
2 <cffunction name="getPrimaryKeyName" output="false">
3 <cfargument name="Table_Name">
4
5 <cfquery name="getPrimaryKeyName" datasource="#request.maindsn#">
6 SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
7 FROM all_constraints cons, all_cons_columns cols
8 WHERE cols.table_name = <cfqueryparam cfsqltype="cf_sql_text" value="#UCase(arguments.Table_Name)#">
9 AND cons.constraint_type = 'P'
10 AND cons.constraint_name = cols.constraint_name
11 AND cons.owner = cols.owner
12 ORDER BY cols.table_name, cols.position
13 </cfquery>
14
15 <cfreturn getPrimaryKeyName.column_name>
16 </cffunction>
17</cfcomponent>
Then you only need to call the function to get the primary key name:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
<cfinvoke method = "getPrimaryKeyName" returnVariable = "request.PrimaryKeyName">
<cfinvokeargument name="Table_Name" value="#arguments.Table_Name#">
</cfinvoke>
1<cfinvoke method = "getPrimaryKeyName" returnVariable = "request.PrimaryKeyName">
2 <cfinvokeargument name="Table_Name" value="#arguments.Table_Name#">
3</cfinvoke>
My core expertise focuses on architecting multi-lingual, enterprise-level digital solutions, including custom CMS's and large-scale applications which span multiple devices and computing languages.
Comments
There are no comments for this entry.
[Add Comment] [Subscribe to Comments]