{
    "dependencies": {
        "Microsoft.AspNetCore.Mvc.Razor.ViewCompilation.Design": {
            "version": "1.1.0-preview4-final",
            "type": "build"
        }
    },
 
    "tools": {
        "Microsoft.AspNetCore.Mvc.Razor.ViewCompilation.Tools": {
            "version": "1.1.0-preview4-final"
        }
    },

 
    "scripts": {
        "postpublish": [
            "dotnet razor-precompile --configuration %publish:Configuration% --framework %publish:TargetFramework% --output-path %publish:OutputPath% %publish:ProjectPath%",
            "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%"
        ]
    }
}

  "publishOptions": {
    "include": [
      "wwwroot",
       //"**/*.cshtml",
      "appsettings.json",
      "web.config"
    ]
  },

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace DbCop
{
    // Microsoft® SQL Server® 2012 Transact-SQL ScriptDom 
    // SQL Server 2012 managed parser, Supports SQL Server 2005+
    // SQLDom.msi (redist x86/x64)
    // http://www.microsoft.com/en-us/download/details.aspx?id=29065
    // X86: http://go.microsoft.com/fwlink/?LinkID=239634&clcid=0x409
    // X64: http://go.microsoft.com/fwlink/?LinkID=239635&clcid=0x409
    // Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll

    class Program
    {
        static void Main()
        {
            const string tSql = @"
                -- select * in PROCEDURE
                CREATE PROCEDURE dbo.SelectStarTest
                AS
                SELECT * FROM dbo.tbl1
                go

                -- select * in PROCEDURE with TableVar
                Create PRocedure SelectAll
                AS
                Declare @X table(Id integer)
                Select * from @x
                go

                -- select * in PROCEDURE with ctex
                CREATE PROCEDURE dbo.SelectAllCte
                AS 
                WITH ctex
                AS (
                SELECT * FROM sys.objects
                )
                SELECT * FROM ctex
                go

                -- normal select *
                select * from tbl1; 
                select * from dbo.tbl2;
            ";

            IList<ParseError> errors;
            TSqlScript sqlFragment;
            using (var reader = new StringReader(tSql))
            {
                var parser = new TSql110Parser(initialQuotedIdentifiers: true);
                sqlFragment = (TSqlScript)parser.Parse(reader, out errors);
            }

            if (errors != null && errors.Any())
            {
                var sb = new StringBuilder();
                foreach (var error in errors)
                    sb.AppendLine(error.Message);

                throw new InvalidOperationException(sb.ToString());
            }

            var i = 0;
            foreach (var batch in sqlFragment.Batches)
            {
                Console.WriteLine("Batch: {0}, Statement(s): {1}", ++i, batch.Statements.Count);
                foreach (var statement in batch.Statements)
                {
                    processStatement(statement);
                }
                Console.WriteLine();
            }

            Console.WriteLine("\nPress a key...");
            Console.Read();
        }

        private static void processStatement(TSqlStatement statement)
        {
            var createProcedureStatement = statement as CreateProcedureStatement;
            if (createProcedureStatement != null)
            {
                var statementList = createProcedureStatement.StatementList;
                foreach (var procedureStatement in statementList.Statements)
                {
                    processStatement(procedureStatement);
                }
            }

            var selectStatement = statement as SelectStatement;
            if (selectStatement != null)
            {
                var query = selectStatement.QueryExpression;
                var selectElements = ((QuerySpecification)query).SelectElements;
                foreach (var selectElement in selectElements)
                {
                    var expression = selectElement as SelectStarExpression;
                    if (expression == null) continue;
                    Console.WriteLine(
                        "`Select *` detected @StartOffset:{0}, Line:{1}, T-SQL: {2}",
                        expression.StartOffset,
                        expression.StartLine,
                        statementToString(selectStatement));
                }
            }
        }

        private static string statementToString(TSqlFragment selectStatement)
        {
            var text = new StringBuilder();
            for (var i = selectStatement.FirstTokenIndex; i <= selectStatement.LastTokenIndex; i++)
            {
                text.Append(selectStatement.ScriptTokenStream[i].Text);
            }
            return text.ToString();
        }
    }
}

Batch: 1, Statement(s): 1
`Select *` detected @StartOffset:140, Line:5, T-SQL: SELECT * FROM dbo.tbl1

Batch: 2, Statement(s): 1
`Select *` detected @StartOffset:368, Line:12, T-SQL: Select * from @x

Batch: 3, Statement(s): 1
`Select *` detected @StartOffset:659, Line:22, T-SQL: WITH ctex
                AS (
                SELECT * FROM sys.objects
                )
                SELECT * FROM ctex

Batch: 4, Statement(s): 2
`Select *` detected @StartOffset:753, Line:26, T-SQL: select * from tbl1;
`Select *` detected @StartOffset:791, Line:27, T-SQL: select * from dbo.tbl2;

Select
{} On Columns ,
{} On Rows
From <Cube_Name>
Where <Condition>

Select
From [Adventure Works]

Select
From [Adventure Works]
Where [Measures].[Reseller Sales Amount]

Select
From [Adventure Works]
where [Measures].[Internet Sales Amount]

Select
[Measures].[Reseller Sales Amount] on columns
From [Adventure Works]

Select
[Date].[Calendar].[Calendar Year] on columns
From [Adventure Works]

Select

{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns

From [Adventure Works]

Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
[Product].[Product Categories].[Category] on rows
From [Adventure Works]

Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]}on rows
From [Adventure Works]

Select
non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns ,
{[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works]

Select
{[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
{[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

Select
non empty {[Date].[Calendar].[Calendar Year],[Date].[Calendar]} on columns,
non empty {[Product].[Product Categories].[Category],[Product].[Product Categories]} on rows
From [Adventure Works]
Where [Measures].[Internet Sales Amount]

Select
[Sales Channel] on columns
From [Adventure Works]

Select
[Product] on columns
From [Adventure Works]

Select
[Product].[Category] on columns
From [Adventure Works]

Select
[Product].[Category].[all]   on columns
From [Adventure Works]
--
Select
[Product].[Category].[All] on columns
From [Adventure Works]
--
Select
[Product].[Category].[(all)] on columns
From [Adventure Works]
--
Select
[Product].[Category].[all products] on columns
From [Adventure Works]

Select
{[Product].[Category].members} on columns
From [Adventure Works]

Select
[Product].[Category].[Category].members
-- dimension.hierarchy.level.members
on columns
From [Adventure Works]

Select
[Product].[Category].[Category].members,[Product].[Category].[All Products] on columns
From [Adventure Works]

Select
{[Product].[Category].[Category].members,[Product].[Category].[All Products]} on columns
From [Adventure Works]

Select
{[Product].[Category].[Category],[Product].[Category]}
on columns
From [Adventure Works]

Select
[Product].[Category].&[1]
on columns
From [Adventure Works]

Select
[Product].[Category].[Bikes]
on columns
From [Adventure Works]

Select
{[Product].[Category].[Bikes],[Product].[Category].[Clothing]}
on columns
From [Adventure Works]

Select
[Product].[Product Categories].members
on columns
From [Adventure Works]

Select
[Product].[Product Categories],[Product].[Category]
on columns
From [Adventure Works]

Go

Select
{[Product].[Product Categories],[Product].[Category]}
on columns
From [Adventure Works]

Select
[Product].[Product Categories].members
on rows
From [Adventure Works]

Select
{} on columns,
[Product].[Product Categories].members
on rows
From [Adventure Works]

  HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\11.0\General\

 HKEY_CURRENT_USER\Software\Microsoft\VSWinExpress\11.0\General\

 HKEY_CURRENT_USER\Software\Microsoft\VSWDExpress\11.0\General\

Set-ItemProperty -Path HKCU:\Software\Microsoft\VisualStudio\11.0\General -Name SuppressUppercas
eConversion -Type DWord -Value 1

docker pull microsoft/mssql-server-linux:2017-latest

 sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<YourStrong!Passw0rd>' \
-p 1433:1433 --name sql1 \
-d mcr.microsoft.com/mssql/server:2017-latest

Server Host: localhost
Port: 1433
Authentication: SQL Server Authentication
Login: SA
Password: <StrongPasswordYouSet>

sudo docker exec -it sql1 "bash"

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourNewStrong!Passw0rd>'

 CREATE DATABASE TestDB

 CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)

 INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);

 SELECT Name from sys.Databases

 SELECT * FROM Inventory WHERE quantity > 152;

dotnet new webapi -o dockerapi

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

public class Students
{
       public int Id { get; set; }
       public string Name { get; set; }
       public string Phone { get; set; }
}

 dotnet ef dbcontext scaffold "Server=localhost,1433\\Catalog=tutorial_database;Database=<YOUR_DATABASE_NAME>;User=SA;Password=<StrongPasswordYouSet>;" Microsoft.EntityFrameworkCore.SqlServer

"ConnectionStrings": {
  "TestingDatabase": "Server=localhost:1433\\Database=<YourDatabaseName>;User=SA;Password=<StrongPasswordYouSet>;"
}

 dotnet ef migrations add <NAME_OF_MIGRATION>

ef database update

 SELECT TABLE_NAME FROM dockerdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

 SELECT * INTO #temptable FROM [Northwind].[dbo].[Products]
UPDATE #temptable SET [UnitPrice] = [UnitPrice] + 10

DECLARE @tempTable TABLE
(
   [ProductID] [int] NOT NULL,
   [ProductName] [nvarchar](40) NOT NULL,
   [UnitPrice] [money] NULL
)
 
INSERT INTO @tempTable
SELECT
   [ProductID],
   [ProductName],
   [UnitPrice]
FROM [Northwind].[dbo].[Products]
 
UPDATE @temptable SET [UnitPrice] = [UnitPrice] + 10

WITH yourName [(Column1, Column2, ...)]
AS
(
   your query
)

WITH temp
AS
(
    SELECT
         [ProductName],
         [UnitPrice]
    FROM [Northwind].[dbo].[Products]
)
SELECT * FROM temp
ORDER BY [UnitPrice] DESC

WITH orderSales (OrderID, Total)
AS
(
   SELECT
      [OrderID],
     SUM([UnitPrice]*[Quantity]) AS Total
   FROM [Northwind].[dbo].[Order Details]
   GROUP BY [OrderID]
)
SELECT
   O.[ShipCountry],
   SUM(OS.[Total]) AS TotalSales
FROM [Northwind].[dbo].[Orders] AS O INNER JOIN [orderSales] AS OS
ON O.[OrderID] = OS.[OrderID]
GROUP BY O.[ShipCountry]
ORDER BY TotalSales DESC

WITH customerList
AS
(
   SELECT
      [CustomerID],
      [ContactName]
   FROM [Northwind].[dbo].[Customers]
   WHERE [Country] ='UK'
)
,orderList
AS
(
   SELECT
      [CustomerID],
      [OrderDate]
   FROM [Northwind].[dbo].[Orders]
   WHERE YEAR([OrderDate])< 2000
)
SELECT
   cl.[ContactName],
   YEAR(ol.[OrderDate]) AS SalesYear
FROM customerList AS cl JOIN orderList AS ol
ON cl.[CustomerID] = ol.[CustomerID]

WITH digitList
AS
(
   SELECT [digit] from [digits]
)
SELECT
   a.[digit] * 10 + b.[digit] + 1 AS [Digit]
FROM [digitList] AS a CROSSJOIN [digitList] AS b

WITH CTE_1 AS
(
   ....
),
CTE_2 AS
(
   SELECT  ... FROM CTE_1 JOIN ...
)
SELECT   *
FROM FOO
LEFTJOIN  CTE_1
LEFTJOIN  CTE_2

WITH cteName AS
(
   query1
   UNION ALL
   query2
)

IFOBJECT_ID('Employees','U')ISNOTNULL
DROPTABLE dbo.Employees
GO 

CREATETABLE dbo.Employees
(
  EmployeeID intNOTNULLPRIMARYKEY,
  FirstName varchar(50)NOTNULL,
  LastName varchar(50)NOTNULL,
  ManagerID intNULL
)

GO


INSERTINTO Employees VALUES (101,'Alireza','Nematollahi',NULL)
INSERTINTO Employees VALUES (102,'Ahmad','Mofarrahzadeh', 101)
INSERTINTO Employees VALUES (103,'Mohammad','BozorgGhommi', 102)
INSERTINTO Employees VALUES (104,'Masoud','Narimani', 103)
INSERTINTO Employees VALUES (105,'Mohsen','Hashemi', 103)
INSERTINTO Employees VALUES (106,'Aref','Partovi', 102)
INSERTINTO Employees VALUES (107,'Hosain','Mahmoudi', 106)
INSERTINTO Employees VALUES (108,'Naser','Pourali', 106)
INSERTINTO Employees VALUES (109,'Reza','Bagheri', 102)
INSERTINTO Employees VALUES (110,'Abbas','Najafian', 102)

WITHcteReports(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID ISNULL
UNIONALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,r.EmpLevel + 1
FROM Employees e INNERJOINcteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName +' '+ LastName AS FullName,
EmpLevel,
(SELECT FirstName +' '+ LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID)AS Manager
FROMcteReports
ORDERBY EmpLevel, MgrID

WITHcteReports(EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE EmployeeID = 110
UNIONALL
SELECTe.EmployeeID, e.FirstName, e.LastName, e.ManagerID,r.EmpLevel + 1
FROM Employees e INNERJOINcteReports r
ON e.EmployeeID = r.MgrID
)
SELECT
FirstName +' '+ LastName AS FullName,
EmpLevel
FROMcteReports
ORDERBY EmpLevel