# of rows in all tables.

# of rows in all tables.

Post by Allan Grimsha » Thu, 12 Oct 2000 04:00:00



This does what you want for all the tables in the database or
a specific table if you supply a table name.
As well it includes physical sizes of the tables.


as



/*                                  */
/* Build temp tables                */
/* One with the tables to report on*/
/* the other with all the numbers   */
/*                                  */

begin
  select so.name into #temp_name
    from sysobjects so
    where so.type = "U"
    order by so.name

  select name = so.name,
      rowcnt = rowcnt(si.doampg),
      reserved = (reserved_pgs(si.id, si.doampg) +
        reserved_pgs(si.id, si.ioampg)),
      low = d.low,
      dpgs = data_pgs(si.id, si.doampg),
      ipgs = data_pgs(si.id, si.ioampg),
      unused = ((reserved_pgs(si.id, si.doampg) +reserved_pgs(si.id,
si.ioampg))
         - (data_pgs(si.id, si.doampg) + data_pgs(si.id, si.ioampg)))
    into #pagecounts
    from sysobjects so, sysindexes si, master.dbo.spt_values d
    where si.id = so.id
      and d.number = 1
      and d.type = "E"
    order by so.name
end
/*                              */
/* Test for supplied table name */
/*                              */

begin
/*       */
/* List allUsertables "U"     */
/*          */
/* Join the two temp tables     */
/*   for the whole database     */
/*                              */
  select distinct
    #temp_name.name,
    rowcnt = convert(char(11), sum(rowcnt)),
    total_space = convert(char(18), convert(varchar(14),
       (convert(float,sum(reserved)) * convert(float,low))/ 1024.0)
       + " " + "KB"),
    data = convert(char(18), convert(varchar(14),

       (convert(float,sum(dpgs)) * convert(float,low))/ 1024.0)
 + "" + "KB"),
    index_size =convert(char(18), convert(varchar(14),
       (convert(float,sum(ipgs)) * convert(float,low))/ 1024.0)
       + " " + "KB"),
    unused = convert(char(18), convert(varchar(14),
       (convert(float,sum(unused)) * convert(float,low))/ 1024.0)
 + " " + "KB"),

    DB = db_name(),

    from #pagecounts, #temp_name
    where #temp_name.name = #pagecounts.name
    group by #temp_name.name
    order by #temp_name.name
end
else
/*                       */
/* List just the requested table */
/*                               */
begin
/*                                                             */
/* Get the internal object id for the table. Thisis the only  */
/* accurate way to join two system tables        */
/*                                               */

  select name = so.name,
    rowcnt = rowcnt(si.doampg),
    reserved =(reserved_pgs(si.id, si.doampg) +
 reserved_pgs(si.id, si.ioampg)),
    low = d.low,
    dpgs = data_pgs(si.id, si.doampg),
    ipgs = data_pgs(si.id, si.ioampg),
    unused = ((reserved_pgs(si.id, si.doampg) + reserved_pgs(si.id,
si.ioampg))
      - (data_pgs(si.id, si.doampg)+ data_pgs(si.id, si.ioampg)))

  into #pagecounts2
  from sysobjects so, sysindexes si, master.dbo.spt_values d


    and d.number = 1
    and d.type = "E"

  select distinct name,
rowcnt = convert(char(11), sum(rowcnt)),
    total_space = convert(char(18), convert(varchar(14),
       (convert(float,sum(reserved)) * convert(float,low))/ 1024.0)
       + " " + "KB"),
    data = convert(char(18), convert(varchar(14),
       (convert(float,sum(dpgs)) *convert(float,low))/ 1024.0)
       + " " + "KB"),
    index_size = convert(char(18), convert(varchar(14),
       (convert(float,sum(ipgs)) * convert(float,low))/ 1024.0)
       + " " + "KB"),
    unused = convert(char(18), convert(varchar(14),
 (convert(float,sum(unused)) * convert(float,low))/ 1024.0)
       + " " + "KB"),

    DB = db_name(),

  from #pagecounts2
end
exec sp_spaceused
go
GRANT EXECUTE ON dbo.sp_dbarowcount TO public



> Is it possible to write a SQL query that will list all the user tables
> in a database and the no. of rows in each table?

> The output format could be somthing like this :-
> Table            Count
> -------            --------
> TName1               10
> TName2               30
> .
> .

> Thanks.

 
 
 

# of rows in all tables.

Post by Larry Coo » Thu, 12 Oct 2000 04:00:00



> Is it possible to write a SQL query that will list all the user tables
> in a database and the no. of rows in each table?

> The output format could be somthing like this :-
> Table            Count
> -------            --------
> TName1               10
> TName2               30

Something like this will work from the statistics:

select    object_name(id), rowcnt
from      systabstats
where     indid = 0
order by  object_name(id)

Larry Coon
University of California