Getting unique or primary key columns for a given table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kiran Kumar » Getting unique or primary key columns for a...

Getting unique or primary key columns for a given table

Rate Topic

Display Mode

Topic Options

Author

Message

Kiran-339615

Posted Thursday, December 11, 2008 4:27 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, June 25, 2012 8:11 AM
Points: 232, Visits: 108

Comments posted to this topic are about the item Getting unique or primary key columns for a given table

Regards,

-Kiran

Post #617744

peterhails

Posted Friday, January 02, 2009 2:30 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 26, 2013 1:03 AM
Points: 23, Visits: 133

Thats great Kiran...

For interest I have modified this to just show Unique cols plus PK for the param table:

create PROCEDURE [dbo].[GetUniqueCols]
@table_name nvarchar(50)

AS

select
c.name as [Column_Name],
kc.name as [Constraint_Name],
object_name(c.object_id) as [Table_Name]
from sys.columns c
join sys.key_constraints kc
on (c.column_id = kc.unique_index_id and c.object_id = kc.parent_object_id)
where kc.type='UQ' or kc.type = 'PK'
and c.object_id = object_id(@table_name)
GROUP BY c.name,kc.NAME, object_name(c.object_id)
Having object_name(c.object_id)=@table_name

Post #628651

irene.floyd

Posted Friday, January 02, 2009 8:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 13, 2012 1:20 PM
Points: 3, Visits: 41

What version of SQL are you using?

I got these error messages when I tried your code in MS SQL 2000:

Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'sys.columns'.
Server: Msg 208, Level 16, State 1, Line 5
Invalid object name 'sys.key_constraints'.

Post #628812

peterhails

Posted Friday, January 02, 2009 8:17 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, April 26, 2013 1:03 AM
Points: 23, Visits: 133

Yes ...this code will only work with 2005/8 I think...

Post #628826

irene.floyd

Posted Friday, January 02, 2009 8:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, August 13, 2012 1:20 PM
Points: 3, Visits: 41

Kiran -
Do you have a version of your query for MS SQL 2000?

Post #628833

« Prev Topic | Next Topic »

Permissions