View the size of the database tables in SQL

SQL to view the size of each table in the database

Author : CC Abba

2014-6-17

in Daily SQL in the operation of the database, how to quickly query the size of the data in the tables in the database.

There are two ways to refer to this:

The first type:

Create Table #t(name varchar(255), rows bigint, reserved varchar(20), Data varchar(20), index_size varchar(20), Unused varchar(20))

exec sp_msforeachtable "insert into #t exec sp_spaceused '? '"

Select * from #t order by rows desc

Select ' select * from '+name from #t order by rows desc

Drop table #t

The second type:

CREATE PROCEDURE get_tableinfo as

if not exists (Select * from dbo.sysobjects where ID = object_id(N ' [dbo]. [Tablespaceinfo] ') and ObjectProperty(ID, N ' isusertable ') =1)

create Span style= "Font-size:10.0pt;font-family:"courier new";" > table   tablespaceinfo                            -- Create a result store table

               ( nameinfo varchar ( 50 Span style= "Color:gray",  

Rowsinfo int , reserved varchar () ,

Datainfo varchar  () ,

index_size varchar () ,

Unused varchar () )

delete Span style= "Font-size:10.0pt;font-family:"courier new";" > from tablespaceinfo - - empty data table

Declare @tablename varchar  (255) -- Table name

Declare @cmdsql varchar ($)

DECLARE Info_cursor CURSOR for

Select o. name

from Span style= "Font-size:10.0pt;font-family:"courier new";" > dbo sysobjects o where ObjectProperty ( o id n ' istable ' = 1 

and o . name not like  N ' #%% ' Order by o . name

OPEN info_cursor

FETCH NEXT from info_cursor

into @tablename

while @ @FETCH_STATUS = 0

BEGIN

if exists (Select * from dbo.sysobjects where ID = object_id(@tablename) and ObjectProperty(ID, N ' isusertable ') =1)

Execute sp_executesql

N ' insert into Tablespaceinfo exec sp_spaceused @tbname ',

N ' @tbname varchar (255) ' ,

@tbname = @tablename

FETCH NEXT from Info_cursor

into @tablename

END

CLOSE info_cursor

deallocate info_cursor

GO

---- Executing stored procedures

--exec Get_tableinfo

--

-- query results from running the stored procedure

Select * from tablespaceinfo

Orderby cast(ltrim(rtrim(reserved ) , len(ltrim(rtrim( Reserved)))-2) as int) desc