Get Windows OS version using t-sql

Method 1 : Query sys.dm_os_windows SELECT windows_release, windows_service_pack_level, windows_sku, os_language_version FROM sys.dm_os_windows_info; Method 2 : Use xp_cmdshell - although this does mean enabling xp_cmdshell , which is in many organisations as security violation exec master..xp_cmdshell 'systeminfo' Method 3: The @@version returns system and build information for the current SQL Server. SELECT @@version Method 4: xp_msver returns details about the actual build number of the server and information about the server environment.This can be very useful if you want to integrated as part of stored procedure, batch or other type of t-sql code. xp_msver Sample output 1 ProductName NULL Microsoft SQL... Read more →

How to create a graph table in SQL Serevr 2017

Question: How do I create a graph table in SQL Server 2017? Answer: To create a graph database table uses similar syntax as the CREATE TABLE syntax with the addition of the AS NODE keyword or the AS EDGE at the end of the CREATE TABLE syntax. Normally you would create a table such as : CREATE TABLE dbo.MyNode (ID int , NAME varchar(30)); In the graph table CREATE you would do it like: CREATE TABLE dbo.MyNode (ID int , NAME varchar(30)) AS NODE; or CREATE TABLE dbo.MyEdge(ID int , NAME varchar(30)) AS EDGE ; Once you've created the tables... Read more →

How to use parsename with SERVERPROPERTY('productversion')

Question: I'd like to use SERVERPROPERTY('productversion') for purposes of reporting on current sql server level. I'm using the SERVERPROPERTY('productversion') funcion , but am struggling with how to split the parts of the details , to make it easier for reporting. For example : 13.0.5081.1 , I'd like to split as Major = 13 Minor = 0 Build = 5081 Revision = 1 Answer: The PARSENAME function can be used to split the SERVERPROPERTY('productversion') and avoid using various string functions. PARSENAME is commonly used to split object names into there constituent parts. Here is an example: select parsename('master.dbo.sysdatabases',1) select parsename('master.dbo.sysdatabases',2) select... Read more →

How to convert sys.master_files size column into GB

Question: I need to convert the sys.master_files size column into GB. Currently when I run select name,size from sys.master_files I get back the file size as KB. I've tried doing something - size/1024/1024 - but I'm getting incorrect results. How do I get the current sys.master_files size unit into the GB format? Answer: The size column in the sys.master_files is an int data type and is represented as 8-KB pages. In the example you've given above - you're not taking into account the 8-KB pages. You need to multiply the size value by 8. i.e size * 8 Find an... Read more →

How to find a SQL schema owner name

Question: How can I find the owner of a SQL Server schema ? I want o find the owner through a t-sql solultion , rather than looking through the GUI. Answer: To find a schema owner you can use either sys.schema view or the information_schema.schemata. Since SQL 2005, information_schema.schemata has come into line with sys.schema. information_schema.schemata returns schemas just from the current database. Find examples of the twp approaches below. If you're using the sys.schemas method, you'll need to use the schema_name() and user_name() functions to extract the names. use msdb go select schema_name(schema_id) as schemanames, user_name(s.principal_id) as usernames from... Read more →