Wednesday, 20 February 2013

List Datafiles names , Locations and Size of all instance Databases (best script ever)

create table #temp (Databasename varchar(100),name varchar(1000),fileid int , filename varchar(500),filegroup varchar(100) ,
                    size varchar(200),maxsize varchar(300),growth varchar(100),usage varchar(100))

declare @database varchar(100)
Declare c cursor for select name  from master..sysdatabases where dbid not in (13,14)
open c
fetch next from c into @database
while @@fetch_status=0
exec('use '+@database +'
insert into #temp(name ,fileid , filename,filegroup ,
                    size ,maxsize ,growth ,usage )
 exec sp_helpfile')
update #temp set Databasename=@database where Databasename is null
fetch next from c into @database
close c
deallocate c

update #temp set size=replace(size,'KB','')
select * from #temp
select 'Sql Server',rtrim(convert(varchar(100),a.Filename))+'&'+left(replace(replace(b.filename,'d:\Program Files\Microsoft SQL Server\MSSQL\',''),'data\',''),26)
,a.DatabaseName,CONVERT(char(20), SERVERPROPERTY('servername'))
  from #temp a inner join (select * from #temp where usage<>'data only') b
on a.databasename=b.databasename
where a.usage='data only' order by 1

drop table #temp

No comments:

Post a Comment