Tutorial :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.


Solution: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  


Solution:2

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


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


Solution: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'' '  


Solution:5

This worked in 2008 R2

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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »