2

I have a batch statement which I am going to execute via sp_executesql or Exec:

Declare @query1 varchar(max), @query2  varchar(max)
Set @query1 = '

select top 10 * from sysobjects;
select top 10 * from sysColumns;'

Set @query2 = '
select top 10 * from sysobjects
select top 10 * from sysColumns
select top 10 * from sysColumns
'

Exec(@query1)

Exec(@query2)

Is there any way by which I can know how many select/insert/update/delete statements or queries are there in a single batch ?

For above Eg; the answer is 2 & 3 respectively

For clarity : I do not decide the batch statements, it directly comes to me via a stored procedure parameter.

I just need to know how many queries is the server executing in this batch.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Abdul Rehman Sayed
  • 6,532
  • 7
  • 45
  • 74
  • Can we assume that every statement ends with `;`? – Lukasz Szozda Sep 28 '15 at 08:14
  • I cannot be sure about this hence i gave both as egs.. – Abdul Rehman Sayed Sep 28 '15 at 08:16
  • 1
    The only way is to check the execution plan, eg as [shown here](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan). You can't guess at the number of statements without parsing the query. Once you have the execution plan in XML format you can use OPENXML to check how many statements appear in the plan – Panagiotis Kanavos Sep 28 '15 at 08:19

1 Answers1

1

You can use SET SHOWPLAN_ALL Option. Unfortunately you can't save and analyse the output (at least not in TSQL - here is a way to do this with ADO Objects)

SET SHOWPLAN_ALL ON
GO

DECLARE @sql NVARCHAR(MAX)

SET @sql = '
SELECT * FROM dbo.Test 
SELECT TOP 1 * FROM dbo.Test'

EXECUTE sp_executesql @sql
GO

SET SHOWPLAN_ALL OFF
GO
Community
  • 1
  • 1
CeOnSql
  • 2,615
  • 1
  • 16
  • 38