Skip to content

TSQL Parser Code Samples

Jump to bottom
Bruce Dunwiddie edited this page Jul 5, 2018 · 6 revisions

Select Statement Parsing

T-SQL:

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
HAVING OrderDateKey > 20010000
ORDER BY OrderDateKey;

Code:

using System;

using TSQL;
using TSQL.Statements;
using TSQL.Tokens;

namespace TSQLParserExample
{
	class Program
	{
		static void Main(string[] args)
		{
			TSQLSelectStatement select = TSQLStatementReader.ParseStatements(@"
				SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales
				FROM FactInternetSales
				GROUP BY OrderDateKey
				HAVING OrderDateKey > 20010000
				ORDER BY OrderDateKey;")[0] as TSQLSelectStatement;

			Console.WriteLine("SELECT:");
			foreach (TSQLToken token in select.Select.Tokens)
			{
				Console.WriteLine("\ttype: " + token.Type.ToString() + ", value: " + token.Text);
			}

			if (select.From != null)
			{
				Console.WriteLine("FROM:");
				foreach (TSQLToken token in select.From.Tokens)
				{
					Console.WriteLine("\ttype: " + token.Type.ToString() + ", value: " + token.Text);
				}
			}

			if (select.Where != null)
			{
				Console.WriteLine("WHERE:");
				foreach (TSQLToken token in select.Where.Tokens)
				{
					Console.WriteLine("\ttype: " + token.Type.ToString() + ", value: " + token.Text);
				}
			}

			if (select.GroupBy != null)
			{
				Console.WriteLine("GROUP BY:");
				foreach (TSQLToken token in select.GroupBy.Tokens)
				{
					Console.WriteLine("\ttype: " + token.Type.ToString() + ", value: " + token.Text);
				}
			}

			if (select.Having != null)
			{
				Console.WriteLine("HAVING:");
				foreach (TSQLToken token in select.Having.Tokens)
				{
					Console.WriteLine("\ttype: " + token.Type.ToString() + ", value: " + token.Text);
				}
			}

			if (select.OrderBy != null)
			{
				Console.WriteLine("ORDER BY:");
				foreach (TSQLToken token in select.OrderBy.Tokens)
				{
					Console.WriteLine("\ttype: " + token.Type.ToString() + ", value: " + token.Text);
				}
			}

			Console.ReadLine();
		}
	}
}

Result:

SELECT:
	type: Keyword, value: SELECT
	type: Identifier, value: OrderDateKey
	type: Character, value: ,
	type: Identifier, value: SUM
	type: Character, value: (
	type: Identifier, value: SalesAmount
	type: Character, value: )
	type: Keyword, value: AS
	type: Identifier, value: TotalSales
FROM:
	type: Keyword, value: FROM
	type: Identifier, value: FactInternetSales
GROUP BY:
	type: Keyword, value: GROUP
	type: Keyword, value: BY
	type: Identifier, value: OrderDateKey
HAVING:
	type: Keyword, value: HAVING
	type: Identifier, value: OrderDateKey
	type: Operator, value: >
	type: NumericLiteral, value: 20010000
ORDER BY:
	type: Keyword, value: ORDER
	type: Keyword, value: BY
	type: Identifier, value: OrderDateKey

Token Parsing

T-SQL:

CREATE VIEW [HumanResources].[vEmployee] 
AS 
SELECT 
	e.[BusinessEntityID]
	,p.[Title]
	,p.[FirstName]
	,p.[MiddleName]
	,p.[LastName]
	,p.[Suffix]
	,e.[JobTitle]  
	,pp.[PhoneNumber]
	,pnt.[Name] AS [PhoneNumberType]
	,ea.[EmailAddress]
	,p.[EmailPromotion]
	,a.[AddressLine1]
	,a.[AddressLine2]
	,a.[City]
	,sp.[Name] AS [StateProvinceName] 
	,a.[PostalCode]
	,cr.[Name] AS [CountryRegionName] 
	,p.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
	INNER JOIN [Person].[Person] p
	ON p.[BusinessEntityID] = e.[BusinessEntityID]
	INNER JOIN [Person].[BusinessEntityAddress] bea 
	ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
	INNER JOIN [Person].[Address] a 
	ON a.[AddressID] = bea.[AddressID]
	INNER JOIN [Person].[StateProvince] sp 
	ON sp.[StateProvinceID] = a.[StateProvinceID]
	INNER JOIN [Person].[CountryRegion] cr 
	ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
	LEFT OUTER JOIN [Person].[PersonPhone] pp
	ON pp.BusinessEntityID = p.[BusinessEntityID]
	LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
	ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
	LEFT OUTER JOIN [Person].[EmailAddress] ea
	ON p.[BusinessEntityID] = ea.[BusinessEntityID];

Code:

using System;

using TSQL;
using TSQL.Tokens;

namespace TSQLParserExample
{
	class Program
	{
		static void Main(string[] args)
		{
			foreach (TSQLToken token in TSQLTokenizer.ParseTokens(@"
				CREATE VIEW [HumanResources].[vEmployee] 
				AS 
				SELECT 
					e.[BusinessEntityID]
					,p.[Title]
					,p.[FirstName]
					,p.[MiddleName]
					,p.[LastName]
					,p.[Suffix]
					,e.[JobTitle]  
					,pp.[PhoneNumber]
					,pnt.[Name] AS [PhoneNumberType]
					,ea.[EmailAddress]
					,p.[EmailPromotion]
					,a.[AddressLine1]
					,a.[AddressLine2]
					,a.[City]
					,sp.[Name] AS [StateProvinceName] 
					,a.[PostalCode]
					,cr.[Name] AS [CountryRegionName] 
					,p.[AdditionalContactInfo]
				FROM [HumanResources].[Employee] e
					INNER JOIN [Person].[Person] p
					ON p.[BusinessEntityID] = e.[BusinessEntityID]
					INNER JOIN [Person].[BusinessEntityAddress] bea 
					ON bea.[BusinessEntityID] = e.[BusinessEntityID] 
					INNER JOIN [Person].[Address] a 
					ON a.[AddressID] = bea.[AddressID]
					INNER JOIN [Person].[StateProvince] sp 
					ON sp.[StateProvinceID] = a.[StateProvinceID]
					INNER JOIN [Person].[CountryRegion] cr 
					ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
					LEFT OUTER JOIN [Person].[PersonPhone] pp
					ON pp.BusinessEntityID = p.[BusinessEntityID]
					LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
					ON pp.[PhoneNumberTypeID] = pnt.[PhoneNumberTypeID]
					LEFT OUTER JOIN [Person].[EmailAddress] ea
					ON p.[BusinessEntityID] = ea.[BusinessEntityID];"))
			{
				Console.WriteLine("type: " + token.Type.ToString() + ", value: " + token.Text);
			}

			Console.ReadLine();
		}
	}
}

Result:

type: Keyword, value: CREATE
type: Keyword, value: VIEW
type: Identifier, value: [HumanResources]
type: Character, value: .
type: Identifier, value: [vEmployee]
type: Keyword, value: AS
type: Keyword, value: SELECT
type: Identifier, value: e
type: Character, value: .
type: Identifier, value: [BusinessEntityID]
type: Character, value: ,
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [Title]
type: Character, value: ,
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [FirstName]
type: Character, value: ,
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [MiddleName]
type: Character, value: ,
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [LastName]
type: Character, value: ,
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [Suffix]
type: Character, value: ,
type: Identifier, value: e
type: Character, value: .
type: Identifier, value: [JobTitle]
type: Character, value: ,
type: Identifier, value: pp
type: Character, value: .
type: Identifier, value: [PhoneNumber]
type: Character, value: ,
type: Identifier, value: pnt
type: Character, value: .
type: Identifier, value: [Name]
type: Keyword, value: AS
type: Identifier, value: [PhoneNumberType]
type: Character, value: ,
type: Identifier, value: ea
type: Character, value: .
type: Identifier, value: [EmailAddress]
type: Character, value: ,
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [EmailPromotion]
type: Character, value: ,
type: Identifier, value: a
type: Character, value: .
type: Identifier, value: [AddressLine1]
type: Character, value: ,
type: Identifier, value: a
type: Character, value: .
type: Identifier, value: [AddressLine2]
type: Character, value: ,
type: Identifier, value: a
type: Character, value: .
type: Identifier, value: [City]
type: Character, value: ,
type: Identifier, value: sp
type: Character, value: .
type: Identifier, value: [Name]
type: Keyword, value: AS
type: Identifier, value: [StateProvinceName]
type: Character, value: ,
type: Identifier, value: a
type: Character, value: .
type: Identifier, value: [PostalCode]
type: Character, value: ,
type: Identifier, value: cr
type: Character, value: .
type: Identifier, value: [Name]
type: Keyword, value: AS
type: Identifier, value: [CountryRegionName]
type: Character, value: ,
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [AdditionalContactInfo]
type: Keyword, value: FROM
type: Identifier, value: [HumanResources]
type: Character, value: .
type: Identifier, value: [Employee]
type: Identifier, value: e
type: Keyword, value: INNER
type: Keyword, value: JOIN
type: Identifier, value: [Person]
type: Character, value: .
type: Identifier, value: [Person]
type: Identifier, value: p
type: Keyword, value: ON
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [BusinessEntityID]
type: Operator, value: =
type: Identifier, value: e
type: Character, value: .
type: Identifier, value: [BusinessEntityID]
type: Keyword, value: INNER
type: Keyword, value: JOIN
type: Identifier, value: [Person]
type: Character, value: .
type: Identifier, value: [BusinessEntityAddress]
type: Identifier, value: bea
type: Keyword, value: ON
type: Identifier, value: bea
type: Character, value: .
type: Identifier, value: [BusinessEntityID]
type: Operator, value: =
type: Identifier, value: e
type: Character, value: .
type: Identifier, value: [BusinessEntityID]
type: Keyword, value: INNER
type: Keyword, value: JOIN
type: Identifier, value: [Person]
type: Character, value: .
type: Identifier, value: [Address]
type: Identifier, value: a
type: Keyword, value: ON
type: Identifier, value: a
type: Character, value: .
type: Identifier, value: [AddressID]
type: Operator, value: =
type: Identifier, value: bea
type: Character, value: .
type: Identifier, value: [AddressID]
type: Keyword, value: INNER
type: Keyword, value: JOIN
type: Identifier, value: [Person]
type: Character, value: .
type: Identifier, value: [StateProvince]
type: Identifier, value: sp
type: Keyword, value: ON
type: Identifier, value: sp
type: Character, value: .
type: Identifier, value: [StateProvinceID]
type: Operator, value: =
type: Identifier, value: a
type: Character, value: .
type: Identifier, value: [StateProvinceID]
type: Keyword, value: INNER
type: Keyword, value: JOIN
type: Identifier, value: [Person]
type: Character, value: .
type: Identifier, value: [CountryRegion]
type: Identifier, value: cr
type: Keyword, value: ON
type: Identifier, value: cr
type: Character, value: .
type: Identifier, value: [CountryRegionCode]
type: Operator, value: =
type: Identifier, value: sp
type: Character, value: .
type: Identifier, value: [CountryRegionCode]
type: Keyword, value: LEFT
type: Keyword, value: OUTER
type: Keyword, value: JOIN
type: Identifier, value: [Person]
type: Character, value: .
type: Identifier, value: [PersonPhone]
type: Identifier, value: pp
type: Keyword, value: ON
type: Identifier, value: pp
type: Character, value: .
type: Identifier, value: BusinessEntityID
type: Operator, value: =
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [BusinessEntityID]
type: Keyword, value: LEFT
type: Keyword, value: OUTER
type: Keyword, value: JOIN
type: Identifier, value: [Person]
type: Character, value: .
type: Identifier, value: [PhoneNumberType]
type: Identifier, value: pnt
type: Keyword, value: ON
type: Identifier, value: pp
type: Character, value: .
type: Identifier, value: [PhoneNumberTypeID]
type: Operator, value: =
type: Identifier, value: pnt
type: Character, value: .
type: Identifier, value: [PhoneNumberTypeID]
type: Keyword, value: LEFT
type: Keyword, value: OUTER
type: Keyword, value: JOIN
type: Identifier, value: [Person]
type: Character, value: .
type: Identifier, value: [EmailAddress]
type: Identifier, value: ea
type: Keyword, value: ON
type: Identifier, value: p
type: Character, value: .
type: Identifier, value: [BusinessEntityID]
type: Operator, value: =
type: Identifier, value: ea
type: Character, value: .
type: Identifier, value: [BusinessEntityID]
type: Character, value: ;
Clone this wiki locally