October 25, 2002 at 4:10 pm
I am looking for a way to get a list of all the tables in this huge db sorted by the file size.
Any help?
Thanks
October 27, 2002 at 12:13 pm
You can use this
go
/*******************************************************************************
Written By : Simon Sabin
Date : 25 October 2002
Description : Returns the spaceused by all tables in a database
:
History
Date Change
------------------------------------------------------------------------------
25/10/2002 Created
*******************************************************************************/
SET NOCOUNT ON
DECLARE @SetOption bit, @databasename varchar(30), @orderCol varchar(30), @numeric bit
/*******************************************************************************
--Change this to change the way data is ordered
*******************************************************************************/
SELECT @orderCol = 'data'
SELECT @DatabaseName = db_name()
SELECT @numeric = 1
IF @DatabaseName <> 'Master'
AND NOT EXISTS (select 1 from master..sysdatabases WHERE name = @DatabaseName AND (status & 4) = 4)
BEGIN
exec sp_dboption @databaseName ,'select into/bulkcopy', 'true'
SELECT @SetOption = 1
END
IF EXISTS (SELECT 1 FROM master..sysobjects WHERE name = 'space1')
DROP TABLE master..space1
CREATE TABLE master..Space1 (name varchar(60), rows varchar(11), reserved varchar(11), data varchar(11), index_size varchar(11), unused varchar(11))
DECLARE @Cmd varchar(255)
declare cSpace CURSOR FOR
select 'USE ' +@DatabaseName + ' INSERT into master..space1 EXEC sp_spaceUsed ''[' + u.name + '].[' + o.name + ']'''
FROM sysobjects o
join sysusers u on u.uid = o.uid
WHERE type = 'U'
AND o.Name <> 'Space1'
OPEN cSPACE
FETCH cSpace INTO @Cmd
WHILE @@FETCH_STATUS =0
BEGIN
-- PRINT @Cmd
EXECUTE (@Cmd)
FETCH cSpace INTO @Cmd
END
DEALLOCATE cSPace
SELECT Description,
Rows,
Reserved,
Data,
Index_size,
dataPerRows
FROM (
SELECT 3 DataOrder,
CONVERT(int,CASE @OrderCol WHEN 'Rows' THEN Rows
WHEN 'Reserved' THEN SUBSTRING(Reserved, 1,LEN(Reserved)-2)
WHEN 'data' THEN SUBSTRING(Data, 1,LEN(Data)-2)
WHEN 'index_size' THEN SUBSTRING(Index_size, 1,LEN(index_Size)-2)
WHEN 'unused' THEN SUBSTRING(unused, 1,LEN(unused)-2) END) OrderData,
name Description,
rows,
CASE @NUMERIC WHEN 0 THEN reserved ELSE SUBSTRING(reserved, 1, len(reserved)-2) END reserved,
CASE @NUMERIC WHEN 0 THEN data ELSE SUBSTRING(data, 1, len(data)-2) END data,
CASE @NUMERIC WHEN 0 THEN index_size ELSE SUBSTRING(index_size, 1, len(index_size)-2) END index_size,
--SUBSTRING(data, 1, len(data)-2) DataPerRows
--CONVERT(numeric(19,6),SUBSTRING(data, 1, len(data)-2)) /rows dataPerRows
CASE WHEN Rows = 0 THEN '' ELSE CONVERT(varchar(11),CONVERT(numeric(10,2),CONVERT(numeric,SUBSTRING(reserved, 1, len(reserved)-2)) /rows*1000)) END DataPerRows
FROM master..Space1
UNION ALL
SELECT 1 DataOrder, 0 OrderData,
CONVERT(varchar(30),'Total' ) Description,
CONVERT(varchar(11),SUM(CONVERT(int,Rows))) Rows,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Reserved, 1,LEN(Reserved)-2)))) Reserved,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Data, 1,LEN(Data)-2)))) Data,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(index_size, 1,LEN(Index_size)-2)))) Index_Size,
''
FROM master..space1
UNION ALL
SELECT 2, 0,
REPLICATE('-',30),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11)
UNION ALL
SELECT 4,0,
REPLICATE('-',30),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11),
REPLICATE('-',11)
UNION ALL
SELECT 5,0,
CONVERT(varchar(30),'Total' ) Description,
CONVERT(varchar(11),SUM(CONVERT(int,Rows))) Rows,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Reserved, 1,LEN(Reserved)-2)))) Reserved,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Data, 1,LEN(Data)-2)))) Data,
CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(index_size, 1,LEN(Index_size)-2)))) Index_Size,
''
FROM master..space1 ) Stuff
ORDER BY DataOrder, OrderData desc, description
EXECUTE ('DROP TABLE master..space1')
IF @SetOption = 1
exec sp_dboption @databasename ,'select into/bulkcopy', 'false'
GO
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 28, 2002 at 9:56 am
You can use SQLExecMS.
Use Tools|Space Usage in main menu.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply