Unanswered: How find out which tables have Unique indexes?
I am using Sql SERVER 2000 and I am doing some kind of schema comparisions and I would like to find out how would I be able to find which tables have which columns on which there are unique indexes?
I tried checking out the SYSINDEXES but theres no field which gives me that info.
I have also checked out sysobjects and checked out the XTYPE='UQ'
but that would give me the unique constraints but not the unique indexes.
Can you help?
any help in this direction is very well appreciated.
YES. Infact when you create a table using the Enterprise Manager you can go in the table design view and then click on creating new index and now under the unique option theres a check box for either a contraint or for a index.
If you chek the index it will create an index.
How can you find out all the unique indexes that were created by the system.
Originally posted by rnealejr
A Unique constraint automatically creates a unique index ... so are you looking for something else or more specific ?
Where do I find more info on the information_Schema views and their descriptions?
Secondly, Whats wierd is that it did not show me any rows with constraint type=Unique at all even though I know that there are tables that I have created lately which had the unique indexes?
I never knew about the information_Schema views. Thanx for sharig that piece of info with me.
Originally posted by Satya
Yes, I think INFORMATION_SCHEMA.TABLE_CONSTRAINTS is the one nearest and its best policy to query IS views rather than direclty to the system tables.
OK - Here are the results - use at your own risk. One of the problems with this is that ms does not guarantee the usuage of these columns - so it could change from service pack to service pack or between versions...
In the sysindexes table - look at the status field. The status field is a field that set bit values - each value has its own meaning.
4096 = unique constraint
2 = unique index (also set for a unique constraint)
1 = ignore duplicate key
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
inner join [INFORMATION_SCHEMA].[table_constraints] t
on u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
where t.[CONSTRAINT_TYPE] = 'UNIQUE'