Know the side can not be enough ~ wrote a calculation of the data table to occupy the storage space method

Back to Catalog

This example is about calculating the amount of storage space used by all the fields in a data table under a certain volume of data. Using the Sys.tables,systypes and syscolumns a few system tables, meaning is to traverse all the data table, and then the table so that the length of the field to sum, it is easy to understand the method, there is no technical content, but it played an important effect.

DECLARE @tableName VARCHAR( -)DECLARE @totalRecord BIGINTSET @tableName = 'User_info'SET @totalRecord = 1000000SELECT  @tableName + 'the table has' + LTRIM(STR(@totalRecord))+ 'data, the space occupied is:'        + LTRIM(STR(SUM(c.length)* @totalRecord / 1024.0 / 1024.0))+ 'MB' fromsystypes T, syscolumns CWHERET.xtype=C.xtype andC.id=(SELECTID fromSYSOBJECTSWHERENAME= @tableName                   ) SELECTTt.name, DATA= @TOTALRECORD, SIZE=(SELECT STR(SUM(c.length)* @TOTALRECORD / 1024.0 / 1024.0)                        + 'MB'                  fromsystypes T, syscolumns CWHERET.xtype=C.xtype andC.id=Tt.object_id               ) fromSYS. TABLES asTTORDER  byTt.name

Execution results are as follows

What's the fun?

Back to Catalog

Know the side can not be enough ~ wrote a calculation of the data table to occupy the storage space method