SQL Server Elencare tutti i file di tutti i DB
04 novembre 2014
Script T-SQL per elencare tutti i file di tutti i DB
SQL
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