SQL Server Performance Forum – Threads Archive
Using sp_primarykeys
HII 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
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.
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
…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
]]>