Re: Get Primary Key column

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Quentin Ran (ab_at_who.com)
Date: 04/08/04 

Date: Thu, 8 Apr 2004 14:34:03 -0500

Steve,

with the condition that the PK contains only 1 column, you may add the
following to your code:

declare @columnName varchar(55)

select @columnName = c.column_name
from information_schema.constraint_column_usage c
inner join sysobjects o on o.name = c.constraint_name
where o.xtype = 'PK'
and o.parent_obj = object_id(@tableName)

and change

SET @Sql = 'SELECT * FROM ' + @TableName + ' WHERE ' + @columnName + ' = ' +
@Value

Please note that this is accessing both the information_schema view and
system tables.

Quentin

"Steve" <stevea@centurion-ms_RemoveThis_.co.uk> wrote in message
news:eL7iyXXHEHA.2128@TK2MSFTNGP11.phx.gbl...
> Hi Adam
>
> All my Primary Keys are composed of 1 column, I thought I read somewhere
> that you could do what I'm after, but I guess not.
>
> I would like to write 1 stored procedure that can perform the same
function
> on any table depending on the parameters I pass to it. For example, a
Stored
> Proc takes a table name and a value as input parameters, and in the proc I
> execute the following statement:
>
> Declare @Sql nvarchar(100)
> SET @Sql = 'SELECT * FROM ' + @TableName + ' WHERE myPrimaryKey = ' +
> @Value
> Exec sp_executesql @sql
>
> Using this I do not have to specify the actual name of the PK column, so
it
> makes the proc more general. This is only 1 example of how I would use it,
> there are more. Basically, the tables only have 1 primary key and I
thought
> that there was a SQL name that I could use to refer to that column. I know
> that I could get the name of the PK from the system tables, but a 1 line
> solution is much more preferable :o)
>
> Thanks for your quick response!
>
> Kind Regards,
> Steve.
>
> "Adam Machanic" <amachanic@air-worldwide.nospamallowed.com> wrote in
message
> news:eQBK4PXHEHA.1048@TK2MSFTNGP12.phx.gbl...
> > No, and a primary key can also be a composite of multiple columns, so
that
> > wouldn't be possible anyway...
> >
> > What is it you're trying to accomplish? If you can post more details,
> > perhaps we can help you find a solution.
> >
> >
> > "Steve" <stevea@centurion-ms_RemoveThis_.co.uk> wrote in message
> > news:OaaV8MXHEHA.2260@TK2MSFTNGP09.phx.gbl...
> > > Hi all
> > >
> > > Is there a way in SQL to reference the Primary Key column without
using
> > the
> > > name of the column?
> > >
> > > Something like: Select * From Table1 Where myPrimaryKey = '1'
> > >
> > > Kind Regards,
> > > Steve.
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Views vs Stored Procedures, whats the difference?
    ... In the proc I would write the MAX as you've done. ... @optional_parm1 int = NULL, ... While the above does contain logic, it will give you the best plan in MS SQL ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)
  • Re: Get Primary Key column
    ... Proc takes a table name and a value as input parameters, ... the tables only have 1 primary key and I thought ... that there was a SQL name that I could use to refer to that column. ... > No, and a primary key can also be a composite of multiple columns, so that ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • Re: Binding and adding rows with SQL identity column
    ... Each time you call AddNew then the DataTable generates a new ... Each time an update is done then the real primary key ... > generated by the built-in wizard in VS2003. ... > identity column on the sql table, perhaps this would all the fine and I ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: What happens after 2^32 autonum keys are exhausted?
    ... Tony Rogerson, SQL Server MVP ... generated primary key despite the risk of a hardware or software error or a fault in the space-time continuum. ... identifier and have to invent one, follow the data design (data ...
    (comp.databases)