ALTER PROCEDURE [dbo].[usp_ShowAllFileName]

AS

BEGIN

DECLARE @px INT = 0

DECLARE @dbname VARCHAR(30)

DECLARE @sql VARCHAR(MAX)

IF OBJECT_ID('tempdb..#dbs', 'U') IS NOT NULL

DROP TABLE #dbs

IF OBJECT_ID('tempdb..#tbfname', 'U') IS NOT NULL

DROP TABLE #tbfname

CREATE TABLE #tbfname

(

id INT IDENTITY(1, 1) ,

dbname VARCHAR(40),

fname VARCHAR(200)

)

SELECT id = IDENTITY( INT ,1,1 ),

name

INTO #dbs

FROM master.sys.databases db

WHERE db.database_id > 4

AND db.name NOT IN ( 'ReportServer',

'ReportServerTempDB' )

WHILE ( @px < ( SELECT MAX(id)

FROM #dbs

) )

BEGIN

SELECT TOP 1

@dbname = #dbs.name

FROM #dbs

SET @sql = 'use ' + @dbname

+ ' select ''' +@dbname + ''',filename from sys.sysfiles'

INSERT #tbfname EXEC ( @sql )

SET @px = @px + 1

DELETE #dbs

WHERE name = @dbname

END

SELECT *

FROM #tbfname

END