benlaan/sqlformat

.NET SQL Parser and Formatter Tool and SSMS Plugin

sqlformat's Issues

Unicode

Are there plans to extend support for Unicode characters? The Atom Beautify package is getting reports that sqlformat fails on some non-Latin characters:

Glavin001/atom-beautify#1094

Parser hung trying to process block comment with tons of whitespace

I was commenting out large chunks of some code; when trying to format the remaining, I got presented with the Hourglass of GUI Death in SSMS.

After a few more deaths trying to distill the statements, I got it down to this:

/*SELECT * FROM               Table*/

For some reason, the time it takes to parse the block seems to increase exponentially with the number of spaces there.

List of unsupported features?

Hi, can you create a README.MD listing known unsupported features?

• table-valued functions
• table type variables

etc.

This would have saved me a lot of time; thanks!

Inner unioned query without From fails to parse

    select
        S.Field
    FROM Source S
    JOIN (
        SELECT Field = 'AA'
        UNION
        SELECT 'BB'
        UNION
        SELECT 'CC'
    ) D ON D.Field = S.Field

---

Expected: ')' but found: 'SELECT' at Row: 9, Col: 4

Parsing error of sub-select when using TOP () PERCENT

SELECT TOP (100) PERCENT * FROM
(
    SELECT 
        *
    FROM Something
) t
ORDER BY t.Modified

Produces

SELECT TOP (100)
    PERCENT * FROM(Laan.Sql.Parser.Entities.SelectStatement) t
ORDER BY t.Modified 

Fields in the form or [Aggregation]([Owner].[Table].[Column]) aren't being formatted properly

For example: "SELECT Count(HumanResources.Employee.EmployeeID) FROM HumanResources.Employee" doesn't work while "SELECT Count(Employee.EmployeeID) FROM Employee" works.

NullReferenceException with missing 'On' clause in join

select * from Table Outer
join (
select * from Table
) Inner --on inner.Value = Outer.Value

Getting a SyntaxException while parsing a SQL statment

The SQL statement SELECT [\nName] FROM [Table] fails to enumerate through the tokens in the SqlTokenizer class. A SyntaxException is thrown in RegexTokenizer.ReadNextToken().

Here is the test I had created in TestTokenizer.cs in Lann.Sql.Parser.Test:

[Test]
[TestCase( "SELECT [\nName] FROM [Table]", new[] { "SELECT", "[\nName]", "FROM", "[Table]" } )]
public void Can_Tokenize_Columns_Containing_Whitespace( string input, string[] tokens )
{
	Verify( input, tokens );
}

Multiple Statements with sp_execute only parse the first one

example:

exec sp_executesql N'SELECT1; SELECT 2'

Should output

SELECT 1;
SELECT 2

But only returns

SELECT 1;

Support integrating with Visual Studio

http://blog.bagearon.com/2012/03/sql-server-management-studio-2012-color-themes-extension-support/

FYI looks like SSMS 2012 and above do support MEF extensions by installing into C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Extensions

Support Bitwise operations

Need to add bitwise operations:

see MSDN: Bitwise Operators (Transact-SQL)

SELECT 1
FROM A
WHERE ~A.B & (A.C | A.D) <> 0 
  AND A.E ^ 5 = 0

New Installer required due to VS2012 upgrade

Currently the code used the old school VS installer.This has been deprecated in VS2012, so a WIX installer is now required.

Can't parse SELECT COUNT(DISTINCT SomeColumn)

Proper handling of IS NULL and IS NOT NULL when multiple conditions are used

SELECT Name FROM Customer
WHERE telephone IS NOT NULL
  AND email IS NULL
  AND TwitterId IS NULL

When parsing the above SQL script, the WHERE condition is evaluated as a CriteriaExpression, with the left part being "telephone" and the right part being "NOT NULL AND email IS NULL AND TwitterId IS NULL".

Thanks for the great library.

Strange Format when NOT is wrapped in an expression

SELECT 1
FROM ATable
WHERE (NOT (ATable.AReallyReallyLargeField IS NOT null) OR ATable.AReallyReallyLargeField < @p1)

Can't parse alias using AS syntax when expression is a quoted string

Example:

SELECT Name AS 'Person Name' FROM SomeTable

WITH (rowlock) missing

SELECT x FROM Something S WITH (rowlock)

The old formatter fails, the new one simply rids of the WITH (rowlock) statement

sp_execute INSERT looses fields

Input:
exec sp_executesql N'-- Query
INSERT INTO dbo.Table (Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8) VALUES (@p0, @p1, @p2, @p3, @P4, @p5, @p6, @P7)',N'@p0 int,@p1 int,@p2 nvarchar(4000),@p3 nvarchar(4000),@P4 datetime,@p5 datetime,@p6 bit,@P7 uniqueidentifier',@p0=1,@p1=1,@p2=NULL,@p3=N'A-User',@P4='2019-01-23 22:41:57.023',@p5='2019-01-23 22:41:57.023',@p6=0,@P7='87EE43C3-1589-41FA-8C17-A9DF00979904'

Output:
DECLARE
@p0 int,
@p1 int,
@p2 nvarchar(4000),
@p3 nvarchar(4000),
@P4 datetime,
@p5 datetime,
@p6 bit,
@P7 uniqueidentifier

SELECT
@p0 = 1,
@p1 = 1,
@p2 = NULL,
@p3 = N'A-User',
@P4 = '2019-01-23 22:41:57.023',
@p5 = '2019-01-23 22:41:57.023',
@p6 = 0,
@P7 = '87EE43C3-1589-41FA-8C17-A9DF00979904'

INSERT INTO dbo.Table (
)
VALUES (@p0, @p1, @p2, @p3, @P4, @p5, @p6, @P7)

VS2017: Some of projects cannot be open

I tried to open and build the solution in VS2017 but received some errors and some of projects could not be open like these:

E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Laan.SQL.Formatter\Laan.Sql.Formatter.csproj : error : The imported project "E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\packages\MSBuildTasks.1.5.0.235\tools\MSBuild.Community.Tasks.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Build\Common.targets

E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Laan.NHibernate.Appender\Laan.NHibernate.Appender.csproj : error : The imported project "E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\packages\MSBuildTasks.1.5.0.235\tools\MSBuild.Community.Tasks.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Build\Common.targets

E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Laan.SQL.Parser\Laan.Sql.Parser.csproj : error : The imported project "E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\packages\MSBuildTasks.1.5.0.235\tools\MSBuild.Community.Tasks.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Build\Common.targets

E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Laan.AddIns.Ssms\Laan.AddIns.Ssms.csproj : error : The imported project "E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\packages\MSBuildTasks.1.5.0.235\tools\MSBuild.Community.Tasks.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Build\Common.targets

E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Laan.Sql.Tools.Installer\Laan.Sql.Tools.Installer.wixproj : error : The imported project "E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\packages\WiX.Toolset.3.9.1208.0\tools\wix\wix.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Laan.Sql.Tools.Installer\Laan.Sql.Tools.Installer.wixproj

E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Laan.SQL.Formatter\Laan.Sql.Formatter.csproj : error : The imported project "E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\packages\MSBuildTasks.1.5.0.235\tools\MSBuild.Community.Tasks.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Build\Common.targets

E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Laan.SQL.Parser\Laan.Sql.Parser.csproj : error : The imported project "E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\packages\MSBuildTasks.1.5.0.235\tools\MSBuild.Community.Tasks.targets" was not found. Confirm that the path in the declaration is correct, and that the file exists on disk. E:\MyProjects\SQLServer\SSMS_1\sqlformat-master\Build\Common.targets

EXEC someProc doesn't have a formatter

It turns out that the ExecuteSqlStatementFormatter which sounds like it does the trick is there only to support sp_executesql.

i.e ExecuteSqlStatement does have a formatter, but ExecStatement doesn’t

INSERT INTO formatting issue with nested SELECT's

Source
insert into dbo.ATable
(Col1, Col2)
VALUES (
(SELECT Id FROM Table2),
(SELECT Id FROM Table3)
)

Output
INSERT INTO dbo.ATable (Col1, Col2)
VALUES ((Laan.Sql.Parser.Entities.SelectStatement), (Laan.Sql.Parser.Entities.SelectStatement))

Expected Output
INSERT INTO dbo.ATable (Col1, Col2)
VALUES (
(SELECT Id
FROM Table2),
(SELECT Id
FROM Table3))

Failed to parse delete without columns

DELETE FROM [ATable] doesn't parse. It requires a column. This is not right!

Create View statement cannot handle schema prefix

Input:
CREATE VIEW schema.Name
AS
SELECT * FROM dbo.Table

Expected:
Formatter not implemented for statement: CreateViewStatement

Desired:
CREATE VIEW schema.Name
AS

SELECT
*
FROM
dbo.Table

Support of literal values as dedicated literal types

It would be ideal that the literal values in SQL script are parsed as dedicated literal types, such as StringLiteral, NumberLiteral, BooleanLiteral, etc. Currently they seem to be all parsed as IdentifierExpression.

For instance, with the following script:

select fielda 
from table1 t1
where t1.fieldd = 10.0 or t1.fieldc = 'abc'

Ideally, 10.0 and 'abc' can be parsed as NumberLiteral and StringLiteral, and so on.

Failing to parse CAST() with DOUBLE PRECISION type

Although odd SQL, the SQL below fails to parse at the PRECISION token. It is valid for SQL Server 2008.

SELECT avg(cast(this_.Value as DOUBLE PRECISION)) as y0_ FROM dbo.[SomeTable] this_

This is generated by the following NHibernate statement, so cannot be easily changed.
Session.
QueryOver(() => entity)
.Select(Projections.Avg("FieldName"));

Conditions without parenthesis

SELECT Name FROM Customer
WHERE CreditLimit = 10000
  AND Email LIKE '%smith.com'
  AND TwitterId = 'joesmith'

When parsing the above SQL script, the WHERE condition is evaluated as three nested CriteriaExpression's. It would be ideal if they are evaluated as NestExpression's.

Thanks!

Separating comment text

When formatting an sql file with no arguments, any text wrapped with a /* at the top and a */ at the bottom is separated out each time it is formatted.

Original file:

-- Rollback Statements
/*
delete from TEST
  where attribute_id=1120;

COMMIT;
*/

-- Entering in the attribute category
insert into TEST
  (category_id,
  description,
  entered_date,
  entered_by_user_id,
  sort_order) values (
  test.nextval,
  'TEST',
  sysdate,
  1,
  9);

COMMIT;

First format:

-- Rollback Statements
 /*

delete from TEST

  where attribute_id=1120;



COMMIT;

*/ -- Entering in the attribute category

INSERT INTO test (category_id, description, entered_date, entered_by_user_id, sort_order)
VALUES ( test.nextval,
         'TEST',
         sysdate,
         1,
         9);


COMMIT;

Second format:

-- Rollback Statements
 /*



delete from TEST



  where attribute_id=1120;







COMMIT;



*/ -- Entering in the attribute category

INSERT INTO test (category_id, description, entered_date, entered_by_user_id, sort_order)
VALUES (test.nextval,
        'TEST',
        sysdate,
        1,
        9);


COMMIT;

Formatting takes a long time (Gave up after an hour)

Now I can't say I expect this to format "nicely", but it's got to be better than how NHibernate generates it.

exec sp_executesql N'
SELECT count(*) as y0_ FROM dbo.SomeView this_ WHERE (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((this_.Code = @p0 or this_.Code = @p1) or this_.Code = @p2) or this_.Code = @p3) or this_.Code = @P4) or this_.Code = @p5) or this_.Code = @p6) or this_.Code = @P7) or this_.Code = @p8) or this_.Code = @p9) or this_.Code = @p10) or this_.Code = @p11) or this_.Code = @p12) or this_.Code = @P13) or this_.Code = @p14) or this_.Code = @P15) or this_.Code = @p16) or this_.Code = @p17) or this_.Code = @p18) or this_.Code = @p19) or this_.Code = @p20) or this_.Code = @p21) or this_.Code = @p22) or this_.Code = @p23) or this_.Code = @p24) or this_.Code = @p25) or this_.Code = @P26) or this_.Code = @p27) or this_.Code = @p28) or this_.Code = @p29) or this_.Code = @p30) or this_.Code = @p31) or this_.Code = @P32) or this_.Code = @p33) or this_.Code = @p34) or this_.Code = @p35) or this_.Code = @p36) or this_.Code = @P37) or this_.Code = @p38) or this_.Code = @p39) or this_.Code = @p40) or this_.Code = @p41) or this_.Code = @p42) or this_.Code = @P43) or this_.Code = @p44) or this_.Code = @p45) or this_.Code = @p46) or this_.Code = @P47) or this_.Code = @p48) or this_.Code = @p49) or this_.Code = @p50) or this_.Code = @p51) or this_.Code = @p52) or this_.Code = @p53) or this_.Code = @p54) or this_.Code = @p55) or this_.Code = @p56) or this_.Code = @p57) or this_.Code = @p58) or this_.Code = @p59) or this_.Code = @p60) or this_.Code = @p61) or this_.Code = @p62) or this_.Code = @p63) or this_.Code = @p64) or this_.Code = @p65) or this_.Code = @p66) or this_.Code = @p67) or this_.Code = @p68) or this_.Code = @p69) or this_.Code = @p70) or this_.Code = @p71) or this_.Code = @p72) or this_.Code = @p73) or this_.Code = @p74) or this_.Code = @p75) or this_.Code = @p76) or this_.Code = @p77) or this_.Code = @p78) or this_.Code = @p79) or this_.Code = @p80) or this_.Code = @p81) or this_.Code = @p82) or this_.Code = @p83) or this_.Code = @p84) or this_.Code = @p85) or this_.Code = @p86) or this_.Code = @p87) or this_.Code = @p88) or
this_.Code = @p89) or this_.Code = @p90) or this_.Code = @p91) or this_.Code = @p92) or this_.Code = @p93) or this_.Code = @p94) or this_.Code = @p95) or this_.Code = @p96) or this_.Code = @p97) or this_.Code = @p98) or this_.Code = @p99) or this_.Code = @p100) or
this_.Code = @p101) or this_.Code = @p102) or this_.Code = @p103)',N'@p0 nvarchar(4000),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@P4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@P7 nvarchar(4000),@p8 nvarchar(4000),@p9 nvarchar(4000),@p10
nvarchar(4000),@p11 nvarchar(4000),@p12 nvarchar(4000),@P13 nvarchar(4000),@p14 nvarchar(4000),@P15 nvarchar(4000),@p16 nvarchar(4000),@p17 nvarchar(4000),@p18 nvarchar(4000),@p19 nvarchar(4000),@p20 nvarchar(4000),@p21 nvarchar(4000),@p22 nvarchar(4000),@p23
nvarchar(4000),@p24 nvarchar(4000),@p25 nvarchar(4000),@P26 nvarchar(4000),@p27 nvarchar(4000),@p28 nvarchar(4000),@p29 nvarchar(4000),@p30 nvarchar(4000),@p31 nvarchar(4000),@P32 nvarchar(4000),@p33 nvarchar(4000),@p34 nvarchar(4000),@p35 nvarchar(4000),@p36
nvarchar(4000),@P37 nvarchar(4000),@p38 nvarchar(4000),@p39 nvarchar(4000),@p40 nvarchar(4000),@p41 nvarchar(4000),@p42 nvarchar(4000),@P43 nvarchar(4000),@p44 nvarchar(4000),@p45 nvarchar(4000),@p46 nvarchar(4000),@P47 nvarchar(4000),@p48 nvarchar(4000),@p49
nvarchar(4000),@p50 nvarchar(4000),@p51 nvarchar(4000),@p52 nvarchar(4000),@p53 nvarchar(4000),@p54 nvarchar(4000),@p55 nvarchar(4000),@p56 nvarchar(4000),@p57 nvarchar(4000),@p58 nvarchar(4000),@p59 nvarchar(4000),@p60 nvarchar(4000),@p61 nvarchar(4000),@p62
nvarchar(4000),@p63 nvarchar(4000),@p64 nvarchar(4000),@p65 nvarchar(4000),@p66 nvarchar(4000),@p67 nvarchar(4000),@p68 nvarchar(4000),@p69 nvarchar(4000),@p70 nvarchar(4000),@p71 nvarchar(4000),@p72 nvarchar(4000),@p73 nvarchar(4000),@p74 nvarchar(4000),@p75
nvarchar(4000),@p76 nvarchar(4000),@p77 nvarchar(4000),@p78 nvarchar(4000),@p79 nvarchar(4000),@p80 nvarchar(4000),@p81 nvarchar(4000),@p82 nvarchar(4000),@p83 nvarchar(4000),@p84 nvarchar(4000),@p85 nvarchar(4000),@p86 nvarchar(4000),@p87 nvarchar(4000),@p88 nvarchar(4000),@p89 nvarchar(4000),@p90 nvarchar(4000),@p91 nvarchar(4000),@p92 nvarchar(4000),@p93 nvarchar(4000),@p94 nvarchar(4000),@p95 nvarchar(4000),@p96 nvarchar(4000),@p97 nvarchar(4000),@p98 nvarchar(4000),@p99 nvarchar(4000),@p100 nvarchar(4000),@p101 nvarchar(4000),@p102 nvarchar(4000),@p103 nvarchar(4000)',@p0=N'CODE',@p1=N'CODE',@p2=N'CODE',@p3=N'CODE',@P4=N'CODE',@p5=N'CODE',@p6=N'CODE',@P7=N'CODE',@p8=N'CODE',@p9=N'CODE',@p10=N'CODE',@p11=N'CODE',@p12=N'CODE',@P13=N'CODE',@p14=N'CODE',@P15=N'CODE',@p16=N'CODE',@p17=N'CODE',@p18=N'CODE',@p19=N'CODE',@p20=N'CODE',@p21=N'CODE',@p22=N'CODE',@p23=N'CODE',@p24=N'CODE',@p25=N'CODE',@P26=N'CODE',@p27=N'CODE',@p28=N'CODE',@p29=N'CODE',@p30=N'CODE',@p31=N'CODE',@P32=N'CODE',@p33=N'CODE',@p34=N'CODE',@p35=N'CODE',@p36=N'CODE',@P37=N'CODE',@p38=N'CODE',@p39=N'CODE',@p40=N'CODE',@p41=N'CODE',@p42=N'CODE',@P43=N'CODE',@p44=N'CODE',@p45=N'CODE',@p46=N'CODE',@P47=N'CODE',@p48=N'CODE',@p49=N'CODE',@p50=N'CODE',@p51=N'CODE',@p52=N'CODE',@p53=N'CODE',@p54=N'CODE',@p55=N'CODE',@p56=N'CODE',@p57=N'CODE',@p58=N'CODE',@p59=N'CODE',@p60=N'CODE',@p61=N'CODE',@p62=N'CODE',@p63=N'CODE',@p64=N'CODE',@p65=N'CODE',@p66=N'CODE',@p67=N'CODE',@p68=N'CODE',@p69=N'CODE',@p70=N'CODE',@p71=N'CODE',@p72=N'CODE',@p73=N'CODE',@p74=N'CODE',@p75=N'CODE',@p76=N'CODE',@p77=N'CODE',@p78=N'CODE',@p79=N'CODE',@p80=N'CODE',@p81=N'CODE',@p82=N'CODE',@p83=N'CODE',@p84=N'CODE',@p85=N'CODE',@p86=N'CODE',@p87=N'CODE',@p88=N'CODE',@p89=N'CODE',@p90=N'CODE',@p91=N'CODE',@p92=N'CODE',@p93=N'CODE',@p94=N'CODE',@p95=N'CODE',@p96=N'CODE',@p97=N'CODE',@p98=N'CODE',@p99=N'CODE',@p100=N'CODE',@p101=N'CODE',@p102=N'CODE',@p103=N'CODE'

Parsing error

Here is the problematic query:
SELECT ID_POVPNML_P_KLASIFI, AREA_MAX, AREA_MIN, DATURA_ZADNJE_SPREMEMBE FROM NEW TABLE (INSERT INTO IMP.POVPNML_P_KLASIFI (ID_NML_TIP_S_SIF, ID_POVSDIMENZIJA_STANDARD_MEJA, ID_SKUSKLASIFI_VREDNOST_NAZ, ID_SKUSKLASIFI_VREDNOST_OBD, ID_SKUSKLASIFI_VREDNOST_STM, ID_SKUSKLASIFI_VREDNOST_ZLI, ID_SKUSSTANDARD_LASTNOST_REV, OSEBA_ZADNJE_SPREMEMBE, VREDNOST) VALUES ('1', '1', '64', '3', '438', '433', '798', 'usr01526', '380'));

Error message:
Expected: ')' but found: 'INSERT' at Row: 1, Col: 90