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 (http://blog.SQLAuthority.com)

About these ads

34 thoughts on “SQL SERVER – Get All the Information of Database using sys.databases

  1. useful as usual..
    I have a question, how can I change compatibility level of one database? for example improve it from 2005 to 2008?
    tnx

  2. Actually, what would be essential also is the database file locations: for a database – is the log, the data files and FT catalogs on the same disk? And for all databases as a group – are they on the same disk? Are they on the System Disk?

  3. …and now with the Denali CTP out, perhaps it’s time to add another line to the compatibility CASE statement, eh?

    Excellent script…will be adding it to my library immediately. Thanks Pinal.

  4. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #001 « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority

  6. Thank you, this is really helpful.

    Can I get information about the database creator and the host name of this database user?

  7. Pingback: SQL SERVER – Get SQL Server Version and Edition Information – SQL in Sixty Seconds #043 – Video « SQL Server Journey with SQL Authority

  8. i want to get details of pc-name or ip who is connected now with specific database, and also i need query to kill process by using query

  9. I ran the script on one of my SQL Server 2005 servers, and some of the creation dates were displayed in DD-MM-YYYY format and some in MM-DD-YYYY format. When I queried sys.databases, the create-date column values were all displayed in YYYY-MM-DD format. Can anyone explain this, and how to display all of the dates in MM-DD-YYYY format?

  10. 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

  11. Very, very informative & helpful script. Thank you, Pinal!
    I’m working on this now but is there a way to use this script to pull only info for a specific database? I’d like to implement this into a database admin tool I am writing.
    Thanks again for all the help you provide.

  12. 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

  13. 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).

  14. 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?

  15. 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?

  16. 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)

  17. 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’.

  18. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s