Skip to content

TSQL Parser Code Samples

Jump to bottom
Bruce Dunwiddie edited this page Aug 7, 2019 · 6 revisions

Token Parsing

Example .Net Fiddle

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
	{
		public class Program
		{
			public 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);
				}
			}
		}
	}

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: ;

Select Statement Parsing

Example .Net Fiddle

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
	{
		public class Program
		{
			public 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);
					}
				}
			}
		}
	}

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
Clone this wiki locally