how to check any table do not have unique constraint.

HI all,

any way to check any tables do not have unique constraint and therefore no unique index to control data?
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

marrowyungSenior Technical architecture (Data)Author Commented:

sorry one more thing, any way to check any tables do not have clustered index and/or unique constraint ?
Jim HornMicrosoft SQL Server Data DudeCommented:
Give this a whirl..
SELECT as table_name, as index_name, i.is_unique
FROM sys.tables t
	JOIN sys.indexes i ON t.object_id = i.object_id
	JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.is_unique_constraint = 1

Open in new window

Vikas GargAssociate Principal EngineerCommented:

This query will help you

Select  SysObjects.[Name] As [Constraint Name] ,SysObjects.xtype,
        Tab.[Name] as [Table Name],
        Col.[Name] As [Column Name]
From SysObjects Inner Join 
(Select [Name],[ID] From SysObjects) As Tab
On Tab.[ID] = Sysobjects.[Parent_Obj] 
Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID] 
Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]
WHERE SysObjects.xtype NOT IN ('C','U')

Open in new window

This will give you the tables which does not have
C - Clustered Index
U - Unique Index
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

marrowyungSenior Technical architecture (Data)Author Commented:
marrowyungSenior Technical architecture (Data)Author Commented:
Jim Horn,

that one give me nothing back, I am not sure if this is normal.


the script don't show me which DB the table belongs to .
Vikas GargAssociate Principal EngineerCommented:

You have to run the query to the particular database and it will give result for that database only.

So no need to get confused about of which database the table belongs
Mark WillsTopic AdvisorCommented:
Here's one that is based on one of your other questions (think it was yours) quite a while back :)

I have simply added a bit more to the final select.

if object_id('tempdb..#tables','U') is not null drop table #tables
create table #tables  (id int identity, databasename nvarchar(128), tablename nvarchar(200), tableschema nvarchar(200), tabletype nvarchar(200), constrainttype nvarchar(200), constraintname nvarchar(200))

insert into #tables
exec sp_msforeachdb '
select * from
SELECT   T.TABLE_CATALOG as databasename
       , T.TABLE_NAME as tablename
       , T.TABLE_SCHEMA as tableschema
       , T.TABLE_TYPE as tabletype
       , C.CONSTRAINT_TYPE as constrainttype
       , C.CONSTRAINT_NAME as constraintname
     on  T.table_catalog = C.table_catalog
     and T.table_schema = C.table_schema
     and  T.table_name = C.table_Name
where t.table_type like ''%table%''
) as dblist'

select *
from #tables
where databasename not in ('master','msdb','model','tempdb')
and constrainttype not in ('primary key', 'unique')

Open in new window

You might need to exclude reportservers etc...

Hopefully you remember the foreachdb system (undocumented) routine. It is one that MS uses themselves in a couple of their routines.

edit : sorry, wasn't your previous question. has a different reference in script. But script is correct anyway :)
Scott PletcherSenior DBACommented:
You can't look just row by row at the constraint type, you have to look at the table overall.  I wasn't sure of exactly what conditions you wanted, so just change the WHERE clause below to match what you want to see.  

For example:

IF OBJECT_ID('tempdb.dbo.#tables') IS NOT NULL
    DROP TABLE #tables
CREATE TABLE #tables (
    db_name nvarchar(128) NULL,
    schema_name nvarchar(128) NULL,
    table_name nvarchar(128) NULL,
    clustered_index varchar(30) NULL,
    count_of_unique_indexes smallint NULL

EXEC sp_MSforeachdb '
IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'')
OR ''?'' LIKE ''ReportServer%''
USE [?]
        ''?'' AS db_name,
        SCHEMA_NAME(o.schema_id) AS schema_name,
        OBJECT_NAME(i.object_id) AS table_name,    
        MAX(CASE WHEN i.index_id = 0 THEN ''None''
                 WHEN i.index_id = 1 THEN CASE WHEN i.is_unique = 0 THEN ''Nonunique'' ELSE ''Unique'' END
                 END) AS clustered_index,
        SUM(CASE WHEN i.is_unique = 1 THEN 1 ELSE 0 END) AS count_of_unique_indexes
    FROM sys.indexes i
    INNER JOIN sys.objects o ON o.object_id = i.object_id AND o.is_ms_shipped = 0
    GROUP BY o.schema_id, i.object_id
) AS derived
WHERE count_of_unique_indexes = 0 --OR clustered_index = ''None'' 
ORDER BY schema_name, table_name

FROM #tables
ORDER BY db_name, schema_name, table_name

Open in new window

marrowyungSenior Technical architecture (Data)Author Commented:
just because I heard that if the table do not have any clustered key/index, uniqure constraint on it, no matter what non-clustered index add to the table, the access can't be too fast!

so I would like to filter out which one DO NOT have these and have developer focus on defining it back, this is my plan.
Mark WillsTopic AdvisorCommented:
Well, the theory would suggest a primary clustered index and play with fill factor if non-sequential identity / guid (ie a surrogate key).

If a natural key, then clustered doesn't always mean performance improvement, and can lead to fragmentation.

So, there is a lot more to index design and performance other than having a clustered key / index, unique constraint.
Scott PletcherSenior DBACommented:
What you've heard is false.  It depends on how the table is accessed.  For some tables, a non-unique clustered index is perfectly fine.

But, keep in mind, it is extraordinarily rare that a table should ever have a nonclustered index and not ha clustered index.  I would certainly review any tables with that condition.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
Mark Wills,

"Hopefully you remember the foreachdb system (undocumented) routine. It is one that MS uses themselves in a couple of their routines."

yeah, I keep seeing this. tks.

I add drop table #tables at the end.


"For some tables, a non-unique clustered index is perfectly fine.
if nothing can be done. yes!

"What you've heard is false. "

I am not sure why, it seems once the table is defined and system run for a while, it seems we can only create non-clustered index, by the missing index suggestion to improve the performance of a query.

I will also create trace of the system and give it to DTA for suggestion.

" it is extraordinarily rare that a table should ever have a nonclustered index and not ha clustered index."

sorry what is that mean ?
marrowyungSenior Technical architecture (Data)Author Commented:

"But, keep in mind, it is extraordinarily rare that a table should ever have a nonclustered index and not ha clustered index.  I would certainly review any tables with that condition. "

finally question here is, you mean by this is it will be abnormal for a table to have only nonclustered index but no clustered index, right?
Scott PletcherSenior DBACommented:
Yes, exactly.  Since the clustered index is the table itself, it doesn't really cost you any space to make the first index a clustered index rather a nonclustered one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.