Getting unique or primary key columns for a given table

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

peterhails SSC Enthusiast Points: 128 More actions · Answer 1

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

irene.floyd SSC Journeyman Points: 99 More actions · Answer 2

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'.

peterhails SSC Enthusiast Points: 128 More actions · Answer 3

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

irene.floyd SSC Journeyman Points: 99 More actions · Answer 4

Kiran -

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