SQL Server Performance

Using sp_primarykeys

Discussion in 'T-SQL Performance Tuning for Developers' started by cjai_prakash, Jul 29, 2004.

  1. cjai_prakash New Member

    HI
    I have the SQL Server client which is connected to the Network SQL Server. I tried to
    execute the sp_primarykeys for adding a primary key to a table.

    emp
    ------
    emp_id int
    emp_name char(20)

    I tried this query

    sp_primarykeys emp, emp_id

    I got the error message

    Server: Msg 7202, Level 11, State 2, Procedure sp_primarykeys, Line 7
    Could not find server 'emp' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

    Then i added the server(TEST_SERVER) by using sp_addlinkedserver .

    I tried this query

    sp_primarykeys TEST, emp, empid

    The error is TEST doesn't have any data access.

    I found that, our server is under the Remote server in the Enterprise Manager. And I removed the server from remote server and added to linked servers.

    Then I tried the same query, it is executing successfully and returning the o/p as

    TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
    ---------------------------------------------------------------
    I verified that there is no key generated for the table emp.

    How I can rectify this problem ?

    How to create the primary key without creating the constraint ? [/size=1][/size=2]

    Cheers

    Jayaprakash Chandrasekaran
  2. satya Moderator

    SP_primarykeys returns the primary key columns, one row per key column, for the specified remote table.

    To define a primary key

    In your database diagram or Table Designer, click the row selector for the database column you want to define as the primary key. If you want to select multiple columns, hold down the CTRL key while you click the row selectors for the other columns.


    Right-click the row selector for the column and select Set Primary Key. A primary key index, named "PK_" followed by the table name, is automatically created; you can find it on the Indexes/Keys tab of the property pages.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. FrankKalis Moderator

    When you want to add a primary key to an existing table you need to do something like


    create table pk_t
    (
    empid int not null
    , emp_name varchar(20)
    )
    go
    alter tablepk_t
    add constraint pk_empid primary key(empid)
    go
    exec sp_pkeys 'pk_t'
    drop table pk_t
    Also note the difference between sp_pkeys and sp_primarykeys, which is explained in BOL

    --Frank
    http://www.insidesql.de
  4. FrankKalis Moderator

    ...forgot to add:
    In order to add a primary key constraint to a column, it needs to benot null. Maybe you have to modify the above script to:


    create table pk_t
    (
    empid int
    , emp_name varchar(20)
    )
    go
    alter table pk_t
    alter column empid int not null
    go
    alter tablepk_t
    add constraint pk_empid primary key(empid)
    go
    exec sp_pkeys 'pk_t'
    drop table pk_t


    --Frank
    http://www.insidesql.de

Share This Page