Need a Tool to Automatically Indent and Format SQL Server Stored Procedures

Need a tool to automatically indent and format SQL Server stored procedures

Here's a couple -- no idea how well they work, unfortunately...

http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm (free)

http://www.sqlinform.com/ (free for personal use)

Notepad++/Eclipse sql code auto-indent option?

Yes, there is a free/open-source T-SQL formatting plugin for Notepad++, called "Poor Man's T-SQL Formatter". It is available in the NPP "Plugin Manager" plugin list (gets updated every once in a while automatically), and is also available for manually downloading/installing from here: http://www.architectshack.com/PoorMansTSqlFormatter.ashx

If you just want to check it out without adding to notepad++, you can check the online formatting site:

http://poorsql.com

(disclaimer: I wrote these tools :))

SQL Server 2005: How to automatically include database name and brackets in queries

Sorry, not from drag and drop from object explorer.

One of the tools suggested in this question may help:

Need a tool to automatically indent and format SQL Server stored procedures

CTRL + K, CTRL + F (Format Code) Not Working in MS SQL Server

Do you have any add-ins installed? For me Ctrl+K, Ctrl+F brings up the SSMS Tools Pack find dialog. So perhaps this is happening because an add-in has taken over the keyboard combo.

Also, what is your keyboard scheme under Tools > Options > Environment > Keyboard? If this has been switched to "SQL Server 2000" you might try switching it back to Standard.

Finally, I believe there are cases where the keyboard shortcuts can get messed up depending on the co-existence and order of install for Visual Studio / BIDS etc.

Json output format from SQL Server 2017

To start, you can test if a JSON String is valid with ISJSON. The expected output you indicated does not pass validation, but is close. It is missing a "[]" for the inner array.

However, I see where you were going with this. To better explain what I think the issue you are running into is, I am going to beautify the format of the output JSON from your query to match your expected JSON.

Original output as follows:

[
    {"Event":
        {"ID":"236","Date":"2019-03-01",
            "Activity":{
                "ID":10,"Date":"2019-01-02T11:47:33.2800000","Notes":"Event created"
            }
        }
    },
    {"Event":
        {"ID":"236","Date":"2019-03-01",
            "Activity":{
                "ID":20,"Date":"2019-01-02T11:47:34.3933333","Notes":"Staff selected"}
        }
    },
    {"Event":
        {"ID":"236","Date":"2019-03-01",
            "Activity":{
                "ID":20,"Date":"2019-01-02T11:47:34.3933333","Notes":"Staff selected"
            }
        }
    }
]

Based on your ideal format, a possible valid JSON string would be as follows:

{"Event":
    [
        {"ID":236,"Date":"2019-03-01",
            "Activity":
                [
                    {"ID":10,"Date":"2019-01-02T11:47:33.2800000","Notes":"Event created"},
                    {"ID":20,"Date":"2019-01-02T11:47:34.3933333","Notes":"Staff selected"},
                    {"ID":20,"Date":"2019-01-02T11:47:34.3933333","Notes":"Staff selected"}
                ]
        }
    ]
}

You can achieve this by adjusting your table alias for the second table and using FOR JSON AUTO and ROOT. It will return an output with the "Event" attributes not repeated for each of its "EventActivities". For each "Event" it will put its related "EventActivities" into an array instead.

The following SQL will return the desired output:

SELECT [Event].EventID AS 'ID',
    [Event].EventDate AS 'Date',
    Activity.ActivityID AS 'ID',
    Activity.CreateDate AS 'Date',
    Activity.Notes AS 'Notes'
FROM #Events [Event]
JOIN #EventActivities Activity
    ON [Event].EventID = Activity.EventID
FOR JSON AUTO, ROOT('Event')

The exact output for this will be as follows:

{"Event":[{"ID":236,"Date":"2019-03-01","Activity":[{"ID":10,"Date":"2019-01-02T11:47:33.2800000Z","Notes":"Event created"},{"ID":20,"Date":"2019-01-02T11:47:33.2800000Z","Notes":"Staff selected"},{"ID":20,"Date":"2019-01-02T11:47:33.2800000Z","Notes":"Staff selected"}]}]}

It will not return in a beautified format, but spaces and indentation can be added without compromising the fact that it is valid JSON, while also achieving the intended array requirements.

---

Related Topics