[dba-SQLServer] ?s on sp_MSForEachTable

Susan Harkins ssharkins at gmail.com
Wed Jan 14 07:55:47 CST 2009


Maybe you can help. When I run the following statement

EXEC sys.sp_MSForEachTable 'DELETE FROM ?'

I should get an error on tables with constraints to other tables. 
Interesting, when I run TRUNCATE TABLE, I see those errors. With DELETE 
FROM, I see a message that the statement's been completed successfully 
instead of seeing the expected error messages for related tables. Should I 
see an error message for these tables or does it just happily delete what it 
can and ignore the tables it can't?

Susan H.

>I am, and so is Tim Chapman, who has written a couple of extensions to it,
> available from www.techrepubic.com
>
>> I ran the following statement against a copy of AdventureWorks, expecting
>> to
>> see some constraint errors:
>>
>> EXEC sp_MSForEachTable 'DELETE FROM ?'
>>
>> Instead, the command was completely successfully -- by message, but of
>> course, no data was deleted, which is what the statement should do --
>> delete
>> all the records from every table in the database. Of course, I didn't
>> expect
>> it to work because of the constraints, but I did expect to see some kind 
>> of
>> error message.
>>
>> It's an undocumented sp and despite my efforts to figure this out, I'm 
>> not
>> finding any help. I've found a lot of information on the different
>> arguments
>> and how it's supposed to run, but answers to the above question -- why 
>> not
>> error message -- can't find that sort of information.
>>
>> Is anyone familiar with this sp?
>>
>> Susan H.
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 




More information about the dba-SQLServer mailing list