Determine the database objects' sizes?

I have a database whose size is over 50GB. What is the easy way to determine
the size of each database objects (tables)? I want to find out the ones that
take most of the space. Thanks a lot,
FLThis uses an undocumented and unsupported system procedure.
EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
Another method would be to do this:
SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
Run that in Query Analyzer, using Results to TEXT, copy the output to the
top pane and run that...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||FLX
1)sp_spaceused in the BOL
--
2) Vays has written a great SP to show big tables
CREATE PROC sp_show_huge_tables
(
@.top int = NULL,
@.include_system_tables bit = 0
)
AS
/*
To see the top three biggest user or system tables in your database:
EXEC sp_show_huge_tables 3, 1
****************************************************************************
*********************/
BEGIN
IF @.top > 0
SET ROWCOUNT @.top
SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
FROM
(
SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
[Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT
low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) /
1024.)/1024.)) AS [Total space used (MB)]
FROM sysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
AND
((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))
WHERE indid IN (0, 1, 255)
GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
) as a
ORDER BY [Total space used (MB)] DESC
SET ROWCOUNT 0
END
GO
--
3)SELECT TOP 5
CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'
ORDER BY used DESC
"FLX" <nospam@.hotmail.com> wrote in message
news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> I have a database whose size is over 50GB. What is the easy way to
determine
> the size of each database objects (tables)? I want to find out the ones
that
> take most of the space. Thanks a lot,
> FL
>
>|||And for both sets of responses, remember to update statistics first
(sysindexes won't necessarily be up to date).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#MXp$pu1EHA.1564@.TK2MSFTNGP09.phx.gbl...
> FLX
> 1)sp_spaceused in the BOL
> --
> 2) Vays has written a great SP to show big tables
> CREATE PROC sp_show_huge_tables
> (
> @.top int = NULL,
> @.include_system_tables bit = 0
> )
> AS
> /*
> To see the top three biggest user or system tables in your database:
> EXEC sp_show_huge_tables 3, 1
>
****************************************************************************
> *********************/
> BEGIN
> IF @.top > 0
> SET ROWCOUNT @.top
> SELECT [Table Name], (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND
> s.id = OBJECT_ID(a.[Table Name])) AS [Row count], [Total space used (MB)]
> FROM
> (
> SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
AS
> [Table Name],
> CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) *
(SELECT
> low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E'))
/
> 1024.)/1024.)) AS [Total space used (MB)]
> FROM sysindexes i (NOLOCK)
> INNER JOIN
> sysobjects o (NOLOCK)
> ON
> i.id = o.id AND
> ((@.include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')
> AND
> ((@.include_system_tables = 1)OR (OBJECTPROPERTY(i.id, 'IsMSShipped') =0))
> WHERE indid IN (0, 1, 255)
> GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id))
> ) as a
> ORDER BY [Total space used (MB)] DESC
>
> SET ROWCOUNT 0
> END
>
> GO
> --
> 3)SELECT TOP 5
> CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
> sysindexes.[rows] AS [NO_OF_ROWS],
> sysindexes.reserved AS [RESERVED_SPACE],
> sysindexes.used AS [USED_SPACE]
> FROM sysobjects
> INNER JOIN sysindexes
> ON sysobjects.[id] = sysindexes.[id]
> WHERE sysindexes.indid < 2
> AND sysobjects.type = 'U'
> ORDER BY used DESC
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwZI6nu1EHA.3908@.TK2MSFTNGP12.phx.gbl...
> This uses an undocumented and unsupported system procedure.
> EXEC sp_MSForEachTable 'EXEC sp_spaceused ''?'''
> Another method would be to do this:
> SELECT 'EXEC sp_spaceused '+TABLE_NAME+' FROM INFORMATION_SCHEMA.TABLES
> Run that in Query Analyzer, using Results to TEXT, copy the output to the
> top pane and run that...
>
Cool.
FYI, I found there is a typo (extra single quote) in the query. It should
be:
SELECT 'EXEC sp_spaceused '+ TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
FL
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:OicCviu1EHA.1192@.tk2msftngp13.phx.gbl...
> > I have a database whose size is over 50GB. What is the easy way to
> determine
> > the size of each database objects (tables)? I want to find out the ones
> that
> > take most of the space. Thanks a lot,
> >
> > FL
> >
> >
> >
>