Script to get the SQL Server Properties

2 Replies

You can execute the below script to get the SQL Server Properties.

create table #server(ID int,  Name  sysname null, Internal_Value int null, Value nvarchar(512) null)
insert #server exec master.dbo.xp_msver

declare @RegRootDir nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\Setup', N'SQLPath', @RegRootDir OUTPUT

SELECT
(select Value from #server where Name = N'ProductName') AS [Product],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
(select Value from #server where Name = N'Language') AS [Language],
(select Value from #server where Name = N'Platform') AS [Platform],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
(select Internal_Value from #server where Name = N'ProcessorCount') AS [Processors],
(select Value from #server where Name = N'WindowsVersion') AS [OSVersion],
(select Internal_Value from #server where Name = N'PhysicalMemory') AS [PhysicalMemory],
CAST(SERVERPROPERTY('IsClustered') AS bit) AS [IsClustered],
@RegRootDir AS [RootDirectory],
convert(sysname, serverproperty(N'collation')) AS [Collation]

drop table #server

Output

Product VersionString Language Platform Edition Processors OSVersion PhysicalMemory IsClustered RootDirectory Collation
Microsoft SQL Server 10.0.1600.22 English (United States) NT INTEL X86 Enterprise Edition 4 6.0 (6002) 3496 0 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL SQL_Latin1_General_CP1_CI_AS 

					

		
		
	

							
	

				

			
						

				Steps to add Log Shipping monitor into an existing SQL Server
			

										

					1 Reply				

					


				

			
Problem

I have a requirement to add the Log Shipping Monitor for an existing installation. I have heard you can only complete this by rebuilding the Log Shipping infrastructure.  Is that true?  Are there any other options?  In this tip I will explain how we can add the Log Shipping monitor to a SQL Server 2005, 2008, 2008 R2 or 2012 environment without rebuilding the Log Shipping installation.


Solution

http://www.mssqltips.com/sqlservertip/2799/steps-to-add-log-shipping-monitor-into-an-existing-sql-server/

					

		
		
	

							
	

				

			
						

				sys.sql_logins
			

										

					1 Reply				

					


				

			
sys.sql_logins: Returns one row for each SQL Server authentication login. It will return all the below columns.


    

  • name
    • 

  • principal_id
    • 

  • sid
    • 

  • type
    • 

  • type_desc
    • 

  • is_disabled
    • 

  • create_date
    • 

  • modify_date
    • 

  • default_database_name
    • 

  • default_language_name
    • 

  • credential_id
    • 

  • is_policy_checked
    • 

  • is_expiration_checked
    • 

  • password_hash
    • 



You can query sys.sql_logins to get all the below information.


SQL Logins which are disabled:


SELECT name  
FROM [sys].[sql_logins] 
WHERE [is_disabled] = 1; 



SQL Server Logins which adhere the password policy:


SELECT name  
FROM [sys].[sql_logins] 
WHERE [is_policy_checked] = 1;



SQL Server Logins which do not adhere to the password policy


SELECT name  
FROM [sys].[sql_logins] 
WHERE [is_policy_checked] = 0;



SQL Logins which do not adhere to password expiration


SELECT name  
FROM [sys].[sql_logins] 
WHERE [is_policy_checked] = 0 
   OR  ([is_policy_checked] = 1 AND [is_expiration_checked] = 0);


					

		
		
	

							
	

				

			
						

				Prevent Truncation of Dynamically Generated Results in SQL Server Management Studio
			

										

					Leave a reply				

					


				

			
Problem

While working with the Results to Text option in SSMS, you may come across a situation where the output from dynamically generated data is truncated. In this article I will guide you on how to fix this issue and print all the text for the Results to Text option.


Solution

http://www.mssqltips.com/sqlservertip/2795/prevent-truncation-of-dynamically-generated-results-in-sql-server-management-studio/

					

		
		
	

							
	

				

			
						

				Steps to Check the Host Name for a Clustered SQL Server Instance
			

										

					Leave a reply				

					


				

			
Problem

While troubleshooting a SQL Server cluster failover issue, it is essential to know the time needed for the cluster failover and the node name where SQL Server was running before the failover occurred. In this tip, I will show you the different options to find the failover time and node name where SQL Server was running before the failover over.


Solution

http://www.mssqltips.com/sqlservertip/2744/steps-to-check-the-host-name-for-a-clustered-sql-server-instance/