Use scripts to view the size of each table in a database

Use scripts to view the size of each table in a database

-- Transferred from sqlservercentrual.com   Declare @ ID int Declare @ type character (2) Declare @ pages Int Declare @ dbname sysname Declare @ dbsize Dec (15, 0) Declare @ bytesperpage Dec (15, 0) Declare @ pagespermb Dec (15, 0)   Create Table # spt_space ( Objid int null, Rows int null, Reserved Dec (15) null, Data Dec (15) null, Indexp Dec (15) null, Unused Dec (15) null )   Set nocount on   -- Create a cursor to loop through the user tables Declare c_tables cursor Select ID From sysobjects Where xtype = 'U'   Open c_tables   Fetch next from c_tables Into @ ID   While @ fetch_status = 0 Begin   /* Code from sp_spaceused */ Insert into # spt_space (objid, reserved) Select objid = @ ID, sum (Reserved) From sysindexes Where indid in (0, 1,255) And Id = @ ID   Select @ pages = sum (dpages) From sysindexes Where indid <2 And Id = @ ID Select @ pages = @ pages + isnull (sum (used), 0) From sysindexes Where indid = 1, 255 And Id = @ ID Update # spt_space Set Data = @ pages Where objid = @ ID     /* Index: sum (used) Where indid in (0, 1,255)-Data */ Update # spt_space Set indexp = (select sum (used) From sysindexes Where indid in (0, 1,255) And Id = @ ID) -Data Where objid = @ ID   /* Unused: sum (Reserved)-sum (used) Where indid in (0, 1,255 )*/ Update # spt_space Set unused = Reserved -(Select sum (used) From sysindexes Where indid in (0, 1,255) And Id = @ ID) Where objid = @ ID   Update # spt_space Set rows = I. Rows From sysindexes I Where I. indid <2 And I. ID = @ ID And objid = @ ID   Fetch next from c_tables Into @ ID End   Select tablename = (select left (name, 60) from sysobjects where id = objid ), Rows = convert (char (11), rows ), Reservedkb = ltrim (STR (Reserved * D. Low/1024.,) + ''+ 'kb '), Datakb = ltrim (STR (Data * D. Low/1024 ..,) + ''+ 'kb '), Indexsizekb = ltrim (STR (indexp * D. Low/1024.,) + ''+ 'kb '), Unusedkb = ltrim (STR (unused * D. Low/1024.,) + ''+ 'kb ') From # spt_space, master. DBO. spt_values d Where D. Number = 1 And D. type = 'E' Order by reserved DESC Drop table # spt_space Close c_tables Deallocate c_tables