User Tables with Size all DBs (SQL 2005 Only)
Hope this is helpful to someone. This script will create a table and a stored procedure in the master database to gather and store table info for all datbases. The sp uses a temp table to hold the results returned by sp_spaceused, then transfers the information to the permanent table DatabaseTableSizes and adds the database name so you can query it later for any db. Feel Free to Improve. Only works with 2005 right now, too lazy to modify and post 2000 version, for now!
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
--*************************************************************************
-- Create a table to hold Database Table Size Information
-- This table will be used to hold the results of the stored procedure
-- usp_DBA_DatabaseTableSizes
-- *************************************************************************
CREATE TABLE [dbo].[DatabaseTableSizes](
[DatabaseName] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Rows] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Reserved] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Index_Size] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Unused] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LogTime] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--*************************************************************************
Use Master
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--*************************************************************************
-- Author:
-- Date: 10/11/2006
-- When this procedure is run, the results will be stored in the table
-- DatabaseTableSizes in the Master Database.
-- Usage: exec sp_DBA_DatabaseTableSizes
-- I would recommend that this be set up as a job to run on a daily or weekly
-- basis so you can trend some of your information
--*************************************************************************
Create Procedure sp_DBA_DatabaseTableSizes
as
Begin
CREATE TABLE #DatabaseTableSizes(
[Name] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Rows] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Reserved] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Data] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Index_Size] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Unused] [varchar](18) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LogTime] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]
exec sp_msforeachdb @command1='if ''?'' <> ''tempdb'' use ?;
EXEC sp_MSForEachTable @command1=''INSERT INTO
#DatabaseTableSizes
([name],[rows],[reserved],[data],[index_size],[unused])
EXEC sp_spaceused ''''%'''' '', @replacechar=''%'';
Insert into master..DatabaseTableSizes (DatabaseName, Name, Rows, Reserved, Data,Index_Size, Unused)
Select ''?'', Name, Rows, Reserved, Data, Index_Size, Unused from #DatabaseTableSizes;
Truncate Table #DatabaseTableSizes; '
Drop Table #DatabaseTableSizes
End
GO
