Get Primary Key Column

Hello, this SQL query gets all columns of a table:
SELECT syscolumns.name AS [Fields in Items Database], syscolumns.type,syscolumns.length, syscolumns.isnullable FROM sysobjects INNER JOINsyscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name ='IssueTracker_IssueAttachments' ORDER BY syscolumns.colid
I would like to know, is there a way to get a column saying who is theprimary key in the table ? I am reading columns for tables andprocessing them, but I would like to know who is the primary key, isthat possible ?
regards

Your query is running in the Master Database, you need to run it in the Issue Tracker database and to see the primary key of a table go to Query Analyzer and run a Select all query with show results as a grid. But most large databases uses Identity column for keys so the clustered index will be small. Hope this helps.|||

Well no, I am running this script against the IssueTrackerStarterKit database. I am getting that table for sure, listing al columns, but I would like to know if I can know the primary key programmatically !!

regards

|||Run a search for sp_helpindex in SQL Server BOL(books online). And your select statement is using SQL- DMO(data management object) which is Microsoft property and all service packs makes changes which will make you code out dated because the tables have moved. Go to Query Analyzer open the object browser and right click on your table and you will have options of select statements without Syscolumns and Sysobjects. Hope this helps.|||Hi,
You can check a script listing Primary Key columns of a table by using this linkhttp://www.kodyaz.com/ShowPost.aspx?PostID=204
A simplified version is as below

declare @.tablename as sysname
set @.tablename = 'Customers'
declare @.tableid as int
select @.tableid = id from sysobjects where name = @.tablename
SELECT *
FROM syscolumns
INNER JOIN (
SELECT
*
FROM SysIndexKeys IK
WHERE
IK.Id = (select id from sysobjects where name = @.tablename)
AND IK.IndId = (select indid fromsysindexes where name = (select name from sysobjects where xtype = 'PK'and parent_obj = (select id from sysobjects where name = @.tablename)))
) PKColumns ON PKColumns.id = syscolumns.id AND PKColumns.colid = syscolumns.colid

I hope this helps
Eralper
http://www.kodyaz.com

|||Guys that was great, thanks a lot
I never used those system tables, are there any reference for them ? Are the SQL Server books good ?
thanks|||

The Systems tables have a poster but since SQL Server 2005 is almost here Microsoft have removed it and have made others to remove it. But they are all in the SQL Server Master database with the System title next to them. A good book for starting SQL Server for a developer is SQL Server a beginner's guide by Dusan Petkovic but don't let the title fool you it is not really a beginner's book it was given that title because English is not the writer's first language, he is German. The book covers everything usefull to a developer including complex configurations and the language, he also covered XML and Full text that most other books did not cover. I have a lot of them I call crappy but his book is worth the money for a C# developer, while SQL Server Developer's guide is good for VB developer. Try this link for T-SQL tutorial but I have sent you an ANSI SQL tutorial in the mail. Dowload the file because the site is now part of a consolidator so that file may be removed soon. Hope this helps.

http://www.mssqlserver.com/tsql/

|||

The Systems tables have a poster but since SQL Server 2005 is almosthere Microsoft have removed it and have made others to remove it. Butthey are all in the SQL Server Master database with the System titlenext to them.

Not exactly. the system tables are not removed. They are not tables anymore. They are just made as VIEWS now. So you can only do selectagainst them.
|||I did not say there are System tables in SQL Server 2005, I said the SQL Server 2000 poster was removed from the online location because SQL Server 2005 is almost here. I have known they are now views for a long time and I have always advised people not to use the System tables.