Get All SQL DB Files and size (oneshot)

--// Get all your db files info and then slice and dice to automate whatever task you need. hmm, sp_detach_db --> xp_cmdshell to move the files --> sp_attach_db. Eh, just a thought.

IF OBJECT_ID('tempdb..##TempDBInfo') IS NOT NULL
BEGIN
PRINT 'Previous ##TmpDBInfo found and Dropped.'
DROP TABLE ##TempDBInfo
END
CREATE TABLE ##TempDBInfo(DBName nVarChar(255), FileName nvarchar(255), fileSize INT)
EXEC sp_MSforeachdb 'USE [?] INSERT INTO ##TempDBInfo SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
SELECT * FROM ##TempDBInfo
--// DROP TABLE ##TempDBInfo

--// Thanks mssqltips

Comments :

0 comments to “Get All SQL DB Files and size (oneshot)”