· 8 years ago · Aug 31, 2018, 11:18 AM
1sp_spaceused 'tblBOrderRelationship'
2go
3
4select 318008/1024.00 AS reserved,
5140208/1024.00 AS data,
6177048/1024.00 AS index_size,
7752/1024.00 AS unused
8
9SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
10
11SELECT
12 schema_name(t.schema_id) as SchemaName,
13 t.NAME AS TableName,
14 t.type_desc,
15 t.is_ms_shipped,
16 t.is_published,
17 t.lob_data_space_id,
18 t.filestream_data_space_id,
19 t.is_replicated,
20 t.has_replication_filter,
21 t.is_merge_published,
22 t.is_sync_tran_subscribed,
23 --t.is_filetable,
24 i.name as indexName,
25 i.type_desc,
26 i.is_unique,
27 i.is_primary_key,
28 i.is_unique_constraint,
29 i.fill_factor,
30 i.is_padded,
31
32
33 sum(p.rows) OVER (PARTITION BY t.OBJECT_ID,i.index_id) as RowCounts,
34 sum(a.total_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) as TotalPages,
35 sum(a.used_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) as UsedPages,
36 sum(a.data_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) as DataPages,
37
38 (sum(a.total_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 8) / 1024 as TotalSpaceMB,
39 (sum(a.used_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 8) / 1024 as UsedSpaceMB,
40 (sum(a.data_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 8) / 1024 as DataSpaceMB
41FROM
42 sys.tables t
43INNER JOIN
44 sys.indexes i ON t.OBJECT_ID = i.object_id
45INNER JOIN
46 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
47INNER JOIN
48 sys.allocation_units a ON p.partition_id = a.container_id
49WHERE
50 t.NAME NOT LIKE 'dt%' AND
51 i.OBJECT_ID > 255
52AND T.NAME = 'tblBOrderRelationship'
53
54--==================================
55-- the figures from sp_spaceused
56--==================================
57select 318008/1024.00 AS reserved,
58140208/1024.00 AS data,
59177048/1024.00 AS index_size,
60752/1024.00 AS unused
61
62--==================================
63-- the figures from my select
64--==================================
65select 137+61+56+54 AS reserved,
66 137 AS data,
67 61+56+54 AS index_size
68
69--==================================
70-- the figures from sp_spaceused
71--==================================
72select
73318072 /1024.00 AS reserved,
74140208 /1024.00 AS data,
75177096 /1024.00 AS index_size,
76768 /1024.00 AS unused
77go
78
79--==================================
80-- the figures from my select
81--==================================
82select 137.7578125+61.7968750+56.4218750+54.6406250 as reserved,
83 137.7578125 as data,
84 61.7968750+56.4218750+54.6406250 as index_size
85
86(sum(a.total_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 8) / 1024.00 as TotalSpaceMB,
87(sum(a.used_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 8) / 1024.00 as UsedSpaceMB,
88(sum(a.data_pages) OVER (PARTITION BY t.OBJECT_ID,i.index_id) * 8) / 1024.00 as DataSpaceMB
89
90/* one line per table for all databases on the server */
91DROP TABLE IF EXISTS #h
92
93CREATE TABLE #h (dbname sysname NOT NULL, TableName sysname NULL, UsedMB INT NOT NULL, rowcnt BIGINT NOT NULL, FilegroupName sysname)
94
95
96EXEC sp_foreachdb ';WITH extra AS
97( -- Get info for FullText indexes, XML Indexes, etc
98 SELECT sit.name,
99 sit.[object_id],
100 sit.[parent_id],
101 ps.[index_id],
102 sit.internal_type_desc,
103 SUM(ps.reserved_page_count) AS [reserved_page_count],
104 SUM(ps.used_page_count) AS [used_page_count]
105 FROM ?.sys.dm_db_partition_stats ps WITH (NOLOCK)
106 INNER JOIN ?.sys.internal_tables sit WITH (NOLOCK) ON sit.[object_id] = ps.[object_id]
107 WHERE sit.internal_type IN
108 (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
109 GROUP BY sit.name,
110 sit.[object_id],
111 sit.[parent_id],
112 ps.[index_id],
113 sit.internal_type_desc
114), agg AS
115( -- Get info for Tables, Indexed Views, etc (including "extra")
116 SELECT ps.[object_id] AS [ObjectID],
117 ps.index_id AS [IndexID],
118 NULL AS [PassThroughIndexName],
119 NULL AS [PassThroughIndexType],
120 SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
121 SUM(ps.used_page_count) AS [UsedPageCount],
122 SUM(ps.reserved_page_count) AS [ReservedPageCount],
123 SUM(ps.row_count) AS [RowCount],
124 SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
125 AS [LobAndRowOverflowUsedPageCount]
126 FROM ?.sys.dm_db_partition_stats ps WITH (NOLOCK)
127 GROUP BY ps.[object_id],
128 ps.[index_id]
129 UNION ALL
130 SELECT ex.[parent_id] AS [ObjectID],
131 ex.[object_id] AS [IndexID],
132 ex.[name] AS [PassThroughIndexName],
133 ex.[internal_type_desc] AS [PassThroughIndexType],
134 0 AS [InRowDataPageCount],
135 SUM(ex.used_page_count) AS [UsedPageCount],
136 SUM(ex.reserved_page_count) AS [ReservedPageCount],
137 0 AS [RowCount],
138 0 AS [LobAndRowOverflowUsedPageCount]
139 FROM extra ex
140 GROUP BY ex.[parent_id],
141 ex.[object_id],
142 ex.[name],
143 ex.[internal_type_desc]
144), spaceused AS
145(
146SELECT agg.[ObjectID],
147 agg.[IndexID],
148 agg.[PassThroughIndexName],
149 agg.[PassThroughIndexType],
150 --OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
151 --OBJECT_NAME(agg.[ObjectID]) AS [TableName],
152 s.name as [SchemaName],
153 t.name AS [TableName],
154 SUM(CASE
155 WHEN (agg.IndexID < 2) THEN agg.[RowCount]
156 ELSE 0
157 END) AS [Rows],
158 SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
159 SUM(agg.LobAndRowOverflowUsedPageCount +
160 CASE
161 WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
162 ELSE 0
163 END) * 8 AS [DataKB],
164 SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
165 CASE
166 WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
167 ELSE 0
168 END) * 8 AS [IndexKB],
169 SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
170 SUM(agg.UsedPageCount) * 8 AS [UsedKB]
171FROM agg
172LEFT join ?.sys.tables t on agg.ObjectID= t.Object_ID
173LEFT join ?.sys.schemas s on t.schema_id=s.Schema_id
174GROUP BY agg.[ObjectID],
175 agg.[IndexID],
176 agg.[PassThroughIndexName],
177 agg.[PassThroughIndexType],
178 --OBJECT_SCHEMA_NAME(agg.[ObjectID]),
179 --OBJECT_NAME(agg.[ObjectID])
180 s.name,
181 t.name
182)
183INSERT INTO #h
184SELECT ''?'' as dbname, sp.SchemaName + ''.'' + sp.TableName AS TableName
185 , CAST(SUM(sp.UsedKB / 1024.0 ) AS DECIMAL(18,0)) AS [UsedMB]
186 , SUM(sp.Rows) AS rowcnt
187 , MAX(f.name) AS FilegroupName
188FROM spaceused sp
189INNER JOIN ?.sys.objects so WITH (NOLOCK) ON so.[object_id] = sp.ObjectID
190LEFT JOIN ?.sys.indexes si WITH (NOLOCK) ON si.[object_id] = sp.ObjectID AND si.[index_id] = sp.IndexID
191OUTER APPLY (
192 SELECT MAX(fg.name) AS Name
193 FROM ?.sys.partitions P
194 LEFT JOIN ?.sys.allocation_units au ON au.container_id = p.hobt_id
195 LEFT JOIN ?.sys.filegroups fg ON fg.data_space_id = au.data_space_id
196 WHERE
197 P.object_id = si.object_id AND P.index_id = si.index_id
198) AS F
199WHERE so.is_ms_shipped = 0
200GROUP BY sp.SchemaName + ''.'' + sp.TableName
201ORDER BY UsedMB DESC
202', @exclude_list='tempdb, model'
203
204SELECT top (10000) * FROM #h
205ORDER BY UsedMB DESC