Validate T-SQL text in powershell?

Hi Experts,

I am using Powershell to write a T-SQL query validation to validate it before it actually get executed in database.
I want to check whether it is only a select-statement or involves any table/view drop/created, and return error if there is not only one single select-statement.
I checked the MSDN SQL Server Tool and UI Class Library tool but haven't found the way to do it.
Can you help me to find a way?
Thank you!

Below is the environment I am working with:
 Remote database: SQL Server 2012
 Powershell version on desktop: 4.0
 SQL Server Management Studio on desktop: 2014
 Desktop OS: Win 7
huangs3Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
May I ask why you want to do that?
There are more practical solutions to not allow DML (Data Management Language).
0
huangs3Author Commented:
I am automating the validation of some sql select statements by test running them to make sure they returns some records for some down-the road technical reason. The database account I am using can do everything such as dropping a view. Practically mistake can be made when the sql select statement is submitted to the validation script I write, by including a "DROP" statement (myself have been do that). Hence it seems to be helpful if I can do some syntax validation on the sql statements before actually running them.
0
huangs3Author Commented:
Limiting the DML from DB account may be a technical option, but I want to make it safe from the code first.
0
Top Threats of Q1 & How to Defend Against Them

WEBINAR: Join WatchGuard CTO and our Threat Research Team on Aug. 2nd to hear the findings from our Q1 Internet Security Report! Learn more about the top threats detected in the first quarter and how you can defend your business against them!

Vitor MontalvãoMSSQL Senior EngineerCommented:
SQL Server engine gives you all the tools that you need to secure your databases so writing code to reinvent the wheel doesn't seems to me practical. Anyway I can't help you with Powershell since I'm a SQL Server Expert only.
0
huangs3Author Commented:
Thanks for your try. It is ok.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Feasible, but cumbersome. You would have to parse for SELECT being the first keyword except from comments, no INTO, no CREATE, DROP, ALTER, UPDATE, MERGE keywords (means allowed in strings or as part of an object name) etc.
"Single Select" is even more difficult to check prior to execution - subselects should be allowed, for example, but not two consecutive SELECTs.
T-SQL not requiring a statement delimiter makes it worse, as you are not able to isolate something simple like
select 1
select 2

Open in new window

properly. The above results in two sets of values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huangs3Author Commented:
I think ruling out the most obvious keywords ( CREATE, DROP, ALTER, UPDATE, MERGE) is the feasible thing that can be done. As you say, it cannot find some difficult case such as multi-select, but still better than nothing. Finding the CREATE, DROP, ALTER, UPDATE, MERGE can still make the code safer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.