(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) https://esdbp.createaforum.com --------------------------------------------------------- ***************************************************** (DIR) Return to: Scripts ***************************************************** #Post#: 39-------------------------------------------------- Tables without clustered index in all user Databases By: srinivasma_exceldbp Date: April 6, 2014, 11:14 pm --------------------------------------------------------- --** Tables without cluster index --** All tables should have clustered index to avoid rowid look up declare @sql nvarchar(max) declare @tbl table ( id dbname tablename ) select @sql = ' select ''?'',ts.name from ?.sys.tables ts left join ?.sys.indexes si on ts.object_id = si.object_id and si.type = 1 inner join ?. sys.databases d on d.name=''?'' and d.name not in ( ''tempdb'',''master'',''msdb'',''reportserver'') where si.index_id is null ' /* Since this uses ? as place holder , variables can not be used in dynamic sql If it is required for a particular database or set of databases need to add where si.index_id is null and d.name in ( ''dbname1'', ''dbname2'') */ insert into @tbl execute sp_msforeachdb @sql select * from @tbl order by dbname,tablename go *****************************************************