SQL Puzzle | Get DB Name,LogFile Size, MDB File Size and Owner from System Tables

In this puzzle you have to write a query to fetch database_name, the size of the log file, mdb file and the owner of the database using system tables.

Sample Input

NA…

Expected Output

Script

Use below script to create table and insert sample data into it.

--

NA

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--


SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))   
    , DB_Size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
	, r.[Owner]
FROM sys.master_files m
CROSS APPLY
(
	SELECT 
	suser_sname( owner_sid ) [Owner],
	name DBName 
	FROM sys.databases t
	WHERE t.name = DB_NAME(m.database_id)
)r
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id,r.[Owner]


--

Output

/*------------------------
SELECT 
      database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2))   
    , DB_Size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
	, r.[Owner]
FROM sys.master_files m
CROSS APPLY
(
	SELECT 
	suser_sname( owner_sid ) [Owner],
	name DBName 
	FROM sys.databases t
	WHERE t.name = DB_NAME(m.database_id)
)r
WHERE database_id = DB_ID() -- for current db 
GROUP BY database_id,r.[Owner]
------------------------*/
database_name                                                                                                                    log_size_mb                             DB_Size_mb                              Owner
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
SAP_INT                                                                                                                          8.00                                    80.00                                   xxx\pawan
Warning: Null value is eliminated by an aggregate or other SET operation.

(1 row(s) affected)



--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com