Thanks ngreene for sharing your script. Without it this forum topic and sharing of scripts would not of happened.
SanDroid my visual tests show the results are the same for both scripts except that TravisDBA script does not handle tables with a dbo schema. I do not know why.
WILLIAM MITCHELL script shows all tables but does not display the schema because Microsoft sp_spaceused does not report it.
Last week I finished a similar script that collects row counts and table sizes for all databases in an instance. It is a bit more complicated and still needs more testing to confirm its accuracy. The code is based on other published SQL scripts for which do I give credit to in the comments. It is being tested on SQL Server 2000, 2005, 2008 and 2008 R2. I will be adding it to the SQL Overview Package[/url].
My experience in the past has shown sp_spaceused sizes can be off and the row counts from sysindexes are based on SQL Server optimization statistics that may not always be accurate.
Here is my code
USE [tempdb]
GO
-- Create Temp Table to collect data
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = OBJECT_ID(N'[tempdb].[dbo].[temp_CapturerSpaceUsed]')
)
DROP TABLE [tempdb].[dbo].[temp_CapturerSpaceUsed]
GO
CREATE TABLE [TEMPDB].[dbo].[temp_CapturerSpaceUsed](
[Server] [nvarchar](128) DEFAULT @@SERVERNAME,
[DatabaseName] [nvarchar](128) NULL,
[TableSchema] [nvarchar](50) NOT NULL,
[TableName] [nvarchar](128) NOT NULL,
[Rows_cnt] [nvarchar](32) NOT NULL,
[ReservedKB] [nvarchar](32) NOT NULL,
[DataKB] [nvarchar](32) NOT NULL,
[IndexSzKB] [nvarchar](32) NOT NULL,
[UnusedKB] [nvarchar](32) NOT NULL,
[Package_run_date] [datetime] DEFAULT CURRENT_TIMESTAMP
) ON [PRIMARY]
GO
--------------------------------------------------
-- Create Stored procedure to load data into table
IF OBJECT_ID(N'[dbo].[usp_CapturerSpaceUsed]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_CapturerSpaceUsed];
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [usp_CapturerSpaceUsed]
@DatabaseName NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;
---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- Posts used as a references in creating this stored procedure
-- http://www.novicksoftware.com/Articles/sql-server-table-space-reporting-sp_spaceused.htm
-- http://www.sqlservercentral.com/scripts/Miscellaneous/30317/
DECLARE @TableSchema NVARCHAR(50)
DECLARE @TableName NVARCHAR(600)
DECLARE @SQL NVARCHAR(2000)
DECLARE @I AS INT
DECLARE @Row_Count AS INT
SET @I = 1
-- Get list of tabes in database \IF OBJECT_ID('tempdb..#TableList') IS NOT NULL DROP TABLE #TableList
CREATE TABLE #TableList (ID_num int IDENTITY(1,1),TableSchema NVARCHAR (50),TableName NVARCHAR (128) )
INSERT INTO #TableList (TableSchema,TableName) EXEC ('SELECT TABLE_SCHEMA, TABLE_NAME FROM [' + @DatabaseName + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' ORDER BY 1,2;')
-- Create table to capture resulst from sp_spaceused
IF OBJECT_ID('tempdb..#sp_spaceused') IS NOT NULL DROP TABLE #sp_spaceused
CREATE TABLE #sp_spaceused (TableName NVARCHAR (128) ,[Rows_cnt] [nvarchar](32),[ReservedKB] [nvarchar](32),[DataKB] [nvarchar](32),[IndexSzKB] [nvarchar](32),[UnusedKB] [nvarchar](32))
SET @Row_Count = (SELECT COUNT(*) FROM #TableList)
-- Cursorless loop
WHILE @I <= @Row_Count
BEGIN
-- The replace is used to handle table names with apostophes
SELECT @TableSchema = TableSchema, @TableName = REPLACE(TableName,'''','''''')
FROM #TableList WHERE ID_Num = @I;
-- Get sp_spaceused data
SET @SQL = '[' + @DatabaseName + ']..sp_spaceused @objname=''[' + @TableSchema + '].[' + @TableName + ']''';
-- SELECT @SQL
DELETE FROM #sp_spaceused ;
INSERT INTO #sp_spaceused EXEC (@SQL);
-- Save sp_spaceused data with server, database, and table schema
INSERT INTO [tempdb].[dbo].[temp_CapturerSpaceUsed]
([Server]
,[DatabaseName]
,[TableSchema]
,[TableName]
,[Rows_cnt]
,[ReservedKB]
,[DataKB]
,[IndexSzKB]
,[UnusedKB]
,[Package_run_date])
SELECT @@SERVERNAME, @DatabaseName, @TableSchema, TableName
,[Rows_cnt],[ReservedKB],[DataKB],[IndexSzKB],[UnusedKB], current_timestamp
FROM #sp_spaceused;
SET @I = @I + 1
END
DROP TABLE #TableList
DROP TABLE #sp_spaceused
END
GO
--------------------------------------------------
-- Process each database
SET NOCOUNT ON
DECLARE @DBName NVARCHAR(128)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name <> 'TEMPDB'
AND CONVERT(nvarchar(128),DATABASEPROPERTYEX(name,'Status')) = 'ONLINE'
ORDER BY name
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [usp_CapturerSpaceUsed] @DatabaseName = @DBName
FETCH NEXT FROM db_cursor
INTO @DBName
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
--------------------------------------------------
-- Cleanup
USE tempdb
GO
IF OBJECT_ID(N'[dbo].[usp_CapturerSpaceUsed]') IS NOT NULL
DROP PROCEDURE [dbo].[usp_CapturerSpaceUsed];
GO
SELECT [Server]
,[DatabaseName]
,[TableSchema]
,[TableName]
,CAST ([Rows_cnt] AS decimal(18, 0)) AS [Rows_cnt]
,CAST (REPLACE([ReservedKB], ' KB','') AS decimal(18, 0)) AS [ReservedKB]
,CAST (REPLACE([DataKB], ' KB','') AS decimal(18, 0)) AS [DataKB]
,CAST (REPLACE([IndexSzKB], ' KB','') AS decimal(18, 0)) AS [IndexSzKB]
,CAST (REPLACE([UnusedKB], ' KB','') AS decimal(18, 0)) AS [UnusedKB]
,[Package_run_date]
FROM [tempdb].[dbo].[temp_CapturerSpaceUsed]
ORDER BY [Rows_cnt] DESC
David Bird