· 10 years ago · Sep 27, 2016, 07:46 PM
1/*******************************************************
2 Evaluation of storage taken up for all tables
3
4 This gathers all the storage details for tables.
5 This can help identify which tables might be the most heavily used and in need of the tender care of some optimization efforts
6
7 ------- HISTORY -------
8 9/27/2016 10:15 AM sheldonhull.com created... hope it helps
9*******************************************************/
10
11-- For SQLPROMPT USERS: $CURSOR$
12-- For SQLPROMPT USERS: use $DBNAME$;
13
14
15begin try
16
17 if object_id('tempdb..#StorageDetail') is not null
18 drop table #StorageDetail;
19
20
21 if object_id('tempdb..#results') is not null
22 drop table #results;
23
24 declare @Rows int = 0;
25 declare @AsOfDate datetime2(3) = getdate();
26
27 declare @Table_Name varchar(500);
28 declare @Schema_Name varchar(500);
29 declare @Tab1 table
30 (
31 tablename varchar(500)
32 ,schemaname varchar(500)
33 );
34
35 ------------------------- create temp table to hold the values till after script runs changes -------------------------
36
37 create table #results
38 (
39 tablename sysname
40 ,row_count varchar(50)
41 ,reserved varchar(50)
42 ,data varchar(50)
43 ,index_size varchar(50)
44 ,unused varchar(50)
45 );
46
47
48
49 insert into @Tab1
50 select
51 t1.name
52 ,t2.name
53 from
54 sys.tables t1
55
56 inner join sys.schemas t2
57 on (t1.schema_id = t2.schema_id)
58
59 where
60 not exists (
61 -- for simplicity exclude any tables that have same name, but multiple schemas
62 select
63 name
64 ,count(*)
65 from
66 sys.tables
67 group by
68 name
69 having
70 count(*) > 1
71
72 );
73
74 declare c1 cursor for
75 select
76 replace(replace(t2.name + '.' + t1.name, '[', ''), ']', '')
77 from
78 sys.tables t1
79 inner join sys.schemas t2
80 on (t1.schema_id = t2.schema_id);
81
82 open c1;
83 fetch next from c1 into @Table_Name;
84 while @@fetch_status = 0
85 begin
86 declare @ParMdefinition nvarchar(100) = N'@Table_name sysname';
87
88
89 declare @Xsql nvarchar(max) = N' set nocount on;
90 insert into #results exec sp_spaceused @Table_name,false;
91 ';
92 exec sys.sp_executesql @Xsql
93 ,@ParMdefinition
94 ,@Table_Name = @Table_Name;
95 fetch next from c1 into @Table_Name;
96 end;
97 close c1;
98 deallocate c1;
99
100 /*******************************************************
101 UPDATE OBJECT ID TO MAKE LOOKUP OF TABLE EASIER FOR ANALYSIS
102 *******************************************************/
103 alter table #results add object_id int null;
104
105 update a
106 set object_id = o.[object_id]
107 from
108 #results as a
109
110 inner join sys.tables as o
111 on a.tablename collate database_default = o.name -- in case of a variance in collation from tempdb to the database being evaluated
112 and o.type = 'U'
113 where
114 a.[object_id] is null;
115
116 -- just doing some cleanup to take the text strings and put them as decimal values
117 select
118 b.tablename
119 ,row_count = cast(b.row_count as int)
120 ,reserved_mb = cast(replace(b.reserved, 'KB', '') as decimal(18, 2)) / 1024
121 ,data_mb = cast(replace(b.data, 'KB', '') as decimal(18, 2)) / 1024
122 ,index_size_mb = cast(replace(b.index_size, 'KB', '') as decimal(18, 2)) / 1024
123 ,unused_mb = cast(replace(b.unused, 'KB', '') as decimal(18, 2)) / 1024
124 ,total_actual_size = cast(replace(b.data, 'KB', '') as decimal(18, 2)) / 1024 + cast(replace(b.index_size, 'KB', '') as decimal(18, 2)) / 1024
125 into #StorageDetail
126 from
127 #results as b;
128 -- where -- OPTIONAL: if you want to filter out some tables from the final results as a "blacklist" you can do this here
129 -- b.tablename not like '%';
130
131 set @Rows += @@rowcount;
132
133end try
134begin catch
135 declare @ErrorMsg varchar(max) = isnull(error_procedure()+ '; ', '') + 'Error Line #: ' + convert(varchar(10), error_line()) + '; Error Message: ' + error_message();
136 declare @ErrorDate smalldatetime = getdate();
137 declare @ErrorSeverity int = error_severity();
138 declare @ErrorState int = error_state();
139 declare @ErrorLineNumber int = error_line();
140 declare @ErrorNumber int = error_number();
141
142
143 raiserror (
144 @ErrorMsg
145 , @ErrorSeverity
146 , @ErrorState
147 , @ErrorLineNumber
148 , @ErrorNumber
149 );
150
151
152end catch;
153
154
155
156/*******************************************************
157 review results
158*******************************************************/
159select
160 R.tablename
161 ,R.row_count
162 ,R.reserved_mb
163 ,R.data_mb
164 ,R.index_size_mb
165 ,R.unused_mb
166 ,R.total_actual_size
167from
168 #StorageDetail as R
169order by
170 total_actual_size desc;