Undocumented System Procedure 'sp_MSforeachtable' and the @whereand parameter

Question

I'm attempting to use the undocumented system procedure sp_MSforeachtable. But I need to restrict the affected tables to those that start with "smp" and that are in the "dbo" schema. I was able to find how to find procedures that start with "smp". I simply do:

sp_MSforeachtable @command1=' print ''?''', @whereand=' and name like ''smp%''  '

but how do I filter for a given schema using the @whereand parameter?

UPDATE: I tried the following but it didn't work:

sp_MSforeachtable @command1=' print ''?''', @whereand=' and name like ''smp%'' and Left(''?'', 5)=''[dbo]'' '

Update 2: I'm running on SQL Server 2000.

Answer 1

Update for SQL2000:

declare @s nvarchar(1000)
set @s = ' and uid = ' + convert(nvarchar, user_id('my_schema'))
exec sp_msforeachtable @command1='print ''?''', @whereand = @s

Answer 2

From here:

---------------------
--Drop table of particular shcemaID/shemaName and with name starting with 'Temp_'
Exec sp_MSforeachtable @command1 = "DROP TABLE ? PRINT '? dropped'"
    ,@whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = 'dbo')
                  and o.name LIKE 'Temp_%'"
---------------------

Answer 3

This should works in SQL Server 2000 (can't test now):

@whereand = '
  AND name like ''smp%'' AND
  OBJECTPROPERTY(OBJECT_ID(''name''), ''OwnerID'') = USER_ID(''dbo'')'

Use OBJECTPROPERTY to find the schema owner id.

Edit: OK, tested it on a SQL 2000 box:

@whereand = ' AND name LIKE ''smp%'' AND uid = 1'
OR
@whereand = ' AND name LIKE ''smp%'' AND USER_ID(''dbo'')'

I could not get OBJECTPROPERTY to work

Answer 4

This verion works in Sql Server 2005:

exec sp_MSforeachtable
    @command1=' print ''?''',
    @whereand=' and schema_name(schema_id) = ''dbo'' '

Not exactly sure for Sql Server 2000, but this version might work:

exec sp_MSforeachtable
    @command1=' print ''?''',
    @whereand=' and user_name(uid) = ''dbo'' '