SQL Server Unique/NonUnique Key
-
Hi, To get the unique keys from a given table, i use a sql statement like this:
Select [COLUMN_NAME] as FieldName, [CONSTRAINT_NAME] as KeyName, [ORDINAL_POSITION] as Ordinal FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] WHERE TABLE_SCHEMA=@schema AND TABLE_NAME=@table AND CONSTRAINT_NAME LIKE "IX_%" ORDER BY [CONSTRAINT_NAME], [ORDINAL_POSITION]First of all, it waorks fine, but I don't like the idea of selecting it by the name, but it was the only solution I found... For now it works, so if it's the only way, I'll live with it ;) My main problem is that I didn't find any [INFORMATION_SCHEMA] table where the NonUnique keys are stored, but it has to be somewhere, right? Any ideas about it?Thanks, Dirso
-
Hi, To get the unique keys from a given table, i use a sql statement like this:
Select [COLUMN_NAME] as FieldName, [CONSTRAINT_NAME] as KeyName, [ORDINAL_POSITION] as Ordinal FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] WHERE TABLE_SCHEMA=@schema AND TABLE_NAME=@table AND CONSTRAINT_NAME LIKE "IX_%" ORDER BY [CONSTRAINT_NAME], [ORDINAL_POSITION]First of all, it waorks fine, but I don't like the idea of selecting it by the name, but it was the only solution I found... For now it works, so if it's the only way, I'll live with it ;) My main problem is that I didn't find any [INFORMATION_SCHEMA] table where the NonUnique keys are stored, but it has to be somewhere, right? Any ideas about it?Thanks, Dirso
I use the following against the sysobjects table (it predates the views) it is designed the get the IDENTITY column which for me is always the primary key for a table. It may help!
select syscolumns.name from syscolumns INNER JOIN sysobjects on syscolumns.ID = sysobjects.ID
where sysobjects.name = 'TableName' and colstat & 1 = 1Fixed that univote - this is a valid concern and you are right in not wanting to rely on a constrain name.
Never underestimate the power of human stupidity RAH
-
Hi, To get the unique keys from a given table, i use a sql statement like this:
Select [COLUMN_NAME] as FieldName, [CONSTRAINT_NAME] as KeyName, [ORDINAL_POSITION] as Ordinal FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] WHERE TABLE_SCHEMA=@schema AND TABLE_NAME=@table AND CONSTRAINT_NAME LIKE "IX_%" ORDER BY [CONSTRAINT_NAME], [ORDINAL_POSITION]First of all, it waorks fine, but I don't like the idea of selecting it by the name, but it was the only solution I found... For now it works, so if it's the only way, I'll live with it ;) My main problem is that I didn't find any [INFORMATION_SCHEMA] table where the NonUnique keys are stored, but it has to be somewhere, right? Any ideas about it?Thanks, Dirso
-
Hi, To get the unique keys from a given table, i use a sql statement like this:
Select [COLUMN_NAME] as FieldName, [CONSTRAINT_NAME] as KeyName, [ORDINAL_POSITION] as Ordinal FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] WHERE TABLE_SCHEMA=@schema AND TABLE_NAME=@table AND CONSTRAINT_NAME LIKE "IX_%" ORDER BY [CONSTRAINT_NAME], [ORDINAL_POSITION]First of all, it waorks fine, but I don't like the idea of selecting it by the name, but it was the only solution I found... For now it works, so if it's the only way, I'll live with it ;) My main problem is that I didn't find any [INFORMATION_SCHEMA] table where the NonUnique keys are stored, but it has to be somewhere, right? Any ideas about it?Thanks, Dirso