Category Archives: OS and SQL

by

Script to get the SQL Server Properties

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 

			

	
	



				
					



	
	


		
					

				 by 			

				
Prevent Truncation of Dynamically Generated Results in SQL Server Management Studio

	


		

		
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/

			

	
	



				
					


				
					



	
	


		
					

				 by 			

				
T-SQL Script to identify the data, log and backup drive

	


		

		
You can use the below script to identify the data, log and backup file drive.


select 
@@SERVERNAME as svrName,
drivename, 
drivedescription
from
(
select distinct  SUBSTRING(filename,1,3) as drivename, 'data Drive' as drivedescription from master..sysaltfiles where filename like '%.mdf'
union
select distinct  SUBSTRING(filename,1,3) as drivename, 'Log Drive' as drivedescription from master..sysaltfiles where filename like '%.ldf'
union 
select distinct  SUBSTRING(physical_device_name,1,3) as drivename, 'Backup Drive' as drivedescription from msdb.dbo.backupmediafamily where physical_device_name like '%.bak'
) tab1