hi,
i have a database that has a data file that's over 50GB. i want to make it
smaller. how can i find out which table is causing the overall DB to be so
large. is there a command that shows me the database size broken down by
table, etc.

please advise

R C wrote:
> hi,
> i have a database that has a data file that's over 50GB. i want to make it
> smaller. how can i find out which table is causing the overall DB to be so
> large. is there a command that shows me the database size broken down by
> table, etc.
>
> please advise
which sql server version? database in which compatibility mode?

R C

This will run the sp_spaceused command against every table in the current
database.

sp_msforeachtable 'exec sp_spaceused [?]'

You can then examine the output for which tables are too big. You can also
query system views (or tables in SQL Server 2000 and earlier), but this one
works on all recent versions of SQL Server.

RLF

"R C" <RC@discussions.microsoft.com> wrote in message
news:C59191C8-3CAC-4B99-A004-B1167B009325@microsoft.com...
> hi,
> i have a database that has a data file that's over 50GB. i want to make
> it
> smaller. how can i find out which table is causing the overall DB to be
> so
> large. is there a command that shows me the database size broken down by
> table, etc.
>
> please advise

Check out this free tool SQLSPY at Hybridx.

http://www.hybridx.com/

I use it QUITE a bit, and you can also add your own user defined sql.

50 gig is not large for a database, but the tool should .

Once you find out what tables(s) are causing you grief, you can purge
records, or it's fairly simple to migrate it to an alternate file or
filegroup.

David

On Tue, 17 Jun 2008 05:25:02 -0700, R C <RC@discussions.microsoft.com>
wrote:

>hi,
>i have a database that has a data file that's over 50GB. i want to make it
>smaller. how can i find out which table is causing the overall DB to be so
>large. is there a command that shows me the database size broken down by
>table, etc.
>
>please advise

This for SQL 2000 & 2005, returns the usage of each table in the
database, sorted by size, largest first.

drop table #TemptableforspaceUsed
CREATE TABLE #TemptableforspaceUsed
(name SYSNAME,
rows INT,
reserved VARCHAR(10),
data VARCHAR(10),
index_size VARCHAR(10),
unused VARCHAR(10))
GO
INSERT #TemptableforspaceUsed
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
alter table #temptableforspaceused
add reserved_num integer null
go
update #temptableforspaceused
set reserved_num = left(reserved,charindex(' ',reserved))
select * from #TemptableforspaceUsed order by reserved_num desc