Table Space Allocation & Usage Proc

[btturner]

Need a suggestion (or prewritten proc/sql) to render all table names and their corresponding space allocations and usage for tables in a given Database.

This renders all user table info:

select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name

Does anyone have anything in their vast bag of tricks??

Much appreciate in advance!

 

[btturner]

--Declare/Open/Fetch/Close/Deallocate CURSOR sample
declare @UserTableName nvarchar(40)
declare UserTableSize cursor for
select rtrim(name) from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name
open UserTableSize
fetch next from UserTableSize into @UserTableName
while @@fetch_status = 0
begin
exec sp_spaceused @UserTableName
fetch next from UserTableSize into @UserTableName
end
-- Clean up cursor
close UserTableSize
deallocate UserTableSize

 

[dbomrrsm]

IF EXISTS (select * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tablestats]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[tablestats]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
CREATE PROCEDURE tablestats AS
SET nocount ON
DECLARE @spt_space TABLE
(
	tbname nvarchar(25),
rows int null,
	reserved dec(15) null,
	data dec(15) null,
	indexp dec(15) null,
	unused dec(15) NULL
)
DECLARE @objname nvarchar(776)
DECLARE @id int			
DECLARE @type character(2)
DECLARE	@pages int		
DECLARE @dbsize dec(15,0)
DECLARE @logsize dec(15)
DECLARE @bytesperpage dec(15,0)
DECLARE @pagesperMB dec(15,0)
DECLARE @row int
DECLARE TBLIST CURSOR
READ_ONLY
FOR SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE'
INSERT INTO @spt_space (tbname) SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE'
OPEN TBLIST
FETCH NEXT FROM TBLIST INTO @objname
WHILE (@@fetch_status <> -1)


     BEGIN
     IF (@@fetch_status <> -2)


         BEGIN
         SELECT @id = NULL
         SELECT @id = id, @type = xtype FROM sysobjects WHERE id = object_id(@objname)
         SELECT @pages = sum(dpages) FROM sysindexes WHERE indid < 2 AND id = @id
         SELECT @pages = @pages + isnull(sum(used), 0) FROM sysindexes WHERE indid = 255 AND id = @id
         SELECT @row = i.rows FROM sysindexes i WHERE i.indid < 2 AND i.id = @id
         INSERT INTO @spt_space (reserved) SELECT sum(reserved) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id
         UPDATE @spt_space SET data = @pages WHERE tbname=@objname
         UPDATE @spt_space SET indexp = (select sum(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) where tbname=@objname
         UPDATE @spt_space SET unused = reserved - (select sum(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) where tbname=@objname
         UPDATE @spt_space SET rows = @row WHERE tbname=@objname
         UPDATE @spt_space SET reserved = (select sum(reserved) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) where tbname=@objname
         UPDATE @spt_space SET indexp = (select sum(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) - data where tbname=@objname
         UPDATE @spt_space SET unused = reserved - (select sum(used) FROM sysindexes WHERE indid in (0, 1, 255) AND id = @id) where tbname=@objname
     END
     FETCH NEXT FROM TBLIST INTO @objname
 END
CLOSE TBLIST
DEALLOCATE TBLIST
SELECT tbname,
 rows = convert(char(11), rows),
 reserved = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
 data = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
 index_size = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
 unused = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
FROM @spt_space, master.dbo.spt_values d
WHERE d.number = 1 AND d.type = 'E' and tbname IS NOT NULL
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GRANT EXECUTE ON [dbo].[tablestats] TO [public]
GO

[blue]DBomrrsm[/blue]