Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Capturing Filesizes for all Databases

Author  Topic 

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-09 : 12:45:21
There have been a lot of posts recently in this forum and others so I figured I'd record the script here and then point others to it in the future. If you are interested in retaining the information for posterity to chart growth in your database and then showing the numbers at say your next review to show how much they need you, you can run the initial script that will create the table. Once the table is in place you can run the script to get the data from all databases, and then query the table. If you don't need a history of the sizes and just want to run the query one time then just run the last line of the script "EXEC":

TABLE CREATION:[CODE]Create table DatabaseSizes (dbname varchar(255), fileid int, filename varchar(255), name varchar(255), size_mb int, reporting_date datetime)[/CODE]
POPULATE THE TABLE:[CODE]insert into DatabaseSizes(dbname, fileid, filename, name, size_mb, reporting_date)
exec master.dbo.sp_MSforeachdb 'use ? select db_name() as dbname, fileid, filename, name, size/128, getdate() from sysfiles'[/CODE]


Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."
   

- Advertisement -