SQL SERVER – Get All the Information of Database using sys.databases

Earlier I wrote blog article SQL SERVER – Finding Last Backup Time for All Database. In the response of this article I have received very interesting script from SQL Server Expert Matteo as a comment in the blog. He has written script using sys.databases which provides plenty of the information about database.

I suggest you can run this on your database and know unknown of your databases as well.

SELECT database_id,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
state_desc,
(
SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(
SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(
SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(
SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
user_access_desc AS [User access],
recovery_model_desc AS [Recovery model],
CASE compatibility_level
WHEN 60 THEN '60 (SQL Server 6.0)'
WHEN 65 THEN '65 (SQL Server 6.5)'
WHEN 70 THEN '70 (SQL Server 7.0)'
WHEN 80 THEN '80 (SQL Server 2000)'
WHEN 90 THEN '90 (SQL Server 2005)'
WHEN 100 THEN '100 (SQL Server 2008)'
END AS [compatibility level],
CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
-- last backup
ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],
CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
page_verify_option_desc AS [page verify option],
CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown] FROM sys.databases DB
ORDER BY dbName, [Last backup] DESC, NAME

Please let me know if you find this information useful.

Watch a 60 second video on this subject

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – SQL Server Denali CTP1 – Release Date November 9, 2010
Next Post
SQL SERVER – Reducing CXPACKET Wait Stats for High Transactional Database

Related Posts

40 Comments. Leave new

  • Hi Sir,

    Im given a task of finding the SQL version of 400 + servers. Im frustrated. Is there any script to do so. Please just accordingly

    Reply
  • Steve Walker
    July 22, 2013 5:30 am

    Very helpful. Thank you. What is the easiest way to add server name to this script and then send it to a central database for tracking and metrics? Need to run this daily on 25 servers (1000+ DBs).

    Reply
  • Hi! Pinal,

    For some databases, the name in sys.databases and sys.master_files is different.
    We know because the database_id is same.
    What does that mean?
    [We are using those databases daily for over 6 months and apparently there are no issues in performance or use]
    Is there a way to make them same?

    Reply
  • Very nice.. add WHEN 110 THEN ‘110 (SQL Server 2012)’ if you have 2012.. one thing I noticed was that sys.master_files is empty on my server and another I checked. Any ideas?

    Reply
  • If I use the SQLCMD -L command to generate a list of all servers on my network …
    and than execute your code about to extract data about each database …
    how can i modify the code to execute on each of the servers in my list (loop through them) –
    so i could connect to the sys.databases on each server on my network (form my ITIL CMDB)

    Reply
  • I have SQL 2000 and I just want to find out if a database is in “single user” mode …. how can I do this? Can you help?

    I ran your script above and I’m getting a lot of errors:
    Server: Msg 208, Level 16, State 1, Line 5
    Invalid object name ‘sys.master_files’.
    Server: Msg 208, Level 16, State 1, Line 5
    Invalid object name ‘sys.master_files’.
    Server: Msg 208, Level 16, State 1, Line 5
    Invalid object name ‘sys.master_files’.
    Server: Msg 208, Level 16, State 1, Line 5
    Invalid object name ‘sys.master_files’.
    Server: Msg 208, Level 16, State 1, Line 5
    Invalid object name ‘sys.databases’.

    Reply
  • My SQL 2000 keeps changing to “single user” mode on it’s own =(

    How can I detect this using a stored procedure so I can fix it? Please help.

    Reply
  • little remark – better to use conversion of the db name to NVARCHAR, because, if name is in unicode – the result will be ‘?????’ or similar

    Reply
  • Thanks for sharing this query. I just curious to know how I can retrieve all Database name in dbName column and also if I want that record in excel sheet what would I need to do.
    I know one way of doing this “Right click on result window , select the records and and select ‘save a result as’ option” . Could you let me know the other way of doing this.
    Please…….. let me know. That would be great help

    Reply
  • Mubashar Anwer
    August 17, 2015 2:59 pm

    V useful and informative Script dear pinal Dave Sir!

    Reply
  • HI Pinal,

    I am getting the error message as below:
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.master_files’.
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.master_files’.
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.master_files’.
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.master_files’.
    Msg 208, Level 16, State 1, Line 1
    Invalid object name ‘sys.databases’.

    Please guide me to get this rectified

    Reply
  • Hi,

    How would you display this instance name if you have multiple SQL instances. I am writing a backup utility and have two instances, and I show these as two nodes in a treeview, then I use a different connection string for each instance, but both nodes on the treeview are populated with the list of databases from instance 1.

    I run “select name from sys.databases” for each node with the connection string reflecting this, but it doesnt work.

    I would have like to run something like “select name from sys.databases WHERE [instance name] = instance 1

    Any suggestions please?

    Dave

    Reply
    • Found the solution – I needed to call GC.Collect to clear all disposed variables – it was loading the same database names into both instances on my treeview :)

      Reply
  • Devesh Pandey
    March 23, 2017 6:26 pm

    Hi Pinal,

    Can i have SQL Query which provide me Database Structure of particular DB with all details like (datatype, size, index, primary key etc)

    Regards,
    Devesh

    Reply

Leave a Reply

Menu