Exploring SQL Server Databases

As a SQL Server user, it’s important to have a good understanding of your databases and their properties. In a previous article, we discussed how to find the last backup time for all databases. Today, we will explore a script shared by SQL Server expert Matteo that provides a wealth of information about your databases.

Let’s take a look at the script:

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

This script uses the sys.databases system view to retrieve various information about each database in your SQL Server instance. It provides details such as the database name, status, state, number and size of data and log files, user access, recovery model, compatibility level, creation date, last backup information, and various database options.

By running this script on your database, you can gain insights into the characteristics and properties of your databases. It can be particularly useful for database administrators and developers who need to monitor and manage multiple databases.

For example, you can use this script to identify databases that haven’t been backed up recently, check the size of data and log files, and review various database options that might impact performance and security.

Remember to replace the sys.databases view with the appropriate database name if you want to retrieve information for a specific database.

Overall, this script provides a comprehensive overview of your SQL Server databases, allowing you to better understand and manage them.

I hope you find this information useful in your SQL Server journey. Stay tuned for more articles on SQL Server concepts and best practices!

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on

Exploring SQL Server Databases

Let's work together