Accidently deleted all data in the 'master' database

Question

I've used this script by accident on the 'master' database instead of a temp database.

sp_msforeachtable 'delete from ?'

Has it caused any harm? If so, how can I restore the data?

Answer 1

No it shouldn't have deleted anything (assuming you have no user tables in master).

Testing

exec sys.sp_MSforeachtable 'select ''?'''

Doesn't return anything for me. So it seems to exclude the system tables such as spt_values.

Edit: Indeed Looking at the definition of the procedure it does only include tables where OBJECTPROPERTY(o.id, N'IsUserTable') = 1

Answer 2

Martin Smith is right to say that sp_MSforeachtable does not delete system tables.

However, though we may think of tables such as spt_values and MSreplication_options as system tables, they are in fact user tables according to SQL Server.

When I run this query in my master database:

SELECT name, OBJECTPROPERTY(object_id, N'IsUserTable') AS IsUserTable
FROM master.sys.tables;

I see the following result set:

name                  IsUserTable
--------------------- -----------
spt_fallback_db       1
spt_fallback_dev      1
spt_fallback_usg      1
spt_monitor           1
MSreplication_options 1

So how was Stijn saved from a reinstall?

If you look at how sp_MSforeachtable is implemented, you will see it does something like this to select the tables to drop:

declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

SELECT *
from dbo.sysobjects o join sys.all_objects syso on o.id = syso.object_id 
where OBJECTPROPERTY(o.id, N'IsUserTable') = 1 and o.category & @mscat = 0;

In my master database, this returns an empty result set.

The where clause applies a bitmask to the category column of table sysobjects to exclude tables that are not 'mscat'.

So the tables in the master database are protected not because they are system tables, but because they are 'Microsoft' tables.

This use of the category column is completely undocumented in Books Online All it has is a vague description:

Used for publication, constraints, and identity.

But the sysobjects table is deprecated anyway, so you shouldn't be using it. :)

An equivalent query using the supported view sys.tables would look like this:

SELECT *
FROM sys.tables
WHERE is_ms_shipped = 0;

In my master database, this also returns an empty result set.