察看当前实例每个DB的table大小信息/察看某个DB中包含table大小信息

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