0

I have a T-SQL script which shows the following after execution:

(1 row(s) affected)
(1 row(s) affected)

I assume this means 2 rows, each in different tables where affected.

Is there a way to make SQL Server also show the names of affected tables? (other than researching the SQL script?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • What is your ultimate goal here? In general you know what tables you are updating (or you should.) – Robert Dec 27 '18 at 16:30
  • 1
    @Robert, I have a complicated SQL script which I got from someone who left. I was thinking since SQL server shows 1 row affected then it should know what table is affected. This would be shortcut time saver instead of me digging into the code. –  Dec 27 '18 at 16:35
  • 3
    `I assume this means 2 rows, each in different tables where affected` - no, it means there were two operations and each affected one row. That may be a row from a single table, a row from a join of several tables, a row generated on the fly, a row from a temporary table or any other kind of row. And regardless of the options, it may have been the same row for both operations. – GSerg Dec 27 '18 at 16:43

2 Answers2

0

There is a way to obtain the Rows and the Tables affected by setting the statistics ON:

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

What will show you something like:

 SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 0 ms. Table 'Your_Table'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected) SQL Server parse and compile time:     CPU time = 0 ms, elapsed time = 0 ms. Table 'Your_Table'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, it shows you not only the Tables affected but also the time the system has spent to process each subquery.

When you don't require them anymore just run:

SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
Angel M.
  • 1,360
  • 8
  • 17
-2

As far as I know, SQL cannot show you the tables but can show the contents for the rows that are being INSERTED, UPDATED (old and new values) & DELETED but it can be too much. E.g.:

UPDATE Products
    SET ProductName = 'Pears'
    OUTPUT Deleted.ProductID, Deleted.ProductName AS OldProductName, Inserted.ProductName AS NewProductName
    WHERE ProductName = 'Peras';

What will show: ProductID OldProductName NewProductName 1 Peras Pears

If you use the same logic for INSERTs you will not see the ficticious DELETED table and if you use DELETE you will not see the INSERTED table.

Angel M.
  • 1,360
  • 8
  • 17
  • Are you referring to Use of Trigger, if that's the case it has no use to me in my case. –  Dec 27 '18 at 18:53
  • I wish we had audit tables on all major tables but that is not the case. –  Dec 27 '18 at 18:54