Data and log file iteration

— 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

About Spradlike

Husband and father, epilepsy survivor, avid percussionist, technology enthusiast...insisting on the road less traveled.
This entry was posted in SQL, Tools and Tricks and tagged . Bookmark the permalink.