Sp_MSforeachtable undocumented system procedure and @whereand parameter

Question

I am trying to use the undocumented system procedure sp_MSforeachtable . But I need to limit the affected tables to those that start with "smp" and are in the " dbo " dbo . I was able to find how to find procedures starting with "smp". I just do:

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

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

UPDATE : I tried the following, but this did not work:

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

Update 2 . I am working 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

This should work 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 identifier.

Edit: OK, tested it in a SQL 2000 window:

 @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 3

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 4

This example runs on Sql Server 2005:

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

Not quite sure about Sql Server 2000, but this version may work:

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

Answer 5

It worked in 2008 R2

@whereand = 'and uid = (SELECT schema_id FROM sys.schemas WHERE name =' 'dbo' ') and o.name LIKE' 'TEMP _%' ''