Re: How to know the table size in sql database and how to know whi

From: Sasan Saidi (SasanSaidi_at_discussions.microsoft.com)
Date: 11/01/04 

Date: Mon, 1 Nov 2004 12:00:03 -0800

this will do the trick:

sp_MSforeachtable @command1="print '?' Exec sp_mstablespace '?'"

What is happening here is that '?' is like a variable containing the name of
each table during the iteration that happens within the stored procedure.
Therefore, you will have the table name as output and the size of it. 

--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Wayne Snyder" wrote:
> YOu may also want to write a query directly agains sysindexes... If I
> remember correctly, dpages, ipages, and reserved..You might want to take a
> look at sp_spaceused and steal som code from there to come up with your
> own... or search the web ( maybe www.sqlservercentral.com) probably has a
> script you can use..
> 
> -- 
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> 
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> 
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:uBkRydAwEHA.1976@TK2MSFTNGP09.phx.gbl...
> > Hi
> > Probably  a best method will be to run with cursor thru each table and
> > perform select count(*) from table and then compare which one has a big
> > output
> >
> >
> >
> >
> >
> > "Florencelee" <florencelee@visualsolutions.com.my> wrote in message
> > news:OufvGNAwEHA.3320@TK2MSFTNGP14.phx.gbl...
> > > Hi,
> > >     Thanks for your reply..another question, what if i want to know
> which
> > > table has the biggest size?
> > >
> > > Thanks.
> > > regards,
> > >
> > > florence
> > >
> > > "Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message
> > > news:#IDW43$vEHA.2192@TK2MSFTNGP14.phx.gbl...
> > > > Hi,
> > > >
> > > > To get the space used:-
> > > >
> > > > SP_spaceused <table_name>
> > > >
> > > > Enable the profiler to identify the updates happened.
> > > >
> > > >
> > > > --
> > > > Thanks
> > > > Hari
> > > > SQL Server MVP
> > > >
> > > >
> > > > "Florencelee" <florencelee@visualsolutions.com.my> wrote in message
> > > > news:OYkrcy$vEHA.612@TK2MSFTNGP15.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > May i check with you, how to know the size of the table in the
> > database.
> > > > > If
> > > > > someone has updated one data into one table, how to know, which
> table
> > > has
> > > > > updated at the database?
> > > > >
> > > > > Thanks.
> > > > >
> > > > > regards,
> > > > > florence
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
> 
> 
>

Relevant Pages

  • Re: Time range matching
    ... > datetime with a time range that spans multiple days... ... > Wayne Snyder, MCDBA, SQL Server MVP ... > community of SQL Server professionals. ...
    (microsoft.public.sqlserver.server)
  • Re: Add new column to a replicated table..
    ... "Wayne Snyder" wrote in message ... > Sp adds the column to the table, and fixes replication... ... > Wayne Snyder, MCDBA, SQL Server MVP ... > community of SQL Server professionals. ...
    (microsoft.public.sqlserver.programming)
  • Re: How to learn .Net
    ... > Wayne Snyder, MCDBA, SQL Server MVP ... > Mariner, Charlotte, NC ... >> I read everywhere at the moment that SQL Server professionals need to ...
    (microsoft.public.sqlserver.server)
  • Re: VARCHAR to INT
    ... ISNumeric function allows float, decimal numbers also... ... > Wayne Snyder, MCDBA, SQL Server MVP ... > community of SQL Server professionals. ...
    (microsoft.public.sqlserver.programming)
  • Re: consolidating two data files into one.
    ... > then alter database prod drop file file2 ... > Wayne Snyder, MCDBA, SQL Server MVP ... > community of SQL Server professionals. ...
    (microsoft.public.sqlserver.server)
Loading