Utilisation MSFOREACHTABLE de SQL Server sp pour sélectionner seulement les tables qui répondent à certaines conditions

j'essaie d'écrire cette requête pour trouver toutes les tables avec une colonne spécifique avec une valeur spécifique. C'est ce que j'ai fait jusqu'à présent - 

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
'

j'espère que mes intentions sont claires, je veux juste sélectionner seulement les tableaux où la colonne EMP_CODE est présent, et dans ces tables, je veux sélectionner les lignes où EMP_CODE='HO081'.

Edit -

maintenant c'est comme ça. Mais je ne suis pas en mesure de remplacer @EMPCODE variable dans le requête.

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+''')'
17
demandé sur Arulkumar 2012-03-13 11:34:53

2 réponses

Vous savez comment sp_MSforeachtable est sans-papiers, et peuvent disparaître à tout moment, être modifié?

Eh bien, si vous êtes heureux d'ignorer cela, il a un autre paramètre appelé @whereand, qui est ajouté au WHERE l'article de la requête interne qui est utilisé pour trouver les tables (et doit commencer par AND).

Vous devez aussi savoir qu'il y a un alias, o contre sysobjects, et un second alias syso contre sys.all_objects.

en utilisant ceci de connaissances, vous pouvez créer votre @whereand paramètre:

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

vous pouvez maintenant aussi simplifier votre command1, puisque vous savez qu'il ne sera exécuté que contre des tables contenant un EMP_CODE colonne. J'enlèverais probablement le COUNT(*) condition aussi, puisque je ne vois pas quelle valeur il ajoute.

mise à Jour basé sur la poursuite de votre travail, et testé contre une 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'')'

(j'ai inversé le @whereand à la requête pour EMP_CODE, puisque vous ne voulez pas remplacer la valeur y).

Le problème est que, vous pouvez passer paramètres pour une procédure stockée, ou littéraux, mais vous ne pouvez pas effectuer des calculs / combiner des actions entre eux-donc j'ai déplacé la construction de la déclaration sql dans une action séparée.

46
répondu Damien_The_Unbeliever 2012-03-13 13:08:39

je suppose que tu auras une erreur de quelque sorte, peut-être Invalid column name 'EMP_CODE'?

c'est parce que le code est compilé avant de vérifier la colonne. Tu pourrais faire comme ça à la place.

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
'
9
répondu Mikael Eriksson 2012-03-13 08:06:22