I have observed this question in the interview question few days ago. I had a fun time to see candidates failing to answer this question. The most common answer candidate gave us was that they will right click on the server and see the properties. Well, that option will not give all the details which actual question intended. Let us see the question about SQL Server Configuration:
Question: What is the best way to see all the options set for your SQL Server programmatically?
Method 1: Using sp_configure
Run following script and it will provide all information about the SQL Server. SQL Server provides in detail information if Advanced Options are turned on.
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure GO EXEC sp_configure 'show advanced options', 0 GO
To change any configuration run following script using values from the NAME column of the following result set. E.g. To change max server memory run following script.
sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'max server memory', 2048 GO RECONFIGURE GO
Method 2: Using sys.configurations
You can run following script and it will display almost the same information as above query.
SELECT * FROM sys.configurations</pre> <pre>
Reference: Pinal Dave (https://blog.sqlauthority.com)