Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting unique or primary key columns for a given table Expand / Collapse
Author
Message
Posted Thursday, December 11, 2008 4:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 07, 2014 7:45 AM
Points: 232, Visits: 109
Comments posted to this topic are about the item Getting unique or primary key columns for a given table

Regards,

-Kiran

Post #617744
Posted Friday, January 02, 2009 2:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 24, Visits: 169
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
Posted Friday, January 02, 2009 8:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, January 02, 2009 8:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 9:47 AM
Points: 24, Visits: 169
Yes ...this code will only work with 2005/8 I think...
Post #628826
Posted Friday, January 02, 2009 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse