Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server: getting list of all tables.

Posted on 2013-12-10
7
Medium Priority
?
477 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 27

Accepted Solution

by:
Zberteoc earned 2000 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 27

Expert Comment

by:Zberteoc
ID: 39709402
That report produces exact the same information like my code.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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 27

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 70

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 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