(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
       *****************************************************