Need to edge out the competition for your dream job? Train for certifications today.
Experts Exchange Solution brought to you by
"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.
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
Open in new window
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')
Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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'
where databasename not in ('master','msdb','model','tempdb')
and constrainttype not in ('primary key', 'unique')
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%''
INSERT INTO #tables
''?'' 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
ORDER BY db_name, schema_name, table_name
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment