September 14, 2012 at 6:56 am
hello guys is there any script to find duplicate unique constraints ,there are more than one unique constraint in a table with different names but on same column
September 14, 2012 at 7:08 am
I don't have a script that does exactly what you require, but you could try querying the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view.
John
September 14, 2012 at 8:00 am
How it's possible to have more than one unique constraint on the same column? Obvoiusly, you should get an error, if I understand your question right....
Could you please provide your complete table creation script and post it here? (Script it from SQL mgmt studio)
September 14, 2012 at 8:26 am
bala.a (9/14/2012)
How it's possible to have more than one unique constraint on the same column? Obvoiusly, you should get an error, if I understand your question right....Could you please provide your complete table creation script and post it here? (Script it from SQL mgmt studio)
Example:
CREATE TABLE #john (col1 int)
ALTER TABLE #john ADD CONSTRAINT UQ_John UNIQUE (col1)
ALTER TABLE #john ADD CONSTRAINT UC_John UNIQUE (col1)
SELECT * FROM tempdb.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME LIKE '#john%'
John
September 14, 2012 at 8:32 am
Thanks John!
September 14, 2012 at 8:53 am
Hi Achtro,
Is this is the query you are looking for? If so, in the same query you can filter using HAVING clause to identify the columns which is having more than one unique key..
SELECT DISTINCT a.TABLE_NAME as [TableName], b.COLUMN_NAME, COUNT(a.TABLE_NAME) as [NoofUniqueCons]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b
ON a.TABLE_NAME = b.TABLE_NAME AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
WHERE a.TABLE_NAME = 'john'
AND a.CONSTRAINT_TYPE = 'UNIQUE'
GROUP BY a.TABLE_NAME, b.COLUMN_NAME
September 14, 2012 at 9:11 am
You might get misleading results if you have constraints that span more than one column:
CREATE TABLE #john2 (col1 int, col2 int, col3 int)
ALTER TABLE #john2 ADD CONSTRAINT UQ_John2 UNIQUE (col1,col2)
ALTER TABLE #john2 ADD CONSTRAINT UC_John2 UNIQUE (col1,col3)
SELECT DISTINCT a.TABLE_NAME as [TableName], b.COLUMN_NAME, COUNT(a.TABLE_NAME) as [NoofUniqueCons]
FROM tempdb.INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
INNER JOIN tempdb.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b
ON a.TABLE_NAME = b.TABLE_NAME AND a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
WHERE a.TABLE_NAME LIKE '#john2%'
AND a.CONSTRAINT_TYPE = 'UNIQUE'
GROUP BY a.TABLE_NAME, b.COLUMN_NAME
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply