This repository was archived by the owner on Jan 7, 2026. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 4
Expand file tree
/
Copy pathManagement.GatherTableStatisticsps.sql
More file actions
93 lines (80 loc) · 2.92 KB
/
Management.GatherTableStatisticsps.sql
File metadata and controls
93 lines (80 loc) · 2.92 KB
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
93
set nocount on;
if exists(select * from tempdb.dbo.sysobjects where name = '##TableStats')
drop table ##TableStats;
Create table ##TableStats
(
ServerName varchar(255)
,DBName varchar(255)
,SchemaName nvarchar(128)
,TableName nvarchar(128)
,RowCounts numeric(38,0)
,ReservedKB numeric(38,0)
,DataKB numeric(38,0)
,IndexSizeKB numeric(38,0)
,UnusedKB numeric(38,0)
,RecordedDateTime datetime
);
DECLARE @table_name VARCHAR(128);
DECLARE @servername VARCHAR(256);
DECLARE @dbname VARCHAR(256);
DECLARE @cmd VARCHAR(8000);
CREATE TABLE #dbnames
(
name NVARCHAR(128)
);
SET @servername = Cast(Serverproperty('servername') AS VARCHAR(256));
insert into #dbnames
select name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ( 'master', 'msdb', 'model', 'pubs',
'northwind', 'tempdb' );
DECLARE db CURSOR FAST_FORWARD FOR
SELECT
name
FROM
#dbnames
OPEN db
FETCH NEXT FROM db INTO @dbname
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( @@FETCH_STATUS <> -2 )
BEGIN
IF (SELECT
CONVERT(SYSNAME, Databasepropertyex(@dbname, 'status'))) = 'ONLINE'
BEGIN
set @cmd = '
insert into ##TableStats
SELECT
'''+@servername+''' AS ServerName,
'''+@dbname+''' AS DBName,
Object_schema_name(object_id,DB_ID('''+@dbname+''')) AS SchemaName,
Object_name(object_id,DB_ID('''+@dbname+''')) AS TableName,
Sum(CASE
WHEN index_id < 2 THEN row_count
ELSE 0
END) AS RowCounts,
Sum(reserved_page_count) * 8 AS ReservedKB,
Sum(CASE
WHEN index_id < 2 THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
ELSE lob_used_page_count + row_overflow_used_page_count
END) * 8 AS DataKB,
( Sum(used_page_count) - Sum(CASE
WHEN index_id < 2 THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
ELSE lob_used_page_count + row_overflow_used_page_count
END) ) * 8 AS IndexSizeKB,
Sum(reserved_page_count - used_page_count) * 8 AS UnusedKB,
Getdate() AS RecordedDateTime
FROM
['+@dbname+'].sys.dm_db_partition_stats
WHERE
object_schema_name(object_id,DB_ID('''+@dbname+''')) <> ''sys''
GROUP BY object_id'
exec(@cmd);
END
END
FETCH NEXT FROM db INTO @dbname
END
CLOSE db
DEALLOCATE db
DROP TABLE #dbnames;
select * from ##TableStats;