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