Parse and validate a SQL query against a database schema

Question

I'm improving a software where it is possible to create a database, whereby you can define the entities and the properties of the entities, and is translated to SQL queries in order to create/update the database schema.

Also, it is possible to run some tasks, and one of the tasks is based in SQL queries.

And this is the catch. Because we allow the configuration of the system to the users (power users) it is error prone. To avoid that we use the Microsoft.SqlServer.Management.SqlParser.Parser.Parse() method to parse the query. This parse the syntax without run it, and it is great. But I want to do the next step and validate against the schema... But the schema (sometimes) can only exist in our description language, not physically in database (until it is deploy).

I want to validate not only the syntax of the query, as well the query against the database schema without deploy it. For my research the SqlParser was some Metadata classes. Anyone use it? Or try to accomplish this task in other way?

benjamin moskovits · Accepted Answer · 2015-03-31 14:49:55Z

1

As a developer DBA I have had challenges similar to yours. What I did was create a VM for each developer with the current SQL Server build and used SQL Server projects to deploy each developer's changes.

answered Mar 31, 2015 at 14:49

benjamin moskovits

5,5441 gold badge14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Авдеев Вячеслав · Accepted Answer · 2022-01-22 17:41:55Z

Too late for topic starter, but may be useful for those who came later.

I found 2 ways to validate queries against DB schema

Way 1. Weaker but old.

set fmtonly on

here is you query

set fmtonly off

Way 2. Stronger but younger.

sp_describe_first_result_set @tsql = N'

here is your query

'

Both ways fails if something went wrong and both ways can't validate 100% of your queries. I think nothing can validate 100% your queries. For example, I can't validate a queries with #TempTables. Also, I can't validate an insert statement with incomplete column list (skip 1 NOT null column in INSERT column into specification, this statement will fail, but both checks are green).

As a result of my research, I've made a Visual Studio extension for validate SQL queries in the code. There is also a command line util for same purpose. The project is not super cool, the project is incomplete, but useful, we're using it at my job with success. Take a look if you are interested.

Collectives™ on Stack Overflow

Parse and validate a SQL query against a database schema

2 Answers 2

Comments

Way 1. Weaker but old.

Way 2. Stronger but younger.

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Way 1. Weaker but old.

Way 2. Stronger but younger.

Comments

Your Answer

Sign up or log in

Post as a guest

Related