[Note] Query table record space usage SQL script

 UseExpressdbCREATE TABLE#tablespaceinfo (NameinfoVARCHAR( -), RowsinfoBIGINT, reservedVARCHAR( -), DatainfoVARCHAR( -), Index_sizeVARCHAR( -), unusedVARCHAR( -)    )   --Creating CursorsDECLAREInfo_cursorCURSOR for    SELECT  '[' + [name] + ']'     fromsys.tablesWHEREType= 'U'; OPENInfo_cursorDECLARE @tablename VARCHAR(255); FETCH NEXT  fromInfo_cursor into @tablename    while @ @FETCH_STATUS = 0    BEGIN         INSERT   into#tablespaceinfoEXECsp_spaceused@tablename          FETCH NEXT  fromInfo_cursor into @tablename      END  CLOSEInfo_cursordeallocateInfo_cursor--Create a temporary tableCREATE TABLE [#tmptb](TableNameVARCHAR( -), DatainfoBIGINT,--calculated as data page size, which occupies one data page (8KB) Even if the table has only one recordRowsinfoBIGINT, Spaceperrow as( CaseRowsinfo when 0  Then 0                         --ELSE Datainfo/rowsinfo--Data page size information divided by the number of records.                          ELSE CAST(Datainfo as decimal(8,2))/CAST(Rowsinfo as decimal(8,2))                       END) PERSISTED)--inserting data into a temporary tableINSERT   into [#tmptb]        ( [TableName] ,          [Datainfo] ,          [Rowsinfo]        )        SELECT  [Nameinfo] ,                CAST(REPLACE([Datainfo],'KB',"') as BIGINT) as 'Datainfo' ,                [Rowsinfo]         from#tablespaceinfoORDER  by CAST(REPLACE(Reserved,'KB',"') as INT)DESC  --Summary recordsSELECT  [Tbspinfo].* ,        [TMPTB].[Spaceperrow]  as 'approximately space per row of records (KB)' from    [#tablespaceinfo]  asTbspinfo,[#tmptb]  asTMPTBWHERE   [Tbspinfo].[Nameinfo] = [TMPTB].[TableName]ORDER  by CAST(REPLACE([Tbspinfo].[reserved],'KB',"') as INT)DESC  DROP TABLE [#tablespaceinfo]DROP TABLE [#tmptb]

View Code

When the number of records in a table is low, the amount of space per row recorded is inaccurate.

SELECTAVG (Datalength (C0))+AVG (Datalength (C1))+AVG (Datalength (C2))+AVG (Datalength (C3))From[dbo].[tablename]