List out all Database sizes

[Ashoksql]

can any one tell me to find out the list of databases that have “.ldf” size greater than “.mdf”.

[mail2saion]

FIRST RUN THE COMPLETE SQL THEN CHECK THE OUTPUT. THEN GO FOR MODIFICATION.

Create Table ##helpfile
(dbname varchar(100),
name varchar(100),
FileId int,
Filename varchar(100),
Filegroup varchar(50),
Size varchar(20),
maxsize varchar(20),
growth varchar(20),
usage varchar(20))

Create Table ##filestats
(Fileid int,
FileGroup int,
TotalSpace int,
UsedSpace int,
FreeSpace int,
Name varchar(100),
FileName varchar(100))

Exec sp_MSForeachDB

@command1 = 'Use ?; DBCC UPDATEUSAGE(0)',
@command2 = 'Use ?;Insert ##helpfile (name, fileid, filename, filegroup,
size, maxsize, growth, usage) Exec sp_helpfile; update ##helpfile set
dbname = ''?'' where dbname is null',
@command3 = 'Use ?;Insert ##filestats (Fileid, FileGroup, TotalSpace,
UsedSpace, Name, FileName) exec (''DBCC SHOWFILESTATS WITH TABLERESULTS'')'

Update ##filestats set totalspace = totalspace*64/1024, usedspace =
usedspace*64/1024
Update ##filestats set freespace = totalspace - usedspace

--cube
Select dbname, UPPER(Left(filename,1)) as [Drive Letter],
count(Left(filename,1)) AS [Count of DB Files],
sum (Cast(replace(size,' KB', '')as int)) As [Total OS File Size],
Usage from ##helpfile group by dbname, usage, UPPER(Left(filename,1))
with cube
order by dbname, [Drive Letter] , Usage desc

--filestats
Select * from ##filestats

-- add in the usedextents to the helpfile info
select a.dbname,
a.filename,
Cast(replace(a.size,' KB', '')as int)/1024 as DatafilesizeMB,
b.usedspace as UsedSpaceMB,
b.freespace as FreeSpaceMB
from ##helpfile a, ##filestats b where a.filename = b.filename

Drop table ##helpfile
Drop table ##filestats

[Ashoksql]

Declare @dbfiles table ( 
  DBNAME    VARCHAR(50),
  Fileid    int, 
  Name   VARCHAR(50), 
  Size_MB int,
Filename varchar(max)) 

INSERT INTO @dbfiles
exec sp_msforeachdb 'SELECT ''?'' AS DBNAME,fileid, [Name], Size * 8 / 1024 As Size_MB,Filename FROM ?.sys.sysfiles'

select *into #mytablevar1 from @dbfiles where filename like '%mdf' 
select *into #mytablevar2 from @dbfiles where filename like '%ldf' 

select a.dbname,a.size_mb as Data_SizeMB,b.size_mb as Log_SizeMB,a.filename as Filename_Data,b.filename as Filename_log
 into #sp_logfiles from #mytablevar1 as a inner join #mytablevar2 as b on a.dbname=b.dbname and a.size_mb<b.size_mb

select *from #sp_logfiles

--By
--Ashok

[mikemsq]

Try: Exec sp_MSForeachDB @command1="use [?]; EXEC sp_spaceused"