{"id":4606,"date":"2025-05-08T08:32:24","date_gmt":"2025-05-08T15:32:24","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=4606"},"modified":"2025-05-08T08:34:11","modified_gmt":"2025-05-08T15:34:11","slug":"sql-analysis-dotnet-tool","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/sql-analysis-dotnet-tool\/","title":{"rendered":"Avoid T-SQL anti-patterns with the free T-SQL analysis tool"},"content":{"rendered":"

<\/i>Guest Post<\/strong><\/p>Erik Ejlskov Jensen is a Principal Specialist at Delegate A\/S with over 30 years of experience in .NET Data development, EF Core, and Azure SQL Database\/SQL Server. He is the author of the well-known EF Core Power Tools and is now introducing his latest tool T-SQL Analyzer in this blog post. Thanks Erik!<\/div><\/p>\n

T-SQL Analyzer is a free, open-source, cross platform command line tool for identifying, and reporting the presence of anti-patterns and design issues in SQL Server T-SQL scripts. As a database developer, you can catch any potential bad practices and design problems very early in the project process – this ensures a frictionless path to deployment from development and test environments to production.<\/p>\n

The tool evaluates your scripts with more than 140 rules<\/a> for design, naming and performance issues. The rules are created by Microsoft and the SQL Server community.<\/p>\n

If you already maintain your SQL scripts in a SQL Database project, use build analysis as described in the blog post here.<\/a><\/p>\n

Installation<\/h2>\n

The tool is a .NET 8 tool, and requires the .NET 8 runtime to be installed.<\/p>\n

dotnet tool install<\/span> --global<\/span> ErikEJ.DacFX.TSQLAnalyzer.Cli\n<\/code><\/p>\n

Usage<\/h2>\n

Once installed, you can start using the tool right away. Running the tool from the command prompt in any folder will analyze all .sql files in that folder and sub-folders<\/p>\n

tsqlanalyze<\/code><\/p>\n

The tool will output a summary of the rules that were violated, and the line numbers where the violations occurred.<\/p>\n

CREATE<\/span> TABLE<\/span> [<\/span>dbo<\/span>].[Demo<\/span>Table<\/span>]<\/span>\r\n(<\/span>\r\n    [<\/span>Id<\/span>]<\/span> INT<\/span> NOT<\/span> NULL<\/span>,<\/span> \r\n    [<\/span>Sample<\/span>]<\/span> NCHAR<\/span>(<\/span>500<\/span>)<\/span> NOT<\/span> NULL<\/span>,<\/span>\r\n    [<\/span>Data<\/span>]<\/span> NCHAR<\/span>(<\/span>10<\/span>)<\/span> NOT<\/span> NULL<\/span>\r\n)<\/span><\/code><\/pre>\n
To get help on the options available in the tool, use the help feature:<\/p>\n
tsqlanalyze --help<\/code><\/p>\n
For example, to analyze a single file, use this syntax:<\/p>\n
tsqlanalyze -i C:\\code\\demotable.sql<\/code><\/p>\n
Advanced usage<\/h2>\n
Let me highlight some of the more advanced options the tool supports.<\/p>\n
Ignore rules<\/h3>\n
If you would like to ignore some of the rules, you can add a rule exclusion filter to the command line.<\/p>\n
tsqlanalyze -i C:\\code\\sproc.sql -r Rules:-SqlServer.Rules.SRD0005;-Smells.*<\/code><\/p>\n
This will cause the tool to ignore the SRD0005 rule and any rules in the Smells rule set.<\/p>\n
Format script<\/h3>\n
The tool can also help you format your script, with the -f option.<\/p>\n
tsqlanalyze -i C:\\code\\sproc.sql -f<\/code><\/p>\n
The available formatting options can be customized in an .editorconfig file<\/a>.<\/p>\n
And much more…<\/h3>\n
There are multiple other advanced options, including the ability to analyze a .dacpac file or a live database, output analysis results as xml, integration with SQL Server management Studio and Visual Studio.<\/p>\n
You can also bring your own rules and use the analysis engine from your own code! Read more the the readme file<\/a> for the tool.<\/p>\n
VS Code MCP Server (preview)<\/h2>\n
You can use the tool to ask GitHub Copilot analyze your SQL Server CREATE scripts in Visual Studio Code, just add the built-in MCP server<\/a>\u00a0to your VS Code configuration.<\/p>\n
Paste this link in you browser to prompt VS Code to configure the MCP Server.<\/p>\n
vscode:mcp\/install?%7B%22name%22%3A%22tsqlanalyzer%22%2C%22command%22%3A%22tsqlanalyze%22%2C%22args%22%3A%5B%22-mcp%22%5D%7D<\/p>\n
or install manually:<\/p>\n
{\r\n    \"servers\"<\/span>: {\r\n        \"tsqlanalyzer\"<\/span>: {\r\n            \"type\"<\/span>: \"<\/span>stdio\"<\/span><\/span>,\r\n            \"command\"<\/span>: \"<\/span>tsqlanalyze\"<\/span><\/span>,\r\n            \"args\"<\/span>: [\r\n                \"<\/span>-mcp\"<\/span><\/span>\r\n            ]\r\n        }\r\n    }\r\n}<\/pre>\n
You can now ask GitHub Copilot in Agent mode to help you check your SQL script for bad practices.<\/p>\n
You will get the results presented by Copilot, who will even offer to fix the script for you!<\/p>\n
\"image<\/a><\/p>\n
Parting words<\/h2>\n
I hope you will find this free tool to be a useful addition to your developer toolbox. Please reach out via the GitHub repository<\/a> with and feedback and ideas you may have, and happy SQL coding.<\/p>\n","protected":false},"excerpt":{"rendered":"
T-SQL Analyzer is a free, open-source, cross platform command line tool for identifying, and reporting the presence of anti-patterns and design issues in SQL Server T-SQL scripts. As a database developer, you can catch any potential bad practices and design problems very early in the project process – this ensures a frictionless path to deployment […]<\/p>\n","protected":false},"author":7621,"featured_media":4610,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[444,1,619,625],"tags":[666],"class_list":["post-4606","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-net","category-azure-sql","category-t-sql","category-vs-code","tag-best-pratices"],"acf":[],"blog_post_summary":"
T-SQL Analyzer is a free, open-source, cross platform command line tool for identifying, and reporting the presence of anti-patterns and design issues in SQL Server T-SQL scripts. As a database developer, you can catch any potential bad practices and design problems very early in the project process – this ensures a frictionless path to deployment […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/4606","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/7621"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=4606"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/4606\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/4610"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=4606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=4606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=4606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\"analyze<\/a><\/p>\n