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 →

select current_user shows dbo

Question: I'm logged onto a SQL Server 2016 instance using Windows Authentication and executing SELECT CURRENT_USER. Although I have sysadmin rights - the result always returns dbo. What I need is to return my domain account name. Can you help? Answer: There is a difference between CURRENT_USER and SYSTEM_USER. SELECT CURRENT_USER - Returns the name of the current user. This function is equivalent to SELECT USER_NAME(). If the current user is a sysadmin privilege , the recordset returns “dbo”, rather than the logon name. Which because your current logon has sysadmin privileges explains the dbo result SELECT SYSTEM_USER (also synonym... Read more →