Month: June 2011

This is a replacement for the undocumented sp_MSforeachdb system stored proc.

USE [master];
GO

CREATE PROCEDURE dbo.sp_foreachdb
@command NVARCHAR(MAX),
@replace_character NCHAR(1) = N’?’,
@print_dbname BIT = 0,
@print_command_only BIT = 0,
@suppress_quotename BIT = 0,
@system_only BIT = NULL,
@user_only BIT = NULL,
@name_pattern NVARCHAR(300) = N’%’,
@database_list NVARCHAR(MAX) = NULL,
@recovery_model_desc NVARCHAR(120) = NULL,
@compatibility_level TINYINT = NULL,
@state_desc NVARCHAR(120) = N’ONLINE’,
@is_read_only BIT = 0,
@is_auto_close_on BIT = NULL,
@is_auto_shrink_on BIT = NULL,
@is_broker_enabled BIT = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE
@sql NVARCHAR(MAX),
@dblist NVARCHAR(MAX),
@db NVARCHAR(300),
@i INT;

IF @database_list > N”
BEGIN
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.name) – 1
FROM sys.objects AS s1
CROSS JOIN sys.objects AS s2
)
SELECT @dblist = REPLACE(REPLACE(REPLACE(x,”,’,’),
”,”),”,”)
FROM
(
SELECT DISTINCT x = ‘N”’ + LTRIM(RTRIM(SUBSTRING(
@database_list, n,
CHARINDEX(‘,’, @database_list + ‘,’, n) – n))) + ””
FROM n WHERE n

use master
go

CREATE PROCEDURE sp_GetAllTableSizes
AS
DECLARE @tabname varchar(128)

–Cursor to get the name of all user tables from the sysobjects listing
DECLARE table_cur CURSOR FAST_FORWARD FOR
SELECT [name]
FROM dbo.sysobjects (NOLOCK)
WHERE OBJECTPROPERTY(id, N’IsUserTable’) = 1

–A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(128),
numberofRows int,
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

–Open the cursor
OPEN table_cur

–Get the first table name from the cursor
FETCH NEXT FROM table_cur INTO @tabname

–Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
–Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @tabname

–Get the next table name
FETCH NEXT FROM table_cur INTO @tabname
END

–Get rid of the cursor
CLOSE table_cur
DEALLOCATE table_cur

–Select all records so we can use the reults
SELECT *
FROM #TempTable
ORDER BY numberofRows DESC

–Final cleanup!
DROP TABLE #TempTable

GO