Count of DROP Statements Executed for sp_MSforeachtable

Question

enter image description here Am dropping all the table names in DB using

EXEC sp_MSforeachtable 'DROP TABLE ?'

Is there a way i can get count on number of tables dropped.

Thanks, Peru

gbn · Accepted Answer · 2013-04-08T11:51:25.437

1

DECLARE @before int;

SELECT @before = COUNT(*) FROM sys.tables;
EXEC sp_MSforeachtable 'DROP TABLE ?'
SELECT @before - COUNT(*) FROM sys.tables

edited Apr 08 '13 at 11:51

answered Apr 08 '13 at 11:34

gbn

SELECT COUNT(*) - @before FROM sys.tables returning NULL – user2067567 Apr 08 '13 at 11:40
The first SELECT assigns it. It will be zero. http://stackoverflow.com/questions/2552086/does-count-always-return-a-result/2552102#2552102 – gbn Apr 08 '13 at 11:41
am getting NULL. What does the second SELECT Does ? First returns numeric second SELECT returns NULL – user2067567 Apr 08 '13 at 11:46
added screenshot of results to my question – user2067567 Apr 08 '13 at 11:46
The 2nd select calculates the difference between before and after. To get NULL you have to have NULL in @before. I've tried this and it works for me – gbn Apr 08 '13 at 11:52
This will not work accurately if a parallel proccess is also dropping tables... I wonder if is there somenthig like @@ROWCOUNT but for drop statements. – Hugo Allexis Cardona Aug 24 '16 at 05:07

1 Answers1