SQL Server Elencare tutti i file di tutti i DB

SQLCopia

DECLARE @dbname VARCHAR(150) 
DECLARE @strSQL nvarchar(200)

DROP TABLE  #SgartTbTmp  
CREATE TABLE #SgartTbTmp (
	[DB] VARCHAR(150) not null,
	[Type] VARCHAR(50) not null,
	[Path] VARCHAR(1500) not null,
	[State] VARCHAR(50) not null,
	[Size] float not null
)
DECLARE dbcursor CURSOR FOR
SELECT [name]
   FROM master.sys.databases
  
OPEN dbcursor FETCH NEXT FROM dbcursor INTO @dbname 
WHILE @@FETCH_STATUS = 0 
BEGIN
  SET @strSQL = 'INSERT INTO #SgartTbTmp '
	  +' SELECT ''' + @dbname + ''' AS DB, type_desc, physical_name, state_desc, size FROM [' + @dbname + '].sys.database_files'
  EXEC SP_EXECUTESQL @strSQL
  FETCH NEXT FROM dbcursor INTO @dbname
END
  
CLOSE dbcursor
DEALLOCATE dbcursor
 
SELECT * 
FROM #SgartTbTmp