-- ===================================================== -- Script for: -- table size information query. -- single database, can Ignore schema name -- ===================================================== SET NOCOUNT ON; DECLARE @size__unit decimal(24, 20), @flag_check_server bit, @request_tables nvarchar(max) ; SELECT @size__unit = 8, -- KB @size__unit = @size__unit / 1024, -- MB @size__unit = @size__unit / 1024, -- GB @flag_check_server = 0, @request_tables = N' *.*.*.* '; IF OBJECT_ID(N'tempdb..#tb_request') IS NOT NULL DROP TABLE #tb_request; CREATE TABLE #tb_request( id int IDENTITY PRIMARY KEY, server_name sysname, database_name sysname, schema_name sysname, object_name sysname, UNIQUE( server_name, database_name, schema_name, object_name ) WITH( IGNORE_DUP_KEY = ON ) ); WITH DATA AS( SELECT server_name = PARSENAME(T.c.value('r[1]/text()[1]', 'sysname'), 1), database_name = PARSENAME(T.c.value('r[2]/text()[1]', 'sysname'), 1), schema_name = CASE WHEN T.c.exist('r[4]/text()') = 1 THEN PARSENAME(T.c.value('r[3]/text()[1]', 'sysname'), 1) ELSE N'dbo' END, object_name = CASE WHEN T.c.exist('r[4]/text()') = 1 THEN PARSENAME(T.c.value('r[4]/text()[1]', 'sysname'), 1) ELSE T.c.value('r[3]/text()[1]', 'sysname') END FROM( SELECT data = CONVERT(xml, N'<rs><r><![CDATA[' + REPLACE( REPLACE( REPLACE( REPLACE( @request_tables, char(10), char(13)), CHAR(9), N']]></r><r><![CDATA['), N'.', N']]></r><r><![CDATA['), char(13), N']]></r></rs><rs><r><![CDATA[') + N']]></r></rs>').query('.') )D CROSS APPLY D.data.nodes('/rs') T(c) WHERE T.c.exist('r/text()') = 1 AND LEFT(LTRIM(T.c.value('r[1]/text()[1]', 'nvarchar(1000)')), 2) <> N'--' ) INSERT #tb_request( server_name, database_name, schema_name, object_name ) SELECT server_name, database_name, schema_name, object_name FROM DATA ; IF @flag_check_server = 1 BEGIN; DELETE REQ FROM #tb_request REQ WHERE server_name NOT IN( CONVERT(sysname, SERVERPROPERTY('InstanceName')), CONVERT(sysname, SERVERPROPERTY('ServerName')), N'*' ) ; END; IF OBJECT_ID(N'tempdb..#re') IS NOT NULL DROP TABLE #re; CREATE TABLE #re( database_name sysname, schema_name sysname, table_name sysname, object_id int, row_count bigint, reserved_size decimal(24, 2), data_size decimal(24, 2), index_size decimal(24, 2), unused_size decimal(24, 2) ); DECLARE CUR_DB CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC FOR SELECT name FROM sys.databases DB WITH(NOLOCK) WHERE DB.state IN( 0, 5 ) AND EXISTS( SELECT * FROM #tb_request WHERE database_name IN( DB.name, N'*' ) ) AND name NOT IN( 'master' ,'tempdb' ,'model' ,'msdb' ) ORDER BY name ; DECLARE @sql nvarchar(max), @database_name sysname ; OPEN CUR_DB; FETCH CUR_DB INTO @database_name ; WHILE @@FETCH_STATUS = 0 BEGIN; SET @sql = N' USE ' + QUOTENAME(@database_name) + N'; RAISERROR( N''...... work on %s'', 10, 1, @database_name )WITH NOWAIT; WITH TB AS( SELECT schema_name = SCH.name, TB.* FROM sys.tables TB WITH(NOLOCK) INNER JOIN sys.schemas SCH WITH(NOLOCK) ON SCH.schema_id = TB.schema_id WHERE TB.is_ms_shipped = 0 AND EXISTS( SELECT * FROM #tb_request REQ WHERE REQ.database_name IN( @database_name, N''*'' ) AND REQ.object_name IN( TB.name, N''*'' ) AND REQ.schema_name IN( SCH.name, N''*'' ) ) ), DATA AS( SELECT object_id, reserved_pages = SUM(reserved_page_count), used_pages = SUM(used_page_count), pages = 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), row_count = SUM(CASE WHEN index_id < 2 THEN row_count ELSE 0 END) FROM sys.dm_db_partition_stats WITH(NOLOCK) GROUP BY object_id ), DATA1 AS( SELECT object_id = IT.parent_id, reserved_pages = SUM(P.reserved_page_count), used_pages = SUM(P.used_page_count) FROM sys.dm_db_partition_stats P WITH(NOLOCK), sys.internal_tables IT WITH(NOLOCK) WHERE P.object_id = IT.object_id AND IT.internal_type IN(202,204) GROUP BY IT.parent_id ), SIZE AS( SELECT object_id = DATA.object_id, DATA.row_count, reserved_size = CONVERT(decimal(15, 2), (DATA.reserved_pages + ISNULL(DATA1.reserved_pages, 0)) * @size__unit), data_size = CONVERT(decimal(15, 2), DATA.pages * @size__unit), index_size = CONVERT(decimal(15, 2), CASE WHEN (DATA.used_pages + ISNULL(DATA1.used_pages, 0)) > DATA.pages THEN (DATA.used_pages + ISNULL(DATA1.used_pages, 0)) - DATA.pages ELSE 0 END * @size__unit), unused_size = CONVERT(decimal(15, 2), CASE WHEN (DATA.reserved_pages + ISNULL(DATA1.reserved_pages, 0)) > (DATA.used_pages + ISNULL(DATA1.used_pages, 0)) THEN (DATA.reserved_pages + ISNULL(DATA1.reserved_pages, 0)) - (DATA.used_pages + ISNULL(DATA1.used_pages, 0)) ELSE 0 END * @size__unit) FROM DATA LEFT JOIN DATA1 ON DATA.object_id = DATA1.object_id ) SELECT database_name = @database_name, schema_name = TB.schema_name, table_name = TB.name, SIZE.* FROM SIZE INNER JOIN TB ON TB.object_id = SIZE.object_id ORDER BY schema_name, table_name ; '; INSERT #re( database_name, schema_name, table_name, object_id, row_count, reserved_size, data_size, index_size, unused_size ) EXEC sp_executesql @sql, N' @size__unit decimal(24, 20), @database_name sysname ', @size__unit, @database_name ; FETCH CUR_DB INTO @database_name ; END; CLOSE CUR_DB; DEALLOCATE CUR_DB; IF EXISTS( SELECT * FROM #tb_request REQ WHERE NOT EXISTS( SELECT * FROM #re RE WHERE REQ.database_name IN( RE.database_name, N'*' ) AND REQ.schema_name IN( RE.schema_name, N'*' ) AND REQ.object_name IN( RE.table_name, N'*' ) ) ) BEGIN; SELECT * FROM #tb_request REQ WHERE NOT EXISTS( SELECT * FROM #re RE WHERE REQ.database_name IN( RE.database_name, N'*' ) AND REQ.schema_name IN( RE.schema_name, N'*' ) AND REQ.object_name IN( RE.table_name, N'*' ) ) ORDER BY server_name, database_name, object_name ; END; SELECT * FROM #re ORDER BY database_name, table_name ; --------------------------------------------------------------------------------------------------------------------- USE AuditDB GO WITH OBJ AS( SELECT object_id = TB.object_id, schema_name = SCH.name, object_name = TB.name, TB.Create_Date, TB.Modify_date FROM sys.tables TB WITH(NOLOCK), sys.schemas SCH WITH(NOLOCK) WHERE TB.schema_id = SCH.schema_id ), IX AS( SELECT object_id, index_count = COUNT(*), has_primary_key = MAX(CONVERT(tinyint, is_primary_key)), has_clustered_index = MAX(CASE WHEN type = 1 THEN 1 ELSE 0 END), has_index = MAX(CASE WHEN type = 0 THEN 0 ELSE 1 END) FROM sys.indexes WITH(NOLOCK) GROUP BY object_id ), SIZE_1 AS( SELECT object_id, reserved_pages = SUM(reserved_page_count), used_pages = SUM(used_page_count), pages = 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), row_count = SUM(CASE WHEN index_id < 2 THEN row_count ELSE 0 END) FROM sys.dm_db_partition_stats WITH(NOLOCK) GROUP BY object_id ), SIZE_2 AS( SELECT object_id = IT.parent_id, reserved_pages = SUM(P.reserved_page_count), used_pages = SUM(P.used_page_count) FROM sys.dm_db_partition_stats P WITH(NOLOCK), sys.internal_tables IT WITH(NOLOCK) WHERE P.object_id = IT.object_id AND IT.internal_type IN(202,204) GROUP BY IT.parent_id ), SIZE AS( SELECT SIZE_1.object_id, SIZE_1.row_count, reserved_size = CONVERT(decimal(15, 2), (SIZE_1.reserved_pages + ISNULL(SIZE_2.reserved_pages, 0)) * 8.), data_size = CONVERT(decimal(15, 2), SIZE_1.pages * 8.), index_size = CONVERT(decimal(15, 2), CASE WHEN (SIZE_1.used_pages + ISNULL(SIZE_2.used_pages, 0)) > SIZE_1.pages THEN (SIZE_1.used_pages + ISNULL(SIZE_2.used_pages, 0)) - SIZE_1.pages ELSE 0 END * 8.), unused_size = CONVERT(decimal(15, 2), CASE WHEN (SIZE_1.reserved_pages + ISNULL(SIZE_2.reserved_pages, 0)) > (SIZE_1.used_pages + ISNULL(SIZE_2.used_pages, 0)) THEN (SIZE_1.reserved_pages + ISNULL(SIZE_2.reserved_pages, 0)) - (SIZE_1.used_pages + ISNULL(SIZE_2.used_pages, 0)) ELSE 0 END * 8.) FROM SIZE_1 LEFT JOIN SIZE_2 ON SIZE_2.object_id = SIZE_1.object_id ) SELECT OBJ.*, IX.index_count, IX.has_primary_key, IX.has_clustered_index, IX.has_index, SIZE.row_count, SIZE.reserved_size, SIZE.data_size, SIZE.index_size, SIZE.unused_size FROM OBJ INNER JOIN IX ON IX.object_id = OBJ.object_id INNER JOIN SIZE ON SIZE.object_id = OBJ.object_id WHERE IX.object_id = OBJ.object_id ORDER BY SIZE.data_size DESC,SIZE.reserved_size DESC; declare @db sysname SET @db = DB_NAME() EXEC SP_helpdb @db
察看当前实例每个DB的table大小信息/察看某个DB中包含table大小信息
最新推荐文章于 2022-06-21 20:47:07 发布
这段SQL脚本用于查询指定数据库或所有数据库中每个表的大小信息,包括KB、MB和GB单位。它首先创建一个临时表来存储请求的表信息,然后遍历每个符合条件的数据库,获取每个表的行数、保留大小、数据大小、索引大小和未使用大小。最后,脚本在AuditDB数据库上展示了如何查看表的详细信息。
摘要由CSDN通过智能技术生成