Monday, July 23, 2012

Query to find Database Details in SQL Server 2005 & 2008

Here's the query which I am using.


select Name, SUSER_SNAME(owner_sid) as Owner, create_date,

state_desc, [Total Size MB], recovery_model_desc,

compatibility_level, collation_name, user_access_desc

from sys.databases MST

inner join (select b.name [LOG_DBNAME],

CONVERT(DECIMAL(10,2), sum(CONVERT(DECIMAL(10,2), (a.size * 8))/1024)) [Total Size MB]

from sys.sysaltfiles A

inner join sys.databases B on A.dbid= B.database_id

group by b.name) AA on AA.[LOG_DBNAME] = MST.name

order by owner


MvM

No comments:

Post a Comment