未记录的系统过程'sp msforeachtable'和@whereand参数

更新sql2000:

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

这应该在SQL Server 2000中有效(现在无法测试):

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

使用 OBJECTPROPERTY 查找架构所有者ID。

编辑:好的,在SQL 2000框中测试过:

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

我不能让ObjectProperty工作

从 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_%'"
---------------------

此版本在SQL Server 2005中工作:

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

不完全确定是否适用于SQL Server 2000,但此版本可能适用:

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

这在2008年起作用了R2

@其中,and='and uid=(从sys.schemas中选择schema_id,其中name='dbo')和o.name,如“temp_%”