Solved

SQL Server: getting list of all tables.

Posted on 2013-12-10
7
473 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 30

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 30

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:Scott Pletcher
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

740 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