Recently building a system and preparing to handover the system to a colleague who will be completing the build they asked how they SQL Server databases were configured for the transaction logs, compatibility levels and other things before before other data was loaded in.
To answer questions on that regard you could go through each database one-by-one in SQL Server Management Studio to check the configuration but that’s going to get fairly tedious really fast.
As I’ve said before it’s often best to try and script things out to save them for later rather than poking through the GUIs all the time so this is where building something you can repeatedly use comes in. In this instance the view sys.databases is a really useful one to know.
I’ve built myself a T-SQL script that I use to bring out useful information I can then copy out for documentation or just a quick check of the databases. This script is available in my GitLab repo for you to try. My recommendation would be to modify to your requirements or be an inspiration to build your own.
Your SQL login needs to have ALTER ANY DATABASE, VIEW ANY DATABASE or CREATE DATABASE permission at server level or be in the SYASADMIN server role. If you call the view any database you have the db_owner role over will appear too.
This script I use to give a quick check following building a new system. Just an an example it’s usually a really good idea to have AUTO CLOSE and AUTO SHRINK set to OFF on production databases. Sometimes clients might set these on as they sound like a good idea just by reading them. With sys.databases this is an example of option you can check to see what’s configured for either general checks or troubleshooting purpose.
My invitation to you is to check out my script, leave me a few comments and have fun building your own.
