March 25, 2019 at 10:06 am
Does anyone have a good way of getting all of the server properties from SERVERPROPERTY()? I know you can write a query and enter each individual property but that seems cumbersome. Hopefully someone already has a query that can easily get all of the properties.
We are migrating databases to new servers and we want to see the differences between the old and new server.
March 25, 2019 at 10:14 am
A simple search on this site returned this:
http://www.sqlservercentral.com/blogs/erichumphrey/2011/04/14/get-all-serverproperty-values-for-sql-server/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 25, 2019 at 10:33 am
Michael L John - Monday, March 25, 2019 10:14 AMA simple search on this site returned this:
http://www.sqlservercentral.com/blogs/erichumphrey/2011/04/14/get-all-serverproperty-values-for-sql-server/
I was hoping to avoid having a wall of text as a query. It would be nice if there was a DMV or a way to get all of the server property names without having to type them all out.
March 25, 2019 at 10:41 am
blakemcneill - Monday, March 25, 2019 10:33 AMMichael L John - Monday, March 25, 2019 10:14 AMA simple search on this site returned this:
http://www.sqlservercentral.com/blogs/erichumphrey/2011/04/14/get-all-serverproperty-values-for-sql-server/I was hoping to avoid having a wall of text as a query. It would be nice if there was a DMV or a way to get all of the server property names without having to type them all out.
Uh, someone already did type them for you.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 4, 2019 at 3:11 am
SET NOCOUNT ON
SELECT @@VERSION [VERSION],
CONVERT(VARCHAR(100), SERVERPROPERTY('Servername')) Servername ,
CONVERT(VARCHAR(100), SERVERPROPERTY('ProductVersion')),
CONVERT(VARCHAR(100), SERVERPROPERTY('ProductLevel')) Product_SP_Level,
CONVERT(VARCHAR(100), SERVERPROPERTY('ResourceLastUpdateDateTime'))ResourceLastUpdate,
CONVERT(VARCHAR(100), SERVERPROPERTY('ResourceVersion'))ResourceVersion,
ISNULL(CONVERT(VARCHAR(100), CASE
WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Integrated security'
WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Not Integrated security'
END),'NODATA') EngineEdition,
ISNULL(CONVERT(VARCHAR(100),CASE
WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Personal Edition'
WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Standard Edition'
WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 'Enterprise Edition'
WHEN SERVERPROPERTY('EngineEdition') = 4 THEN 'Express Edition'
END),'NODATA')EngineEdition ,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
ISNULL(CONVERT(VARCHAR(100), SERVERPROPERTY('InstanceName')),'DEFAULT') InstanceName,
ISNULL(CONVERT(VARCHAR(100), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')),'NODATA') ComputerName,
ISNULL(CONVERT(VARCHAR(100), SERVERPROPERTY('LicenseType')),'NODATA')LicenseType,
ISNULL(CONVERT(VARCHAR(100), SERVERPROPERTY('NumLicenses')),'NODATA')NumLicenses,
ISNULL(CONVERT(VARCHAR(100), SERVERPROPERTY('BuildClrVersion')),'NODATA')BuildClrVersion,
ISNULL(CONVERT(VARCHAR(100), SERVERPROPERTY('Collation')),'NODATA')Collation,
ISNULL(CONVERT(VARCHAR(100), SERVERPROPERTY('CollationID')),'NODATA')CollationID,
ISNULL(CONVERT(VARCHAR(100), SERVERPROPERTY('ComparisonStyle')),'NODATA')ComparisonStyle,
CONVERT(VARCHAR(100),CASE
WHEN CONVERT(VARCHAR(100), SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered'
WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error'
END) Cluster_info,
CONVERT(VARCHAR(100),CASE
WHEN CONVERT(VARCHAR(100), SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed'
WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed'
WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error'
END)[FullText],
CONVERT(VARCHAR(100), SERVERPROPERTY('SqlCharSet'))SqlCharSet,
CONVERT(VARCHAR(100), SERVERPROPERTY('SqlCharSetName'))SqlCharSetName,
CONVERT(VARCHAR(100), SERVERPROPERTY('SqlSortOrder'))SqlSortOrder,
CONVERT(VARCHAR(100), SERVERPROPERTY('SqlSortOrderName'))SqlSortOrderName,
CONVERT(VARCHAR, getdate(), 121) sample_date,
(SELECT DISTINCT local_tcp_port FROM sys.dm_exec_connections WHERE local_tcp_port IS NOT NULL ) AS tcp_port
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply