how to check any table do not have unique constraint.

hi,

sorry one more thing, any way to check any tables do not have clustered index and/or unique constraint ?

Give this a whirl..

SELECT t.name as table_name, i.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

Select allOpen in new window

Hi,

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')

Select allOpen in new window

This will give you the tables which does not have
C - Clustered Index
U - Unique Index

Vikas,

tks for that, please help to answer this.

https://www.experts-exchange.com/questions/28735598/log-shipping-and-SSIS-job-monitoring.html

Jim Horn,

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

Vikas,

the script don't show me which DB the table belongs to .

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
FROM [?].INFORMATION_SCHEMA.TABLES T
LEFT OUTER JOIN [?].INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
     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')

Select allOpen 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 :)

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%''
    RETURN
USE [?]
INSERT INTO #tables
SELECT *
FROM (
    SELECT 
        ''?'' 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
'

SELECT *
FROM #tables
ORDER BY db_name, schema_name, table_name

Select allOpen in new window

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 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.

ScottPletcher,

"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 ?

ScottPletcher,

"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?

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.