Correct way of finding a tables primary keys?

Hope this is in the right thread, sorry if not!

I have run into a problem, i need to find out that column(s) in a table that makes the primary key.

I thought that this code did the trick.

***

DECLARE @.c varchar(4000), @.t varchar(128)
SET @.c = ''
SET @.t='contact_pmc_contact_relations'
Select @.c = @.c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @.t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@.c, 1, Datalength(@.c) - 1)

***

This works in most of my cases. But i have encounterd tabels where this code doesn't work.

Here is a dump from one of the tabels where it doesn't work.

SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table

***

id indid colid keyno

933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1

Not sure if that dump made any sense, but i hope it did.

If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).

So, anyone know how i could solve this problem?

Regards

/Anders

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

|||

SQL Server 2000 ou 2005 ?

|||Currently we'r using sql server 2000, but if there is a way that works on both 2000 and 2005 it would be really great!|||

Madhu K Nair wrote:

to get the pk one of the best and recommended method will be

select *from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_type='PRIMARY KEY'

Madhu

Either im missunderstanding or that doesn't work. I want to get the columns that compose the primarykey. Not the primary key itself.

|||

i got the issue. Since there is no direct method (Single select statement), I will give u a workaround. If u check sp_helpconstraint , it give u the details. U can use sp_helptext to find the sourcecode behind this SP and do the required modification adn create u r own SP.

Madhu

|||

select * from INFORMATION_SCHEMA.[CONSTRAINT_COLUMN_USAGE]

where constraint_type='PRIMARY KEY'

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Is this you are looking for?

Code Snippet

SELECT CCU.TABLE_NAME

, CCU.COLUMN_NAME

, ORDINAL_POSITION

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

JOIN INFORMATION_SCHEMA.[KEY_COLUMN_USAGE] CCU

ON TC.TABLE_NAME = CCU.TABLE_NAME

AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME

WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

ORDER BY CCU.TABLE_NAME, ORDINAL_POSITION

Just expanding to what others meant to answer to your question.

Copying Tables and generating new keys

I have a large table that I need to copy, but I need to generate a new value for my id field using a SPROC and replace my existing ID value. I also have a few mapping tables I need to copy, so I need to store this new ID for later use. I currently have a SPROC that performs all these actions, but it takes about 3 or 4 minutes to complete and completely hogs the CPU time. Thus, I can't perform any actions until it finishes.

I'm looking for a way to run this procedure in the background. Unfortunately, my ID field value is not a GUID nor an IDENTITY column. I've researched Integration Services, but I was unable to find any DataFlow Tranformations to call a SPROC to retreive a new id nor could I find anything that would let me store my new id to update my mapping tables. SQLBulkCopy wasn't a good solution either.

If anyone has any insight to this, it would be greatly appreciated. Thanks,

You can use the OLE DB Command to call a stored proc and get an output value, but it tends to slow down execution. Any way to change this to a set based operation?

Copy Tables in SQL 2k5 - Need Help

I have a database - we can call it DatabaseX and I want to copy the tables, keys and indexes to DatabaseY. I tried doing and export/import and it worked for the data but the identity settings were lost. How can I maintain the integrity of the tables and copy the data at the same time?

The Copy Database Wizard is an option when Service Pack 2 is released. Currently Service Pack 2 is available as a CTP http://www.microsoft.com/sql/ctp.mspx|||

simple way is to follow conventional Backup/Resotre method... if it is on the same machine there is no issue.. otherwise u may need to transfer the logins also....

Refer

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

also in BOL Backup database /restore database

Madhu

|||

If you want to copy objects into database X which already exists and has objects you can use bcp with the -E switch to keep identity values.

See SQL Server 2005 Books Online topic:
bcp Utility
http://msdn2.microsoft.com/en-US/library/ms162802.aspx

You can also use the SSIS Transfer Objects task.

Copy table Structure including primary keys, index etc.

Hi all,

I was wondering if there is a SQL command to copy the table structure of a table that includes primary keys, foreign keys, indexes, etc.

Thanks and have a nice day to all

Not a SQL command, but you can script this stuff out using the tools by right clicking the table, or programatically using SMO.

|||can you post a sample script or SMO please or send me a link discuss this matter thanks|||

Hi,

The easiest way to create the script is to right click the original table within SQL Server Management Studio and select "Script Table As ...\ Create To\ ..." . This will create a script for the table and its indexes.

Then you need to run the script but with the new tablename. After that, you need to copy the records using a insert/select command. (it is best to set the constraints/indexes afterwards).

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

I was thinking to use that script in my SP, On my SP I add a linked server then i want to copy all the tables exactly the same

and on the linked server the table names and table count change everyday, but i dont have any problem with that.

I was thinking if there is a way to copy exactly the same table inside an SP in that case.

Thanks

|||You can use sys tables/views inside your SP|||

can you post your samples script please.

thanks

|||

SELECT * INTO NewEmployee FROM Employee WHERE 1 = 0

above query will create same structure table called NewEmployee with structure of Employee. But will not have triggers and primary keys etc. you can create them by using follwing scripts

SELECT *

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee')

SELECT *

FROM syscomments

WHERE id IN ( SELECT id

FROM sysobjects

WHERE parent_obj = OBJECT_ID('Employee') )

|||thanks for the reply Dinesh. Nice sql Stmt, does this work when you have a MS Access linked Server, is there a sysobjects table on the linked server?