Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

database_name log_size_mb DB_Size_mb Owner
SAP_INT 8.00 80.00 xxx\pawan

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

Advertisements