As part of my work, I very frequently have to collect information about the various database engine features that are currently being used on a particular SQL Server instance. Sometimes, this requires me to write T-SQL scripts to fetch the required information. I had updated my initial data collection script some time back and this gave me the idea to write up another set of T-SQL queries to fetch the information for the database engine features in use.
The script collects a bunch of information which are categorized under the following headings:
1. General Server Configuration
Server Info
Non-default sp_configure settings
Server Settings
Active Trace Flags
2. Replication Configuration
Replication Publishers
Merge Replication Publishers
Replication Subscribers
Replication Distributors
3. Full-text enabled databases
4. Linked Servers
5. SQL Agent information
6. Databases
Database information
Database file information
7. Server Triggers
8. Policy Based Management
9. Resource Governor
10. Database Mail
11. Log Shipping
12. Database Mirroring
13. SQL CLR Assemblies
14. sp_OA* procedures
Usage
- Download the script using the link given at the bottom of the page and save it to a file named SQL_DISCOVERY.SQL. Open the script file in a SSMS Query Window.
- Press CTRL+SHIFT+F so that the output results are put into a file. Doing this will not produce a message or any other notification.
- Execute the script and specify SQL_DISCOVERY.html as the output file name so that we can get the output in the require HTML format.
- Once the script is completed, open the HTML file.
If you have any feedback about the script or feel any new additions to the existing data that is being captured, please feel free to leave a comment!
Cool Script, but in SQL2005 colums like http://sys.databases.is_encrypted is not available… the script throws errors.
LikeLike
Great script. I get an error that is_system does not exist when running on 10.0.2723. It is coming from line 517 of the query.
LikeLike
The column is not available on SQL Server 2008 for information that I am fetching for Policy Based Management. Fixed that.
LikeLike
Fixed that as well. Thanks for reporting it.
LikeLike
Also found an issue with a server with 132GB of memory. It looks like your data type for memory does not support 137391288320 amount of memory.
LikeLike
I had not factored that into the script. Will correct it tomorrow.
LikeLike
The physical memory issue has been corrected.
LikeLike
Msg 207, Level 16, State 1, Line 444 Invalid column name ‘is_encrypted’. Msg 207, Level 16, State 1, Line 446 Invalid column name ‘is_cdc_enabled’.
LikeLike
It seems that you are using an older version of the script. I have corrected this issue in the latest version of the script.
LikeLike