Lookup All File Sizes, Status, and FileName for all Databases

This script can help you find some details in all databases.

USE MSDB
go
IF EXISTS(SELECT NULL FROM msdb..sysobjects where name = 'DBFileSizes' AND type ='U')
     TRUNCATE TABLE dbo.DBFileSizes
ELSE
CREATE TABLE dbo.DBFileSizes(
     [DBname] nvarchar(128) NULL,
     [fileid] [smallint] NULL,
     [groupid] [smallint] NULL,
     [size] [int] NOT NULL,
     [maxsize] [int] NOT NULL,
     [growth] [int] NOT NULL,
     [status] [int] NULL,
     [perf] [int] NULL,
     [name] [sysname] NOT NULL,
     [filename] [nvarchar](260) NOT NULL )
EXEC master..sp_msforeachdb "INSERT INTO msdb.dbo.DBFileSizes
SELECT '?' AS
[DBname], * FROM [?]..[sysfiles]"
SELECT * 
     FROM dbo.DBFileSizes
DROP TABLE dbo.DBFileSizes

imagem

Advertisements

Danilo Braga is a Database Administrator with over nine years of experience. He is a Microsoft Certified IT Professional (MCITP) Database Administrator and Developer. His experience includes solutions for management, assessment and implementation of SQL Server environments making use of best practices, performance tuning, and high availability.

Tagged with: , ,
Posted in Scripts, SQL Server Tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: