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