Growth Of TABLE

• nkm129

  SSCommitted

  Points: 1763

  More actions

  May 7, 2008 at 4:33 am

  #190296

  some of my tables are increasing rapidly. as a DBA how can we check which tables are increasing rapidly suppose we have around 1000 tables.

  Thanks!

• Torsten Schüßler-406123

  SSC-Addicted

  Points: 496

  More actions

  May 7, 2008 at 5:34 am

  #811679

  Hi nkm129,

  quick and dirty:

  Use YourDB

  GO

  sp_msforeachtable 'exec sp_spaceused [?]'

  GO

  perhaps a little bit more:

  select 'Database Name: ', db_name()

  set nocount on

  if exists(select name from tempdb..sysobjects where name='##tmp')

  drop table ##tmp

  create table ##tmp(nam varchar(50), rows int, res varchar(15),data varchar(15),ind_sze varchar(15),unsed varchar(15))

  go

  declare @tblname varchar(50)

  declare tblname CURSOR for select name from sysobjects where xtype='U'

  open tblname

  Fetch next from tblname into @tblname

  WHILE @@FETCH_STATUS = 0

  BEGIN

  insert into ##tmp

  exec sp_spaceused @tblname

  FETCH NEXT FROM tblname INTO @tblname

  END

  CLOSE tblname

  deallocate tblname

  go

  select nam Table_Name,rows Total_Rows,res Total_Table_Size,data Data_size,ind_sze Index_Size,unsed Unused_Space from ##tmp

  drop table ##tmp

  CU

  tosc

  [font="Verdana"]CU
  tosc[/font]

  www.insidesql.org