Detalhes importantes de todas as bases

- Mostra alguns outros detalhes importantes, bem como: número de índices não clusterizados, existência de PK e CIX, a existência de colunas de texto ou imagem, a existência de índice de texto completo e triggers.

SET NOCOUNT ON
create table #tmp1 (TableName sysname, rows bigint, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50))
create table #tmp3 (TableName sysname, rows bigint, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50), DBName sysname )
create table #tmp2 (TableName sysname, HasAfterTrigger BIT, HasInsteadOfTrigger BIT, HasTextImage BIT, HasFulltextIndex BIT, HasPrimaryKey BIT, HasClustIndex BIT, DBName sysname, IndexCount INT)
DECLARE @DBName sysname
DECLARE @sql varchar(8000)
CREATE TABLE #dbs (DBName sysname PRIMARY KEY)
INSERT INTO #dbs (DBName)
SELECT name FROM master..sysdatabases WHERE name not in ('tempdb') and
status & 512 <> 512
DECLARE z CURSOR FOR
SELECT DBName FROM #dbs
open z
FETCH NEXT FROM z INTO @DBName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @sql = '
insert into #tmp1 (TableName, rows, reserved, data, index_size, unused) exec sp_spaceused ''?'';
insert into #tmp3 (TableName, rows, reserved, data, index_size, unused, DBName)
select ''?'', rows, reserved, data, index_size, unused, ''' + @DBName + '''
from #tmp1
truncate table #tmp1
insert into #tmp2
(TableName , HasAfterTrigger , HasInsteadOfTrigger , HasTextImage , HasFulltextIndex , HasPrimaryKey , HasClustIndex, DBName,IndexCount)
select ''?'' AS TableName , objectproperty(object_id(''?''),''HasAfterTrigger'')
,objectproperty(object_id(''?''),''HasInsteadOfTrigger'')
,objectproperty(object_id(''?''),''TableHasTextImage'')
,objectproperty(object_id(''?''),''TableHasActiveFulltextIndex'')
,objectproperty(object_id(''?''),''TableHasPrimaryKey'')
,objectproperty(object_id(''?''),''TableHasClustIndex'')
, ''' + @DBName + '''
,(SELECT count(indid) IndexCount FROM dbo.sysindexes
 WHERE dbo.sysindexes.indid between 2 and 254 AND INDEXPROPERTY(id,name, ''IsStatistics'')=0
AND id = object_id(''?''))
'
SELECT @sql = 'USE ' + QUOTENAME(@DBName) + ';
exec sp_msforeachtable ''' + REPLACE(@sql,'''','''''') + ''',''?'''
exec(@sql)
FETCH NEXT FROM z INTO @DBName
END
CLOSE z
DEALLOCATE z
SET NOCOUNT OFF
select @@ServerName AS ServerName, t0.DBName, t0.TableName, Rows, convert(int,left(reserved, len(reserved)-3)) AS Reserved,
convert(int,left(data, len(data)-3)) AS Data,
convert(int,left(Index_Size, len(Index_Size)-3)) AS Index_Size,
convert(int,left(Unused, len(Unused)-3)) AS Unused,
ISNULL(t1.IndexCount,0) AS NCIndexCount,
HasPrimaryKey, HasClustIndex,
HasTextImage, HasFulltextIndex,
HasAfterTrigger, HasInsteadofTrigger, GETDATE() AS ExecDate
from #tmp3 t0 inner join #tmp2 t1 on t0.TableName = t1.TableName AND t0.DBName = t1.DBName
ORDER BY convert(int,left(data, len(data)-3))+convert(int,left(Index_Size, len(Index_Size)-3)) DESC
drop table #tmp1
drop table #tmp2
drop table #tmp3
drop table #dbs