1
Vote

sys2.stp_get_databases_space_used_info and the sp_MSForEachDB unreliability

description

Because of the sp_MSForEachDB unreliability (for example when there are a lot of databases) I would suggest you to remove it from the "sys2.stp_get_databases_space_used_info", replacing it with a more reliable one, like the following SP
 
CREATE PROCEDURE sys2.stp_for_each_db
@command NVARCHAR(MAX),
@replace_character NCHAR(1) = N'?'
AS
BEGIN
SET NOCOUNT ON;
 
DECLARE @sql    NVARCHAR(MAX);
DECLARE @db     NVARCHAR(300);
 
DECLARE c CURSOR 
    LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR 
        SELECT QUOTENAME(name) AS db
        FROM sys.databases 
        WHERE state_desc = 'ONLINE'
        ORDER BY db;
 
OPEN c;
 
FETCH NEXT FROM c INTO @db;
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sql = REPLACE(@command, @replace_character, @db);
 
   EXEC sp_executesql @sql;
 
   FETCH NEXT FROM c INTO @db;
END
 
CLOSE c;
DEALLOCATE c;
END
GO
 
Cheers!

comments