Database Information

MS SQL 1 Minute

USE: To display database detail information.

SELECT @@SERVERNAME [ServerName],
       MF.data_space_id,
       MF.database_id,
       MF.[type],
       MF.[type_desc],
       MF.[name] [DBName],
       MF.[physical_name] [FilePath],
       MF.[state_desc],
       CAST((MF.[size] * 8.0) / 1024.0 AS numeric(19,2)) [FileSize (MB)],
       MF.[max_size],
       MF.[growth],
       DB.[create_date],
       DB.[compatibility_level],
       DB.[collation_name],
       DB.[user_access_desc],
       DB.[state_desc],
       DB.[recovery_model]
  FROM sys.master_files MF
 INNER JOIN sys.databases DB ON MF.database_id = DB.database_id
  LEFT JOIN sys.data_spaces DS ON MF.data_space_id = DS.data_space_id
  LEFT JOIN sys.database_files DF ON DS.name = DF.name
GO

 

Published by Sal Young

Published