June 23, 2005 at 12:01 am
Hi,
I have a situation where I need to identify if a unique constraint exists on a table and if it does, then the names of the columns on which the unique constraint exists in that table.
Please let me know how we do this in SQLServer2000.
Thanks
ourspt
June 23, 2005 at 1:37 am
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t1
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2
ON t1.TABLE_NAME=t2.TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID(t1.CONSTRAINT_NAME),'IsUniqueCnst')=1
AND t1.TABLE_NAME='...'
should do.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 23, 2005 at 4:06 am
Frank,
Thanks for the reply. But the query does not seem to work. It seems to be returning only primary and foreign keys. Are you sure it is correct?
Thanks
ourspt
June 23, 2005 at 4:16 am
CREATE TABLE test
(
idx INT
, idx2 INT
CONSTRAINT ux_test UNIQUE(idx, idx2)
)
INSERT INTO test SELECT 1, 1
INSERT INTO test SELECT 1, 1
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t1
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t2
ON t1.TABLE_NAME=t2.TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID(t1.CONSTRAINT_NAME),'IsUniqueCnst')=1
DROP TABLE test
seems to work fine for me. Including the error message during the INSERT.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply