December 11, 2008 at 4:27 am
Comments posted to this topic are about the item Getting unique or primary key columns for a given table
Regards,
-Kiran
January 2, 2009 at 2:30 am
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
January 2, 2009 at 8:00 am
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'.
January 2, 2009 at 8:17 am
Yes ...this code will only work with 2005/8 I think...
January 2, 2009 at 8:25 am
Kiran -
Do you have a version of your query for MS SQL 2000?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy