SQL - scripts DBA

/*query DBAs */ --================================================================================= /*Consulta Tamanho das Tabelas*/ SELECT CASE when t.NAME = '/BIC/FZSD_C23' then 'WS - Vendas Integradas Histórico' when t.NAME = '/BIC/VZSD_C23F' then 'FACTVIEW - INFOCUBE ZSD_C23 ' ELSE '???' END as ETL, t.NAME AS Entidade, COUNT(DISTINCT(p.partition_id)) AS partition_id, SUM(DISTINCT(p.rows)) AS Registros, COUNT(DISTINCT(c.column_id)) AS Qtd_Cols, SUM(a.total_pages) * 8 AS EspacoTotalKB, SUM(a.used_pages) * 8 AS EspacoUsadoKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS EspacoNaoUsadoKB, MAX(t.modify_date) AS Ultima_Atualizacao, MAX(t.create_date) AS Ultima_Atualizacao2 FROM sys.tables t INNER JOIN sys.all_columns c ON t.OBJECT_ID = c.OBJECT_ID INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE upper(t.NAME) LIKE upper( '%logy%' ) --t.NAME NOT LIKE 'xpto%' and --t.is_ms_shipped = 0 and --i.OBJECT_ID > 255 GROUP BY t.Name -- ,p.partition_id ORDER BY Ultima_Atualizacao DESC , 4 desc --Registros DESC ; --================================================================================= /*Consulta Colunas das Tabelas like*/ SELECT distinct t.NAME AS Entidade, c.Name AS Coluna , c.max_length, c.precision, c.object_id FROM sys.tables t INNER JOIN --select * from sys.all_columns c ON t.OBJECT_ID = c.OBJECT_ID INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE upper(c.NAME) LIKE upper( '%logy%' ) --t.NAME NOT LIKE 'xpto%' and --t.is_ms_shipped = 0 and --i.OBJECT_ID > 255 order by 1,2 --================================================================================= /*Consulta Global Variables select @@VERSION ; select @@SERVERNAME ; select @@CONNECTIONS ; select @@MAX_CONNECTIONS ; select @@CPU_BUSY ; select @@ERROR ; select @@IDENTITY ; select @@IDLE ; select @@IO_BUSY ; select @@LANGID ; select @@LANGUAGE ; --select @@MAXCHARLEN ; select @@PACK_RECEIVED ; select @@PACK_SENT ; select @@PACKET_ERRORS ; select @@ROWCOUNT ; select @@SPID ; select @@TEXTSIZE ; select @@TIMETICKS ; select @@TOTAL_ERRORS ; select @@TOTAL_READ / @@TOTAL_WRITE ; select @@TRANCOUNT ; */