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

Question

I am trying to write this query to find all tables with specific column with some specific value. This is what I've done so far -

EXEC sp_MSforeachtable 
@command1='
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=PARSENAME("?",2) AND TABLE_NAME=PARSENAME("?",1) AND COLUMN_NAME="EMP_CODE")
BEGIN
    IF (SELECT COUNT(*) FROM ? WHERE EMP_CODE="HO081")>0
    BEGIN
        SELECT * FROM ? WHERE EMP_CODE="HO081"
    END
END
'

I hope my intensions are clear, I just want to select only those tables where the column EMP_CODE is present and in those tables I want to select those rows where EMP_CODE='HO081'.

Edit -

Now it stands like this. But I'm not able to replace @EMPCODE variable in the query.

DECLARE @EMPCODE AS VARCHAR(20)
SET @EMPCODE='HO081'
EXEC sp_MSforeachtable 
@command1='
    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
',@whereand='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME='''+@EMPCODE+''')'

Damien_The_Unbeliever · Accepted Answer · 2012-03-13T13:08:39.930

51

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.

edited Mar 13 '12 at 13:08

answered Mar 13 '12 at 07:57

Damien_The_Unbeliever

234,701
27
340
448

5

+1 for showing you what you can do, but implicitly telling you to not do it ;) – cairnz Mar 13 '12 at 08:23
@Damien_The_Unbeliever Suppose if I write `IF (SELECT COUNT(*) FROM ? WHERE EMP_CODE='''+@EMPCODE+''')>0` then why is it giving me - incorrect syntax near `+`. I'm trying to pass the `EMP_CODE` value through a variable. – Soham Dasgupta Mar 13 '12 at 11:27
@SohamDasgupta - It's a little tricky to diagnose this kind of issue in the comments section, since I'd really need to see the whole query as it now stands, and that's not going to work in the comments. I would again query that particular piece of code though - why *count* the number of matching rows instead of simply selecting them? Empty result sets are usually quite easy to deal with. – Damien_The_Unbeliever Mar 13 '12 at 11:39
@Damien_The_Unbeliever I'm posting the new query in the question. Please go through. – Soham Dasgupta Mar 13 '12 at 11:46
1

@SohamDasgupta - Added an updated example to the bottom of my answer, and explanation of why it's necessary. I'd question for a *third* time why you're performing a `COUNT` before any further action - it's perfectly fine to run a `SELECT` (as per your original question) or `DELETE` (as per your updated) which doesn't actually affect any rows. Performing the `COUNT` first is redundant and may cause the query to take more time to execute. – Damien_The_Unbeliever Mar 13 '12 at 13:10
@Damien_The_Unbeliever I wish I had another up vote to give. Many thanks. – Soham Dasgupta Mar 14 '12 at 04:46

score 9 · Answer 2 · answered Mar 13 '12 at 08:06

I guess you get an error of some kind, perhaps Invalid column name 'EMP_CODE'?

It's because the code is compiled before you check for the column. You could do like this instead.

EXEC sp_MSforeachtable 
@command1='
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=PARSENAME("?",2) AND TABLE_NAME=PARSENAME("?",1) AND COLUMN_NAME="EMP_CODE")
BEGIN
   EXEC(''
          IF (SELECT COUNT(*) FROM ? WHERE EMP_CODE="HO081")>0
          BEGIN
              SELECT * FROM ? WHERE EMP_CODE="HO081"
          END
        '')
END
'

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

2 Answers2

Linked

Related