SERVERPROPERTY, part II

I decided to expand upon my SERVERPROPERTY script this morning... there are a lot of other properties beyond just the name of the instance.  :-)  I put this one together quickly, should work well if you're looking at configuration or other settings, and you'd like to see all server properties at once. 

     /* Use SERVERPROPERTY to collect details regarding the server instance. */

     DECLARE @serverproperty TABLE (property sysname)

     INSERT INTO @serverproperty(property)

     VALUES

            ('BuildClrVersion'),

            ('Collation'),

            ('CollationID'),

            ('ComparisonStyle'),

            ('ComputerNamePhysicalNetBIOS'),

            ('Edition'),

            ('EditionID'),

            ('EngineEdition'),

            ('InstanceName'),

            ('IsClustered'),

            ('IsFullTextInstalled'),

            ('IsIntegratedSecurityOnly'),

            ('IsSingleUser'),

            ('LCID'),

            ('LicenseType'),

            ('MachineName'),

            ('NumLicenses'),

            ('ProcessID'),

            ('ProductVersion'),

            ('ProductLevel'),

            ('ResourceLastUpdateDateTime'),

            ('ResourceVersion'),

            ('ServerName'),

            ('SqlCharSet'),

            ('SqlCharSetName'),

            ('SqlSortOrder'),

            ('SqlSortOrderName'),

            ('FilestreamShareName'),

            ('FilestreamConfiguredLevel'),

            ('FilestreamEffectiveLevel');      

     -- pull them back out

     SELECT

            Property,

            SERVERPROPERTY(Property) Value

     FROM

            @serverproperty

Here's a piece of the output from one of my own instances:

Take a look at this for more SERVERPROPERTY details from MSFT:  

   https://msdn.microsoft.com/en-us/library/ms174396(v=sql.110).aspx