Stored procedure to give primary key column details of table

Question

Hi

We want to write a stored procedure which will give what are the primary keys of a specific table and what are its data type etc.. i.e. details about that column such as type and length

we have tried following SPROC

EXEC sp_pkeys '<table name>'

Also this SPROC should work on Microsoft SQL Server 2000, 2005, 2008 and its later version.

Please advise us in this.

Thanks and Regards
Prasad

Santhosh Kumar Jayaraman · Answer

Create proc sp_getprimarykeydetails (@tablename varchar(500)) as select c.COLUMN_NAME,c.TABLE_NAME,c.DATA_TYPE, (Case when C.DATA_TYPE like '%Char%' then CHARACTER_MAXIMUM_LENGTH when c.DATA_TYPE like '%int%' or c.DATA_TYPE like '%decimal%' then NUMERIC_PRECISION when c.DATA_TYPE like '%date%' then DATETIME_PRECISION Else Null End) as Precision, (Case when c.DATA_TYPE like '%int%' or c.DATA_TYPE like '%decimal%' then NUMERIC_SCALE Else Null End) as NumericScale from sys.objects o inner join sys.indexes i on i.object_id=o.object_id and is_primary_key=1 inner join information_Schema.key_column_usage kcu on kcu.CONSTRAINT_NAME=i.name inner join information_Schema.columns c on c.COLUMN_NAME=kcu.COLUMN_NAME and c.TABLE_NAME=o.name where o.name =@tablename

Prasad Godbole · Answer

Hi , Yes Santhosh solution is good. I got 1 more solution and posting it here so that it will help others SELECT c.name 'Column Name', t.Name 'Data type', c.max_length 'Max Length', c.precision , c.scale , c.is_nullable, ISNULL(i.is_primary_key, 0) 'Primary Key' FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id = t.system_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id WHERE c.object_id = OBJECT_ID(' ') and ISNULL(i.is_primary_key, 0)=1

Jignesh Trivedi · Answer

hi, Santhosh solution is good one. you can also Edit or create new SP from sp_pkeys. it is located SystemDataBases >> Master >> store Procedures >> System store Procedures hope this will help you.

Prasad Godbole · Answer

Hi Santhosh, It's ok no problem. We are testing this on sql 2000. Thanks and Regards Prasad

Santhosh Kumar Jayaraman · Answer

I dont have SQL server 2000 with me, so i couldnt try to check the Sp currently

Prasad Godbole · Answer

Hi Santhosh, Thanks for posting this SPROC. There is difficulty that its not working on SQL 2000 due to changes in the system tables names. But this SPROC is very helpful to go ahead. We are looking more into this. Thanks Prasad