none
Retornar valor da coluna utilizando o sys.columns RRS feed

  • Pergunta

  • Galera,

    Estou fazendo uma trigger para auditoria de tabelas. Minha trigger percorre as colunas da tabela para guardar os seus valores, porém, ao montar a select, o que está me retornando é o próprio nome da coluna e não o seu valor. O meu contexto é um pouco complexo, mas eu gerei um script de exemplo para exemplificar o que eu necessito. Segue abaixo:

    DROP TABLE TabelaTeste
    
    CREATE TABLE [dbo].[TabelaTeste] (
    	[id] [int] NOT NULL
    	,[nome] [varchar](500) NOT NULL
    	,[email] [varchar](500) NOT NULL
    	) ON [PRIMARY]
    GO
    
    DELETE FROM  TabelaTeste
    
    INSERT INTO TabelaTeste (
    	id
    	,nome
    	,email
    	)
    VALUES (
    	1
    	,'Jose'
    	,'jose@aol.com'
    	)
    
    INSERT INTO TabelaTeste (
    	id
    	,nome
    	,email
    	)
    VALUES (
    	2
    	,'Maria'
    	,'maria@aol.com'
    	)
    
    INSERT INTO TabelaTeste (
    	id
    	,nome
    	,email
    	)
    VALUES (
    	3
    	,'João'
    	,'joao@aol.com'
    	)
    
    DECLARE @ValorCampo AS VARCHAR(max)
    DECLARE @campo AS SYSNAME
    
    DECLARE Cursor_Tabela CURSOR
    FOR
    SELECT campo.NAME AS Coluna_BD
    FROM sys.tables tabela
    INNER JOIN sys.columns campo ON (tabela.object_id = campo.object_id)
    WHERE tabela.NAME = 'TabelaTeste'
    
    OPEN Cursor_Tabela
    
    FETCH NEXT
    FROM Cursor_Tabela
    INTO @campo
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	SELECT @ValorCampo = (
    			SELECT TOP 1 @campo
    			FROM TabelaTeste
    			WHERE id = 1
    			)
    	
    	-- AQUI EU QUERIA QUE RETORNASSE O VALOR DO CAMPO PERCORRIDO E NÃO O SEU NOME!!!
    	PRINT 'Valor : ' + @valorCampo
    
    	FETCH NEXT
    	FROM Cursor_Tabela
    	INTO @campo
    END
    
    CLOSE Cursor_Tabela
    
    DEALLOCATE Cursor_Tabela
    

    Se alguém puder me ajudar ficarei muito grato!
    sexta-feira, 7 de junho de 2013 14:32

Respostas

  • fhalves,

    Os dados não ficam na syscolumns, nesta system table você vai ter somente informações sobre as tabelas e não sobre seus dados.

    Se você deseja pesquisar o mesmo valor em diversas colunas em diversas tabelas, acho que este exemplo ajuda:

    -- Parte 1: Verificando a existência das Tabelas --
    If Exists(Select Object_Id('Tabela1'))
     Begin 
      Drop Table Tabela1 
    
      CREATE TABLE Tabela1 
       (Coluna1 INT, 
        Coluna2 INT, 
        Coluna3 INT)
     End
     Else
      Begin
       CREATE TABLE Tabela1 
        (Coluna1 INT, 
         Coluna2 INT, 
         Coluna3 INT)
      End  
    
    If Exists(Select Object_Id('Tabela2'))
     Begin
      Drop Table Tabela2 
    
      CREATE TABLE Tabela2 
       (Coluna1 INT, 
        Coluna2 INT, 
        Coluna3 INT)
     End
     Else
      Begin
       CREATE TABLE Tabela2 
        (Coluna1 INT, 
         Coluna2 INT, 
         Coluna3 INT)
      End   
     
    If Exists(Select Object_Id('Tabela3'))
     Begin
      Drop Table Tabela3 
    
      CREATE TABLE Tabela3 
       (Coluna1 INT, 
        Coluna2 INT, 
        Coluna3 INT)
     End
      Else
      Begin
       CREATE TABLE Tabela3 
        (Coluna1 INT, 
         Coluna2 INT, 
         Coluna3 INT)
      End  
      
    -- Parte 2: Inserindo os registros --
    INSERT INTO Tabela1 VALUES (1, 2, 3)
    INSERT INTO Tabela1 VALUES (0, 7, 9)
    INSERT INTO Tabela1 VALUES (3, 4, 2)
    
    INSERT INTO Tabela2 VALUES (2, 2, 2)
    INSERT INTO Tabela2 VALUES (3, 9, 5)
    INSERT INTO Tabela2 VALUES (1, 6, 8)
    
    INSERT INTO Tabela3 VALUES (4, 0, 7)
    INSERT INTO Tabela3 VALUES (6, 5, 1)
    INSERT INTO Tabela3 VALUES (4, 7, 9) 
    
    -- Parte 3: Declarando as variáveis --
    DECLARE @Comando VARCHAR(1000), 
                    @ComandoTransact VARCHAR(100), 
                    @ValordePesquisa INT,
                    @TABLE_NAME VARCHAR(20), 
                    @Coluna_NAME VARCHAR(20)
    
    Set @Comando = '' 
    Set @TABLE_NAME = '' 
    Set @Coluna_NAME = ''
    
    Set @ComandoTransact = 'SELECT ''?'', ''^'', COUNT(*) AS TOTAL FROM ? WHERE ^ = @ UNION ALL' + CHAR(10)
    
    Set @ValordePesquisa = 3 -- Informe o valor a ser pesquisado no Mecanismo.
    
    -- Parte 4: Declarando o CursordePesquisa para retornar o nome da tabela e nome da Coluna --
    DECLARE CursordePesquisa CURSOR FAST_FORWARD
    FOR SELECT TABLE_NAME, Column_Name FROM INFORMATION_SCHEMA.Columns
    
    -- Abrindo o CursordePesquisa --
    OPEN CursordePesquisa
    FETCH NEXT FROM CursordePesquisa INTO @TABLE_NAME, @Coluna_NAME
    
    -- Parte 5: Iniciando o bloco condicional While --
    WHILE @@FETCH_STATUS = 0
     BEGIN
    
      SET @Comando = @Comando + REPLACE(REPLACE(REPLACE(@ComandoTransact,'?',@TABLE_NAME),'^',@Coluna_NAME),'@',@ValordePesquisa)
      FETCH NEXT FROM CursordePesquisa 
      INTO @TABLE_NAME, @Coluna_NAME
     END
    
    -- Parte 6: Realizando a Concatenação e União dos Selects --
    SET @Comando = LEFT(@Comando,LEN(@Comando)-LEN('UNION ALL')-2)
    
    -- Parte 7: Criando a Tabela Temporária para armazenar os Resultados --
    CREATE TABLE #Resultados 
     (NomeTabela Varchar(20), 
      NomeColuna VARCHAR(20), 
      TotaldeRegistros INT)
    
    -- Inserindo os dados na tabela Resultados com base na execução do @Comando --
    INSERT INTO #Resultados 
    Exec (@Comando)
    
    -- Encerrando o Cursor --
    CLOSE CursordePesquisa
    DEALLOCATE CursordePesquisa
    
    -- Apresentando os dados --
    SELECT * FROM #Resultados
    
    -- Excluíndo a Tabela Temporária Resultados --
    DROP TABLE #Resultados


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]


    sexta-feira, 7 de junho de 2013 14:44

Todas as Respostas

  • fhalves,

    Os dados não ficam na syscolumns, nesta system table você vai ter somente informações sobre as tabelas e não sobre seus dados.

    Se você deseja pesquisar o mesmo valor em diversas colunas em diversas tabelas, acho que este exemplo ajuda:

    -- Parte 1: Verificando a existência das Tabelas --
    If Exists(Select Object_Id('Tabela1'))
     Begin 
      Drop Table Tabela1 
    
      CREATE TABLE Tabela1 
       (Coluna1 INT, 
        Coluna2 INT, 
        Coluna3 INT)
     End
     Else
      Begin
       CREATE TABLE Tabela1 
        (Coluna1 INT, 
         Coluna2 INT, 
         Coluna3 INT)
      End  
    
    If Exists(Select Object_Id('Tabela2'))
     Begin
      Drop Table Tabela2 
    
      CREATE TABLE Tabela2 
       (Coluna1 INT, 
        Coluna2 INT, 
        Coluna3 INT)
     End
     Else
      Begin
       CREATE TABLE Tabela2 
        (Coluna1 INT, 
         Coluna2 INT, 
         Coluna3 INT)
      End   
     
    If Exists(Select Object_Id('Tabela3'))
     Begin
      Drop Table Tabela3 
    
      CREATE TABLE Tabela3 
       (Coluna1 INT, 
        Coluna2 INT, 
        Coluna3 INT)
     End
      Else
      Begin
       CREATE TABLE Tabela3 
        (Coluna1 INT, 
         Coluna2 INT, 
         Coluna3 INT)
      End  
      
    -- Parte 2: Inserindo os registros --
    INSERT INTO Tabela1 VALUES (1, 2, 3)
    INSERT INTO Tabela1 VALUES (0, 7, 9)
    INSERT INTO Tabela1 VALUES (3, 4, 2)
    
    INSERT INTO Tabela2 VALUES (2, 2, 2)
    INSERT INTO Tabela2 VALUES (3, 9, 5)
    INSERT INTO Tabela2 VALUES (1, 6, 8)
    
    INSERT INTO Tabela3 VALUES (4, 0, 7)
    INSERT INTO Tabela3 VALUES (6, 5, 1)
    INSERT INTO Tabela3 VALUES (4, 7, 9) 
    
    -- Parte 3: Declarando as variáveis --
    DECLARE @Comando VARCHAR(1000), 
                    @ComandoTransact VARCHAR(100), 
                    @ValordePesquisa INT,
                    @TABLE_NAME VARCHAR(20), 
                    @Coluna_NAME VARCHAR(20)
    
    Set @Comando = '' 
    Set @TABLE_NAME = '' 
    Set @Coluna_NAME = ''
    
    Set @ComandoTransact = 'SELECT ''?'', ''^'', COUNT(*) AS TOTAL FROM ? WHERE ^ = @ UNION ALL' + CHAR(10)
    
    Set @ValordePesquisa = 3 -- Informe o valor a ser pesquisado no Mecanismo.
    
    -- Parte 4: Declarando o CursordePesquisa para retornar o nome da tabela e nome da Coluna --
    DECLARE CursordePesquisa CURSOR FAST_FORWARD
    FOR SELECT TABLE_NAME, Column_Name FROM INFORMATION_SCHEMA.Columns
    
    -- Abrindo o CursordePesquisa --
    OPEN CursordePesquisa
    FETCH NEXT FROM CursordePesquisa INTO @TABLE_NAME, @Coluna_NAME
    
    -- Parte 5: Iniciando o bloco condicional While --
    WHILE @@FETCH_STATUS = 0
     BEGIN
    
      SET @Comando = @Comando + REPLACE(REPLACE(REPLACE(@ComandoTransact,'?',@TABLE_NAME),'^',@Coluna_NAME),'@',@ValordePesquisa)
      FETCH NEXT FROM CursordePesquisa 
      INTO @TABLE_NAME, @Coluna_NAME
     END
    
    -- Parte 6: Realizando a Concatenação e União dos Selects --
    SET @Comando = LEFT(@Comando,LEN(@Comando)-LEN('UNION ALL')-2)
    
    -- Parte 7: Criando a Tabela Temporária para armazenar os Resultados --
    CREATE TABLE #Resultados 
     (NomeTabela Varchar(20), 
      NomeColuna VARCHAR(20), 
      TotaldeRegistros INT)
    
    -- Inserindo os dados na tabela Resultados com base na execução do @Comando --
    INSERT INTO #Resultados 
    Exec (@Comando)
    
    -- Encerrando o Cursor --
    CLOSE CursordePesquisa
    DEALLOCATE CursordePesquisa
    
    -- Apresentando os dados --
    SELECT * FROM #Resultados
    
    -- Excluíndo a Tabela Temporária Resultados --
    DROP TABLE #Resultados


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]


    sexta-feira, 7 de junho de 2013 14:44
  • Obrigado Júnior!

    Sua alternativa me levou solucionar o problema de uma maneira um pouco diferente, utilizando array ao invés de uma tabela temporária, resta saber se isto não vai ter um custo muito grande no banco, pois este procedimento irá ser executado a cada alteração no banco...

    sexta-feira, 7 de junho de 2013 19:15
  • Deleted
    domingo, 9 de junho de 2013 08:56