Calculate the amount of data for each table in a database and the space occupied by each row of records-add schema information-feedback and knowledge collation

Reference article:

SQL Server Cursor Utilization: View all table size information for a database (Sizes of all Tables in a databases)

Share a SQL Server script (calculate the amount of data for each table in the database and the space occupied by each row of records)

Monitor the spatial variability of SQL Server database tables daily

Carefully read the above three-bit article, not the knowledge point and reference to MSDN, consolidating the knowledge points are as follows:

Knowledge Points:

1. The schema information for the table, involving the system objects Sys.schemas and INFORMATION_SCHEMA. TABLES, but the latter is not the official recommended way, specifically refer to MSDN

2. The basic information of the table, the system objects involved sys.tables

3. Creation and deletion of temporary tables, usage of object_id

4. Cursor usage and the meaning of each option, cursor local, forward, read-only, static

5. Conversion of data types, integer division

Reorganize your feet as follows, tables with different schemas are supported

/* You need to specify the database */set NOCOUNT on;if object_id (' tempdb ') before calculating the amount of data for each table in the database and the space consumed by each row of records. #tablespaceinfo ') is not nulldrop TABLE #tablespaceinfo; CREATE TABLE #tablespaceinfo (nameinfo varchar), rowsinfo BIGINT, reserved varchar, datainfo varcha R (n), index_size varchar (+), unused varchar), IF object_id (' tempdb. #tablespaceinfo_temp ') is not nulldrop TABLE #tablespaceinfo_temp; CREATE TABLE #tablespaceinfo_temp (nameinfo varchar), rowsinfo BIGINT, reserved varchar (+), Datainfo V Archar (index_size), unused varchar (20));  DECLARE @tablename VARCHAR (255); --information_schema. TABLES is not the recommended way to get the schema, so use the following statement instead declare info_cursor cursor LOCAL forward_only STATIC read_onlyfor SELECT QUOTENAME (S.nam E, ' [] ') + '. '  +quotename (T.name, ' [] ') from Sys.tables as T-left joins Sys.schemas as S on t.schema_id=s.schema_id WHERE t.type = ' U '; OPEN Info_cursor; FETCH NEXT from Info_cursor to @tablename; While @ @FETCH_STATUS = 0 BegiNdelete from #tablespaceinfo_temp;--sp_spaceused cannot be used before SQL 2005 INSERT into #tablespaceinfo_temp (Nameinfo,rowsinfo, reserved,datainfo,index_size,unused) EXEC sp_spaceused @tablename; INSERT into #tablespaceinfo (Nameinfo,rowsinfo, reserved,datainfo,index_size,unused) SELECT @tablename, rowsinfo,reserved,datainfo,index_size,unused from #        Tablespaceinfo_temp;    FETCH NEXT from Info_cursor to @tablename; Endclose info_cursor;deallocate info_cursor;drop TABLE #tablespaceinfo_temp;--Summary record SELECT *, (case Rowsinfowhen 0 Then 0ELSE Convert (Decimal (19,8), Convert (Decimal (19,2), left (Datainfo,len (Datainfo)-3)/rowsinfo) END) as ' Each row takes up approximately space (KB) ' from #tablespaceinfo--the result sort modifies order by CAST (REPLACE (reserved, ' KB ', ') as INT) Desc;drop TABLE [# Tablespaceinfo];

The above three articles, detailing how to calculate the number of rows in a database table, the amount of space and other information, after reading a reference to review, 1.1 points from MSDN in the resources, compiled a new script. Originally thought will be simple, but the actual operation, the details will defeat the people, comments in the friend will mention a lot of actual business encountered problems, very helpful!