Solved

SQL Server: getting list of all tables.

Posted on 2013-12-10
7
468 Views
Last Modified: 2013-12-26
Need a query to get a list of all tables in the database with the number of rows and size.
Thank you in advance.
0
Comment
Question by:quasar_ee
7 Comments
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 39709360
Try this:
if object_id('tempdb..#tmpTableSizes') is not null 
	DROP TABLE #tmpTableSizes
CREATE TABLE #tmpTableSizes
(
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)
insert #tmpTableSizes
	EXEC sp_MSforeachtable @command1='EXEC sp_spaceused ''?'''


select  
	* 
from 
	#tmpTableSizes
order by 
	cast(LEFT(reservedSize, LEN(reservedSize) - 4) as int)  desc

Open in new window

0
 
LVL 29

Expert Comment

by:Rich Weissler
ID: 39709372
The quick way to get the information would be, in Management Studio, right click the database, select reports -> standard reports -> Disk Usage by Table.  But that doesn't give you a query.

Running "sp_msforeachtable 'EXEC sp_spaceused [?]' " within the database will output the information you want, but the output might still not be what you are looking for (?).
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39709402
That report produces exact the same information like my code.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 29

Expert Comment

by:Rich Weissler
ID: 39709618
> That report produces exact the same information like my code.
Yes sir.  At the heart, its the same.  :-/  I gathered my information, and refreshed right before I started typing my answer, and when I finished discovered you had another answer which predated mine by a couple minutes.

Playing with other queries, I found/modified this one which doesn't use sp_spaceused, but relies on DMVs instead:

SELECT object_name(a.[object_id]) as TableName,
sum(a.record_count) as row_count,
sum(a.page_count)*1.0/128 as size_in_Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED') AS a
   JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
group by a.[object_id],a.index_id, b.name

Open in new window

0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39709677
The problem with sys.dm_db_index_physical_stats is that it takes some time to run, especially if you use DETAILED as parameter. It is usually used to get the index fragmentation.

And by the way, I had no idea that the report produces exact the same data as my query until I checked it after your post. :o) Probably is using the same stored procedure.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39709739
View sys.dm_db_partition_stats will give you that info easily and quickly.

The row count may only be approximate though; you can avoid that by issuing a DBCC UPDATEUSAGE first, but that can take quite a bit of time to complete, depending on table sizes.
0
 
LVL 5

Expert Comment

by:rk_india1
ID: 39710428
-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0 to 2008 any version

set nocount on


print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'

select
        [FileSizeMB]   =
               convert(numeric(10,2),sum(round(a.size/128.,2))),
        [UsedSpaceMB]  =
               convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
        [UnusedSpaceMB]        =
               convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
        [Type] =
               case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
        [DBFileName]   = isnull(a.name,'*** Total for all files ***')
from
        sysfiles a
group by
        groupid,
        a.name
        with rollup
having
        a.groupid is null or
        a.name is not null
order by
        case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
        a.groupid,
        case when a.name is null then 99 else 0 end,
        a.name




create table #TABLE_SPACE_WORK
(
        TABLE_NAME     sysname        not null ,
        TABLE_ROWS     numeric(18,0)  not null ,
        RESERVED       varchar(50)    not null ,
        DATA           varchar(50)    not null ,
        INDEX_SIZE     varchar(50)    not null ,
        UNUSED         varchar(50)    not null ,
)

create table #TABLE_SPACE_USED
(
        Seq            int            not null      
        identity(1,1)  primary key clustered,
        TABLE_NAME     sysname        not null ,
        TABLE_ROWS     numeric(18,0)  not null ,
        RESERVED       varchar(50)    not null ,
        DATA           varchar(50)    not null ,
        INDEX_SIZE     varchar(50)    not null ,
        UNUSED         varchar(50)    not null ,
)

create table #TABLE_SPACE
(
        Seq            int            not null
        identity(1,1)  primary key clustered,
        TABLE_NAME     SYSNAME        not null ,
        TABLE_ROWS     int             not null ,
        RESERVED       int             not null ,
        DATA           int             not null ,
        INDEX_SIZE     int             not null ,
        UNUSED         int             not null ,
        USED_MB                        numeric(18,4)  not null,
        USED_GB                        numeric(18,4)  not null,
        AVERAGE_BYTES_PER_ROW          numeric(18,5)  null,
        AVERAGE_DATA_BYTES_PER_ROW     numeric(18,5)  null,
        AVERAGE_INDEX_BYTES_PER_ROW    numeric(18,5)  null,
        AVERAGE_UNUSED_BYTES_PER_ROW   numeric(18,5)  null,
)

declare @fetch_status int

declare @proc  varchar(200)
select  @proc   = rtrim(db_name())+'.dbo.sp_spaceused'

declare Cur_Cursor cursor local
for
select
        TABLE_NAME     =
        rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
        INFORMATION_SCHEMA.TABLES
where
        TABLE_TYPE     = 'BASE TABLE'
order by
        1

open Cur_Cursor

declare @TABLE_NAME    varchar(200)

select @fetch_status = 0

while @fetch_status = 0
        begin

        fetch next from Cur_Cursor
        into
               @TABLE_NAME

        select @fetch_status = @@fetch_status

        if @fetch_status <> 0
               begin
               continue
               end

        truncate table #TABLE_SPACE_WORK

        insert into #TABLE_SPACE_WORK
               (
               TABLE_NAME,
               TABLE_ROWS,
               RESERVED,
               DATA,
               INDEX_SIZE,
               UNUSED
               )
        exec @proc @objname =
               @TABLE_NAME ,@updateusage = 'true'


        -- Needed to work with SQL 7
        update #TABLE_SPACE_WORK
        set
               TABLE_NAME = @TABLE_NAME

        insert into #TABLE_SPACE_USED
               (
               TABLE_NAME,
               TABLE_ROWS,
               RESERVED,
               DATA,
               INDEX_SIZE,
               UNUSED
               )
        select
               TABLE_NAME,
               TABLE_ROWS,
               RESERVED,
               DATA,
               INDEX_SIZE,
               UNUSED
        from
               #TABLE_SPACE_WORK

        end     --While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
        (
        TABLE_NAME,
        TABLE_ROWS,
        RESERVED,
        DATA,
        INDEX_SIZE,
        UNUSED,
        USED_MB,
        USED_GB,
        AVERAGE_BYTES_PER_ROW,
        AVERAGE_DATA_BYTES_PER_ROW,
        AVERAGE_INDEX_BYTES_PER_ROW,
        AVERAGE_UNUSED_BYTES_PER_ROW

        )
select
        TABLE_NAME,
        TABLE_ROWS,
        RESERVED,
        DATA,
        INDEX_SIZE,
        UNUSED,
        USED_MB                =
               round(convert(numeric(25,10),RESERVED)/
               convert(numeric(25,10),1024),4),
        USED_GB                =
               round(convert(numeric(25,10),RESERVED)/
               convert(numeric(25,10),1024*1024),4),
        AVERAGE_BYTES_PER_ROW  =
               case
               when TABLE_ROWS <> 0
               then round(
               (1024.000000*convert(numeric(25,10),RESERVED))/
               convert(numeric(25,10),TABLE_ROWS),5)
               else null
               end,
        AVERAGE_DATA_BYTES_PER_ROW     =
               case
               when TABLE_ROWS <> 0
               then round(
               (1024.000000*convert(numeric(25,10),DATA))/
               convert(numeric(25,10),TABLE_ROWS),5)
               else null
               end,
        AVERAGE_INDEX_BYTES_PER_ROW    =
               case
               when TABLE_ROWS <> 0
               then round(
               (1024.000000*convert(numeric(25,10),INDEX_SIZE))/
               convert(numeric(25,10),TABLE_ROWS),5)
               else null
               end,
        AVERAGE_UNUSED_BYTES_PER_ROW   =
               case
               when TABLE_ROWS <> 0
               then round(
               (1024.000000*convert(numeric(25,10),UNUSED))/
               convert(numeric(25,10),TABLE_ROWS),5)
               else null
               end
from
        (
        select
               TABLE_NAME,
               TABLE_ROWS,
               RESERVED       =
               convert(int,rtrim(replace(RESERVED,'KB',''))),
               DATA           =
               convert(int,rtrim(replace(DATA,'KB',''))),
               INDEX_SIZE     =
               convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
               UNUSED         =
               convert(int,rtrim(replace(UNUSED,'KB','')))
        from
               #TABLE_SPACE_USED aa
        ) a
order by
        TABLE_NAME

print 'Show results in descending order by size in MB'

select * from #TABLE_SPACE order by USED_MB desc
go

drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE
Result.jpg
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now