[dba-SQLServer] ?s on sp_MSForEachTable

Asger Blond ab-mi at post3.tele.dk
Wed Jan 14 09:53:23 CST 2009


I tried this on both AdventureWorks using SQL Server 2000 and
AdventureWorks2008 using SQL Server 2005, and I *did* get error messages for
failing deletes due to constraint violations, as expected.
Don't know why the error messages are missing in your setup.

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Susan
Harkins
Sendt: 14. januar 2009 14:56
Til: Discussion concerning MS SQL Server
Emne: Re: [dba-SQLServer] ?s on sp_MSForEachTable

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
> 

_______________________________________________
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