SQL Tips

Need to manage a lot of Databases on various different drives?

Use this query to get information re. Databases on your SQL Server:

select db.dbid
,db.name
,db.filename as DBFileName
,logFile.filename as LogFileName
,convert(decimal(18,2),(db.size*8)/1024.0) as DBSize
,convert(decimal(18,2),(logFile.size*8)/1024.0) as LogFileSize
,d.compatibility_level
,d.recovery_model_desc
From (select dbid,name,filename,size
from sys.sysaltfiles safDBFiles
where safDBFiles.groupid>0) db,
(select dbid,name,filename,size
from sys.sysaltfiles safLogFiles
where safLogFiles.groupid = 0) logFile,
sys.databases d
where logFile.dbid = db.dbid
and
d.database_id = db.dbid