MyTechMantra.com
Connect With MyTechMantra.com

Like Us on Facebook    Follow Us on Twitter     Subscribe to our Feeds     Subscribe to NewsLetter












































SQL Server: Different Ways to Retrieve SQL Server Configuration Details

Read Comments   |   Related Topics: SQL Server Replication Tips > Next Topic: SQL Server Indexing Articles & Tips


Introduction

There are often times when as a DBA you need to quickly retrieve SQL Server Configuration Details for a SQL Server Instance. In this article we will explore different ways to retrieve SQL Server configuration details using TSQL queries.

Method 1: Retrieve SQL Server Configuration Details Using SP_CONFIGURE System Stored Procedure

Using SP_CONFIGURE system stored procedure one can retrieve SQL Server configuration details.

/* Enable Show Advanced Options */
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

/* Retrieve SQL Server Configuration Information */
EXEC sp_configure
GO

/* Disable Show Advanced Options */
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
SP_CONFIGURE Results Show Advanced Options

Related Tips

Configure Network Drive Visible for SQL Server during Backup and Restore Using SSMS
How to Export records from SQL Server to Text File using BCP
How to Change the Default SQL Server Backup Folder in SQL Server
How to Move TempDB to New Drive in SQL Server
How to Enable & Disable XP_CMDSHELL using SP_CONFIGURE

Method 2: Retrieve SQL Server Configuration Details using sys.configurations Server-wide Configuration Catalog View

Using SP_CONFIGURE system stored procedure one can retrieve SQL Server configuration details.

Use master
GO

SELECT * FROM sys.configurations
GO
sys.configurations  Results

Thank you for taking your time to read this article. Let's be Connected....

Sign-up for Our Newsletter to Get Free SQL Server Tips and News to Build your Career

Like MyTechMantra on Facebook to get updates on What's Happening in SQL Server


Conclusion

We recommend creating an SQL Server Agent Job which runs once a day to store these results to a table along with timestamp during data load. This will help you identify whenever there is a configuration change at a later point in time.

Learn More...







Last Updated On: May 01, 2016



Share this Article



Receive Free SQL Server Tips and Keep Learning
Get Free SQL Server Tips




Please leave your Valuable Comment or Let us know how this article helped you: