SQL Server sp_msforeachtable usage to select only those tables which meet some condition

by

You know how sp_MSforeachtable is undocumented, and may go away at any time/be modified?

Well, if you’re happy to ignore that, it has another parameter called @whereand, which is appended to the WHERE clause of the internal query that is being used to find the tables (and should start with an AND).

You also have to know that there’s an alias, o against sysobjects, and a second alias syso against sys.all_objects.

Using this knowledge, you might craft your @whereand parameter as:

EXEC sp_MSforeachtable 
@command1='...',
@whereand='AND o.id in (select object_id from sys.columns c where c.name=""EMP_CODE'')'

You can now also simplify your command1, since you know it will only be run against tables containing an EMP_CODE column. I’d probably take out the COUNT(*) condition also, since I don’t see what value it’s adding.

Updated based on your further work, and tested against one table:

DECLARE @EMPCODE AS VARCHAR(20)
SET @EMPCODE='HO081'
declare @sql nvarchar(2000)
set @sql="
    DECLARE @COUNT AS INT
    SELECT @COUNT=COUNT(*) FROM ? WHERE EMP_CODE="''+@EMPCODE+'''
    IF @COUNT>0
    BEGIN
        PRINT PARSENAME("?",1)+'' => ''+CONVERT(VARCHAR,@COUNT)+'' ROW(S)''
        --PRINT ''DELETE FROM ''+PARSENAME("?",1)+'' WHERE EMP_CODE='''''+@EMPCODE+'''''''
    END
'
EXEC sp_MSforeachtable 
@command1=@sql,@whereand='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME=''EMP_CODE'')'

(I’ve reverted the @whereand to query for EMP_CODE, since you don’t want to replace the value there).

The issue is that, you can pass parameters to a stored procedure, or literals, but you can’t perform calculations/combining actions between them – so I moved the construction of the sql statement out into a separate action.

More Related Contents:

  1. SQL Server query to find all permissions/access for all users in a database
  2. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  3. Why use the INCLUDE clause when creating an index?
  4. Retrieve column definition for stored procedure result set
  5. Is it possible to force row level locking in SQL Server?
  6. Regular Expressions in SQL Server servers?
  7. How do I find a stored procedure containing ?
  8. SQL Server GUID sort algorithm. Why?
  9. What happens to the primary key Id when it goes over the limit?
  10. Drop primary key using script in SQL Server database [duplicate]
  11. How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005
  12. SQL Server recursive query
  13. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  14. nvarchar(max) vs NText
  15. INFORMATION_SCHEMA vs sysobjects
  16. “Prevent saving changes that require the table to be re-created” negative effects
  17. What are row, page and table locks? And when they are acquired?
  18. Table Valued Function Killing My Query Performance
  19. Populate Dataset With Table Names From Stored Procedure
  20. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  21. How to add Active Directory user group as login in SQL Server
  22. An attempt to login using SQL authentication failed
  23. Calling stored procedure using VBA
  24. How to find SQL Server running port?
  25. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  26. Changing SQL Server collation to case insensitive from case sensitive?
  27. How to store directory / hierarchy / tree structure in the database?
  28. Eliminate and reduce overlapping date ranges
  29. Merge multiple rows into a single row
  30. Does query plan optimizer works well with joined/filtered table-valued functions?

Leave a Comment