Link to home
Start Free TrialLog in
Avatar of huangs3
huangs3Flag for Canada

asked on

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

May I ask why you want to do that?
There are more practical solutions to not allow DML (Data Management Language).
Avatar of huangs3

ASKER

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.
Avatar of huangs3

ASKER

Limiting the DML from DB account may be a technical option, but I want to make it safe from the code first.
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.
Avatar of huangs3

ASKER

Thanks for your try. It is ok.
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of huangs3

ASKER

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.