——————————————————————–
— This will generate a list of databases to iterate through and pull
— data and log file locations, plus size, etc.
— There are 2 parts. 1 generates the list and 2 creates the output.
——————————————————————–
/* PART 1 */ — Run this part and select all rows to paste below.
set nocount on
declare @dblist table — Declare table VAR
(
–dbid int identity (1,1),
database_name varchar(100),
database_size int,
remarks varchar(200)
)
insert into @dblist exec sp_databases
select ‘USE ‘ + database_name + ‘ insert into @dataFileList exec sp_helpfile’
from @dbList
/* PART 2 */
declare @dataFileList table — Declare table VAR
(
name varchar(100),
fileid int,
[filename] varchar (500),
filegroup varchar(100),
size varchar(255),
maxsize varchar(50),
growth varchar(100),
usage varchar(25)
)
— ADD output from PART1 here:
— EX: USE Database insert into @dataFileList exec sp_helpfile
select * from @dataFileList
