spAllTables

IF OBJECT_ID('dbo.spAlltables') IS NOT NULL
DROP PROCEDURE dbo.spAllTables
GO

CREATE PROCEDURE dbo.spAllTables
AS
/**************************************************************************** 
   Creation Date: 03/30/02      Created By: Randy Dyess
                              Web Site: www.TransactSQL.Com
                              Email: RandyDyess@TransactSQL.Com
   Purpose: Loops through all databases and all user tables to obtain space used
    without the use of a cursor or an undocumented stored procedure
   Location: master database
   Output Parameters: None
   Return Status: None
   Called By: None        
   Calls: None
   Data Modifications: None
   Updates: None                                                                
   Date        Author                      Purpose                                    
   ----------  --------------------------  ---------------------------------  
****************************************************************************/ 
SET NOCOUNT ON

--Declare variables 
DECLARE @strDBName SYSNAME --holds database names
DECLARE @lngDBCount INTEGER --holds database count
DECLARE @lngCounter1 INTEGER --loop counter
DECLARE @strTableName SYSNAME --holds table names
DECLARE @lngTabCount INTEGER --holds table count
DECLARE @lngCounter2 INTEGER --loop counter
DECLARE @strSQL NVARCHAR(4000) --dynamic sql string

--Create temp tables to hold database and table names
CREATE TABLE #tDBName
(
numID INTEGER IDENTITY(1,1)
,strDBName SYSNAME
)

CREATE TABLE #tTableName
(
numID INTEGER IDENTITY(1,1)
,strTableName SYSNAME
)

--Populate database names
INSERT INTO #tDBName (strDBName)
SELECT name from master.dbo.sysdatabases
--You can place an optional WHERE name NOT IN ('master','model','msdb','tempdb') here
SET @lngDBCount = @@ROWCOUNT --How many databases
SET @lngCounter1 = @lngDBCount --Preserves original count for future use

--Loop through database names
WHILE @lngCounter1 <> 0
BEGIN
--Populate database name variable
SET @strDBName = (SELECT strDBName FROM #tDBName WHERE numID = @lngCounter1)

--Populate table names
SET @strSQL = 'INSERT INTO #tTableName (strTableName)
SELECT name FROM ' + @strDBName + '.dbo.sysobjects WHERE xtype = ''u''' --only use user tables
EXEC sp_executesql @strSQL

SET @lngTabCount = (SELECT @@ROWCOUNT) --how many tables in this database
SET @lngCounter2 = @lngTabCount --Preserve table number for future use
--Header
SELECT @strDBName + ' Database'

--Loop through all tables
WHILE @lngCounter2 <> 0
BEGIN

--Populate table name variable
SET @strTableName = (SELECT strTableName FROM #tTableName WHERE numID = @lngCounter2)

--Perform action
SET @strSQL = 'EXEC ' + @strDBName + '.dbo.' + 'sp_spaceused ' + @strTableName 
EXEC sp_executesql @strSQL

--Move backward through tables
SET @lngCounter2 = @lngCounter2 - 1
END

--Move backward through databases
SET @lngCounter1 = @lngCounter1 - 1

--Clean out table name temp table
TRUNCATE TABLE #tTableName
END

DROP TABLE #tDBName
DROP TABLE #tTableName
GO

--Test
EXEC dbo.spAllTables