top - SQL Server sp_msforeachtable use para seleccionar solo aquellas tablas que cumplan con alguna condición

update sql

Question

Estoy intentando escribir esta consulta para encontrar todas las tablas con una columna específica con algún valor específico. Esto es lo que he hecho hasta ahora.

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

Espero que mis intenciones sean claras, solo quiero seleccionar aquellas tablas donde está presente la columna EMP_CODE y en esas tablas quiero seleccionar aquellas filas donde EMP_CODE=''HO081'' .

Editar -

Ahora está así. Pero no puedo reemplazar la variable @EMPCODE en la consulta.

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+'''''')''

Answer 1

¿Sabe cómo sp_MSforeachtable no está documentado y puede desaparecer en cualquier momento / ser modificado?

Bueno, si está @whereand a ignorarlo, tiene otro parámetro llamado @whereand , que se adjunta a la cláusula WHERE de la consulta interna que se está utilizando para encontrar las tablas (y debe comenzar con un AND ).

También debes saber que hay un alias, o contra sysobjects , y un segundo alias syso contra sys.all_objects .

Usando este conocimiento, puede crear su parámetro @whereand como:

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

Ahora también puede simplificar su command1 , ya que sabe que solo se ejecutará en tablas que contengan una columna EMP_CODE . Probablemente eliminaría la condición COUNT(*) también, ya que no veo qué valor está agregando.

Actualizado en base a su trabajo posterior, y probado en una tabla:

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'''')''

(He revertido @whereand para consultar EMP_CODE , ya que no desea reemplazar el valor allí).

El problema es que puede pasar parámetros a un procedimiento almacenado, o literales , pero no puede realizar cálculos / acciones combinadas entre ellos, por lo que trasladé la construcción de la declaración SQL a una acción separada.

Answer 2

Supongo que recibe un error de algún tipo, ¿tal vez un Invalid column name ''EMP_CODE'' ?

Es porque el código se compila antes de verificar la columna. Podrías hacer esto así.

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