what is the equivalent of EXPLAIN form SQLite in SQL Server?

Question

I used an SQLite database and run an EXPLAIN statement before executing the actual query to verify if there was any attempt to write on the database.

Now, we have migrated to SQL Server and I need to know if a query tries to write on the database or is just a simple SELECT statement. I basically try to avoid any malicious statement.

Jarl

Any reason why you don't just run the query with a user/role/application role that doesn't have any DML/DDL permissions? – Ferullo

Indeed; as Damien says, with SQL Server the way to go is to simply create a user that can't write to the database, and use that. Trying to do clever things with analysing query plans is insanely complicated and problem-prone in comparison. – Lubricate

Answer 1

You can see the estimated query plan of any query in SSMS by clicking the estimated query plan button.

See MSDN.

---

However, if the user shouldn't be writing to the database, is shouldn't have the permissions to do so. Ensure it belongs to a role that has restricted permissions.

Labroid

Answer 2

If you do decide to go this route, you could do the following:

set showplan_xml on
go
set noexec on
go
select * from sysobjects
go
set noexec off
go
set showplan_xml off
go

This will return 3 result sets containing a single column of XML. The 2nd result set is the query plan for the actual query (in this case, select * from sysobjects)

But as noted in my comment, you'd be better off preventing the user having permissions to make any changes.

It's also possible to craft statements that are "only" selects but that are also pretty malicious. I could easily write a select that exclusively locks every table in the database and takes an hour to run.

Ferullo

It's a better way than GUI if for some reason you can only execute queries on the remote server! – Notation