SQL Server sp_msforeachtable используется для выбора только тех таблиц, которые удовлетворяют некоторому условию

Я пытаюсь написать этот запрос, чтобы найти все таблицы с определенным столбцом с определенным значением. Это то, что я сделал до сих пор -

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
'

Я надеюсь, что мои интенции ясны, я просто хочу выбрать только те таблицы, где присутствует столбец EMP_CODE, и в этих таблицах я хочу выбрать те строки, где EMP_CODE='HO081'.

Изменить -

Теперь это так. Но я не могу заменить переменную @EMPCODE в запросе.

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

Ответы

Ответ 1

Вы знаете, как sp_MSforeachtable недокументирован и может уйти в любое время/быть измененным?

Хорошо, если вы с удовольствием проигнорируете это, у него есть еще один параметр с именем @whereand, который добавляется к предложению WHERE внутреннего запроса, который используется для поиска таблиц (и должен начинаться с AND).

Вы также должны знать, что есть псевдоним o против sysobjects, а второй псевдоним syso против sys.all_objects.

Используя эти знания, вы можете создать свой @whereand параметр как:

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

Теперь вы можете упростить свой command1, так как вы знаете, что он будет запускаться только с таблицами, содержащими столбец EMP_CODE. Я бы, вероятно, также вынул условие COUNT(*), так как не вижу, какое значение он добавляет.

Обновлено на основе вашей дальнейшей работы и проверено на одну таблицу:

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='''[email protected]+'''
    IF @COUNT>0
    BEGIN
        PRINT PARSENAME("?",1)+'' => ''+CONVERT(VARCHAR,@COUNT)+'' ROW(S)''
        --PRINT ''DELETE FROM ''+PARSENAME("?",1)+'' WHERE EMP_CODE='''''[email protected]+'''''''
    END
'
EXEC sp_MSforeachtable 
@[email protected],@whereand='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME=''EMP_CODE'')'

(Я вернул запрос @whereand для запроса EMP_CODE, так как вы не хотите заменять его там).

Проблема в том, что вы можете передавать параметры хранимой процедуре или литералам, но вы не можете выполнять вычисления/комбинировать действия между ними, поэтому я переместил конструкцию выражения sql в отдельное действие.

Ответ 2

Я предполагаю, что вы получили какую-то ошибку, возможно, Invalid column name 'EMP_CODE'?

Это потому, что код компилируется перед проверкой столбца. Вместо этого вы можете сделать это.

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
'