Unanswered: Urgent : How to find whih table has unique index
I have many databses and we are trying to see how many can be replicated. Found out 90 on't have primary keys.
Next option, find unique indexed tables and convert them to PKeys. Now my question is how do I fin that a table has a unique index and column has "allow null" Please remember I am not asking to find Unique constraint.
In other words , folowing query
select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('UNIQUE') won't tell you if a table has a unique index.
SQL Server automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, SQL Server returns an error message that says the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.
drop table test
create table test(id int unique)
from sysindexes sy
join sysobjects so on so.id=sy.id and so.name='test'
where sy.indid not in(0,255)
select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('UNIQUE')
Let me rephrase this. Is there an SQL which gives me name of table which have either a UNIQUE INDEX or UNIQUE CONSTRAINT. Problem is if you look in Table_constraints VIEW , it won't list table name which has a UNIQUE INDEX and will only list a table which has UNIQUE_CONSTRAINT. Hope that explains what I am looking for. Thanks