1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92
| -- surveillance des bases de données : donne le nombre d'enregistrements par tables
CREATE TABLE #TableCount ( table_schema varchar(250), table_name varchar(250), card int,
ReservedSize_ko int, DataSize_ko int, IndexSize_ko int, UnusedSize_ko int
PRIMARY KEY ( table_schema , table_name ) )
CREATE TABLE #spaceused_tab
( tableName varchar(100), numberofRows varchar(100), reservedSize varchar(50),
dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50) )
declare @sql2 nvarchar(1500)
declare @table_schema varchar(255)
declare @table_name varchar(255)
DECLARE db_cursor CURSOR FOR
select
REQ2 = 'INSERT #spaceused_tab EXEC sp_spaceused ''' + QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) + ''''
, table_schema
, table_name
from information_schema.tables
where table_type = 'BASE TABLE'
--and TABLE_NAME = 'archive_fix_charges'
FOR READ ONLY
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @sql2, @table_schema, @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE table #spaceused_tab
exec sp_executesql @sql2
insert #TableCount
select @table_schema, @table_name ,
numberofRows,
reverse( substring( reverse( reservedSize ) , 4 , 999)),
reverse( substring( reverse( dataSize ) , 4 , 999)),
reverse( substring( reverse( indexSize ) , 4 , 999)),
reverse( substring( reverse( unusedSize ) , 4 , 999))
from #spaceused_tab
FETCH NEXT FROM db_cursor INTO @sql2, @table_schema, @table_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
; WITH SR_col as (
select COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COUNT(*) nb_colonnes
from INFORMATION_SCHEMA.COLUMNS COL with (nolock)
group by COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME
) , SR_Ind AS (
select OBJECT_NAME(IND.object_id) name
, OBJECT_SCHEMA_NAME(IND.object_id) schema_name
, IND.object_id, COUNT(*) nb_indexes
from SYS.indexes IND with (nolock)
where IND.index_id > 0
group by IND.object_id
)
SELECT TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME
, max(SR_col.nb_colonnes) nb_colonnes
, MAX(case when SR_ind.nb_indexes is null then 0 else SR_ind.nb_indexes end) nb_indexes
, sum(case when CNT.CONSTRAINT_TYPE = 'PRIMARY KEY' then 1 else 0 end) PK
, sum(case when CNT.CONSTRAINT_TYPE = 'UNIQUE' then 1 else 0 end) UK
, sum(case when CNT.CONSTRAINT_TYPE = 'FOREIGN KEY' then 1 else 0 end) FK
, sum(case when CNT.CONSTRAINT_TYPE = 'CHECK' then 1 else 0 end) CHK
, max(TBC.card) nb_lignes
, cast(max(TBC.ReservedSize_ko)/1024. as decimal(12,3)) ReservedSize_Mo
, cast(max(TBC.DataSize_ko)/1024. as decimal(12,3)) DataSize_Mo
, cast(max(TBC.IndexSize_ko)/1024. as decimal(12,3)) IndexSize_Mo
, cast(max(TBC.UnusedSize_ko)/1024. as decimal(12,3)) UnusedSize_Mo
FROM information_schema.tables TAB with (nolock)
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS CNT with (nolock)
on CNT.CONSTRAINT_CATALOG = TAB.TABLE_CATALOG
and CNT.CONSTRAINT_SCHEMA = TAB.TABLE_SCHEMA
and CNT.TABLE_NAME = TAB.TABLE_NAME
LEFT OUTER JOIN SR_col with (nolock)
on SR_col.TABLE_CATALOG = TAB.TABLE_CATALOG
and SR_col.TABLE_SCHEMA = TAB.TABLE_SCHEMA
and SR_col.TABLE_NAME = TAB.TABLE_NAME
LEFT OUTER JOIN SR_Ind with (nolock)
ON SR_Ind.schema_name = TAB.TABLE_SCHEMA
and SR_Ind.name = TAB.TABLE_NAME
left outer join #TableCount TBC with (nolock)--order by card desc , table_schema , table_name
on TBC.table_schema = TAB.TABLE_SCHEMA COLLATE database_default
and TBC.table_name = TAB.TABLE_NAME COLLATE database_default
WHERE TAB.table_type='BASE TABLE'
and TAB.TABLE_NAME not in ( 'dtproperties' , 'sysdiagrams' )
group by TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME
order by ReservedSize_Mo desc, TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME
drop TABLE #TableCount
drop TABLE #spaceused_tab
GO |
Partager