CODEWITHSUNDEEP
c#sqlsql-serverparsingdatabase-schema
joaoasrosa
joaoasrosa

Reputation: 301

Parse and validate a SQL query against a database schema

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?

Upvotes: 0

Views: 1007

Answers (2)

Авдеев Вячеслав
Авдеев Вячеслав

Reputation: 11

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.

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

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.

Upvotes: 1

Related Questions