-- =====================================================-- 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 NULLDROP TABLE #tb_request;CREATE TABLE #tb_request(id int IDENTITYPRIMARY 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));WITHDATA AS(SELECTserver_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 = CASEWHEN T.c.exist('r[4]/text()') = 1 THEN PARSENAME(T.c.value('r[3]/text()[1]', 'sysname'), 1)ELSE N'dbo'END,object_name = CASEWHEN 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')ENDFROM(SELECTdata = 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('.'))DCROSS APPLY D.data.nodes('/rs') T(c)WHERE T.c.exist('r/text()') = 1AND LEFT(LTRIM(T.c.value('r[1]/text()[1]', 'nvarchar(1000)')), 2) <> N'--')INSERT #tb_request(server_name, database_name, schema_name, object_name)SELECTserver_name, database_name, schema_name, object_nameFROM DATA; IF @flag_check_server = 1BEGIN;DELETE REQFROM #tb_request REQWHERE server_name NOT IN( CONVERT(sysname, SERVERPROPERTY('InstanceName')), CONVERT(sysname, SERVERPROPERTY('ServerName')), N'*');END;IF OBJECT_ID(N'tempdb..#re') IS NOT NULLDROP 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 STATICFORSELECTnameFROM sys.databases DB WITH(NOLOCK)WHERE DB.state IN(0, 5)AND EXISTS(SELECT * FROM #tb_requestWHERE 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 = 0BEGIN;SET @sql = N'USE ' + QUOTENAME(@database_name) + N';RAISERROR(N''...... work on %s'',10, 1,@database_name)WITH NOWAIT;WITHTB AS(SELECTschema_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 = 0AND EXISTS(SELECT * FROM #tb_request REQWHERE 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(CASEWHEN index_id < 2 THEN in_row_data_page_count + lob_used_page_count + row_overflow_used_page_countELSE lob_used_page_count + row_overflow_used_page_countEND),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_idAND IT.internal_type IN(202,204)GROUP BY IT.parent_id),SIZE AS(SELECTobject_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.pagesELSE 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 DATALEFT JOIN DATA1ON DATA.object_id = DATA1.object_id)SELECTdatabase_name = @database_name,schema_name = TB.schema_name,table_name = TB.name,SIZE.*FROM SIZEINNER JOIN TBON 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 REQWHERE NOT EXISTS(SELECT * FROM #re REWHERE 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 REQWHERE NOT EXISTS(SELECT * FROM #re REWHERE 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 #reORDER BYdatabase_name, table_name;---------------------------------------------------------------------------------------------------------------------USE AuditDBGOWITHOBJ 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_sizeFROM OBJ INNER JOIN IX ON IX.object_id = OBJ.object_id INNER JOIN SIZE ON SIZE.object_id = OBJ.object_idWHERE IX.object_id = OBJ.object_idORDER BY SIZE.data_size DESC,SIZE.reserved_size DESC;declare @db sysnameSET @db = DB_NAME()EXEC SP_helpdb @db
