GET THE LIST OF ALL DATABASES/DATABASE SIZE (T-SQL)

There are so many situations when you need to know what is actually happening on your SQL server instances, especially which databases your are hosting at your servers.

It sounds like an easy task for everyone who is familiar with SQL Management Studio: Expand your server instance in Object explorer --> Expand "Databases folder". This is what I can see on my test instance:

Sometimes, that's not enough because you are interested not just in database list, but in more advanced properties. There is a GUI for that as well if you click on individual database and selecting "Properties" you will get enough for your admin purposes. Here is another approach which will give you all databases with extended properties in just one query using system catalog views:

SELECT * FROM sys.databases

For better understanding of system catalog views check for online help:
http://technet.microsoft.com/en-us/library/ms190334.aspx


This looks like raw data report about individual data properties, but be free to select those which you are the mostly interested in and that's what exactly I did here adding few of them together with databases sizes. Database size is not located in sys.databases catalog and for this purpose we will need to join it with sys.master_files view. Firstly, you can check which columns are included in these 2 views and here are simple queries:

SELECT * FROM sys.databases
GO

SELECT * FROM sys.master_files

The following queries can be used as a good DBA tool for getting info about database statuses and data files:

/*CTE function*/
WITH DbSize
AS
  (
   SELECT
     database_id,
     [type],
     Size = size * 8.0 / 1024
FROM sys.master_files
)

/*Get database list together with database size ordered by db size descending*/
SELECT
    database_id,
    name,
    create_date,
    [compatibility_level],
    collation_name,
    state_desc,
    [recovery_model_desc],
    DataFileSizeInMB = (SELECT SUM(size) FROM DbSize WHERE TYPE = 0 and DbSize.database_id = db.database_id),
    LogFileSizeInMB = (SELECT SUM(size) FROM DbSize WHERE TYPE = 1 and DbSize.database_id = db.database_id)
FROM sys.databases db
ORDER BY DataFileSizeInMB DESC

My recommendation for using the script from above is maybe to create SQL Services online report, so you as a DBA can always go there and see what's happening with your databases. Also, these queries can be adjusted with other DB properties.


Mike C.