How to tell if a column has a unique constraint? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to tell if a column has a unique constraint?

Author

Topic

codism
Starting Member

11 Posts

Posted - 2007-04-04 : 13:07:18

I am writing a script that automatically scans all user tables and changes all nvarchar, nchar, ntext field to their corresponding non-unicode version. For any column with constraints, the constraints have to be removed before changing the column itself and then rebuilt after changing the column. Now I cannot figure out a way to tell if a column has a unique constraint. I do know a unique constraint has a corresponding index but I don't know how to tell if a unique index is being used by a unique constraint.

Any help or suggestion would be deeply appreciated!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-04-04 : 13:43:51

This query will list all of the columns in the current database that have unique constraints on them.

select TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where OBJECTPROPERTY( OBJECT_ID(CONSTRAINT_NAME),'IsUniqueCnst') = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources