How to judge whether an SQL statement is a query or execution

Question

How to judge whether an SQL statement is SELECT or UPDATE/DELETE/...?

How to develop a tool similar to SSMS?

Answer 1

For an example of SSMS alternative, see https://github.com/Microsoft/azuredatastudio.

In order to determine if a script return data like a SELECT query, I think that you can try something like this:

declare @text as nvarchar(max) = 'declare @x int = 123; select @x'
select * from sys.dm_exec_describe_first_result_set( @text, null, 0)

If this special function returns rows, then the checked script returns data.

In case of DELETE, for example, nothing is returned:

set @text = 'delete from Users'
select * from sys.dm_exec_describe_first_result_set( @text, null, 0)

However, an SQL script could include several kinds of statements, calls of stored procedures, etc.

Answer 2

Hi @xiao zhongshuai ,

Simply put, SELECT/UPDATE/DELETE statements start with SELECT/UPDATE/DELETE respectively. You can intuitively determine whether a piece of code is a SELECT, UPDATE, or DELETE statement.
For example, the following code is a SELECT statement:

    SELECT FirstName, LastName, StartDate AS FirstDay    
    FROM DimEmployee     
    WHERE EndDate IS NOT NULL     
    AND MaritalStatus = 'M'    
    ORDER BY LastName;  

UPDATE statement:

UPDATE dbo.Table1     
    SET c2 = c2 + d2     
    FROM dbo.Table2     
    WHERE CURRENT OF abc;    
    GO    
    SELECT c1, c2 FROM dbo.Table1;    
    GO    

DELETE statement:

    DELETE Production.ProductCostHistory    
    WHERE StandardCost BETWEEN 12.00 AND 14.00    
          AND EndDate IS NULL;    
    PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));  

Sorry,I don’t know much about development tools. You can find professional forums for help.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.