Listing 1: Example T-SQL Code That Works Like a USE Statement

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'sp_logfiles' 
   AND objectproperty(id, N'IsProcedure') = 1)
      DROP PROCEDURE [dbo].[sp_logfiles]
GO
CREATE PROCEDURE [dbo].[sp_logfiles]
AS
SET nocount ON 
DECLARE @db     sysname,
                        @stmt nvarchar(4000)
CREATE TABLE #files
(
            [dbid]               [smallint]           NOT NULL,
            [dbname]          [sysname]         NOT NULL,
            [fileid]   [smallint]           NOT NULL,
            [groupid]           [smallint]           NOT NULL,
            [size]                [int]                   NOT NULL,
            [maxsize]          [int]                   NOT NULL,
            [growth]            [int]                   NOT NULL,
            [status] [int]                   NOT NULL,
            [perf]                [int]                   NOT NULL,
            [name]              [sysname]         NOT NULL,
            [filename]          [nvarchar](260) NOT NULL
)
DECLARE db_cursor cursor read_only FOR SELECT name 
   FROM master.dbo.sysdatabases
OPEN db_cursor
FETCH NEXT FROM db_cursor into @db
WHILE (@@fetch_status <> -1)
BEGIN
            SELECT @stmt = 'SELECT [dbid] = db_id(' + quotename
               (@db, '''') + '), [dbname] = ' + quotename(@db, '''') 
               + ', [fileid], [groupid], [size], [maxsize], [growth], [status], 
               [perf], [name], [filename] FROM ' + @db + '.dbo.sysfiles 
               WHERE status & 0x40 = 0x40'
--          SELECT @stmt
            INSERT INTO #files EXEC (@stmt)
            FETCH NEXT FROM db_cursor INTO @db
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT * FROM #files
GO
EXEC sp_logfiles
GO