Sql – DB file size from master..sysaltfiles

sql serversql-server-2005tsql

I'm trying to retrieve db sizes of all databases in my SQL Server 2005 instance. I found this

select filename, size, 8192.0E * size / 1048576.0E as result
from master..sysaltfiles

Is the result varibale in KB? How can get this to MB? I'm confused.

The result must be in MB. I tried your query and have result size in MB.

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

Execute:

SELECT name FROM master.sys.databases

This the preferred approach now, rather than dbo.sysdatabases, which has been deprecated for some time.

Execute this query:

SELECT name FROM master.dbo.sysdatabases

or if you prefer

EXEC sp_databases