0

I'm searching all over the internet and stack Overflow but every example I find is with only one row, or it just does not work.

I have this table:

TBL_RELATORIOS_TAMANHOS

table_catalog
table_name
column_name
data_type
character_maximum_length
numeric_precision
numeric_scale

Inside it I have all columns from all tables. I need to find only those columns that has a different precision, size or etc.

With row_number I got this:

;WITH CTE AS
(
SELECT DISTINCT table_name 'Tabela', 
       column_name 'Coluna',
       character_maximum_length as 'Tamanho', 
       numeric_precision as 'Precisao_Numerica', 
       numeric_scale 'Escala_Numerica'
FROM TBL_RELATORIO_TAMANHOS
)
SELECT *,
    ROW_NUMBER() over ( partition by Tabela,Coluna order by Tabela,Coluna)
FROM CTE
ORDER BY 1,2

and with this, I can see columns that has a 2 on it, so this one is duplicated ( same column, different size, precision or etc ).

| table_Name              | Column_name      | character_maximum_length | numeric_precision | numeric_scale | Row_number |
|-------------------------|------------------|--------------------------|-------------------|---------------|------------|
| ACOES                   | ID_BMF           | 20                       | NULL              | NULL          | 1          |
| ACOES                   | NO_ACAO          | 20                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CODIGO_MITIGADOR | 40                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CONTA_DLO        | 10                       | NULL              | NULL          | 1          |
| AJUSTE_CODIGO_MITIGADOR | CONTA_DLO        | 25                       | NULL              | NULL          | 2          |
| AJUSTE_CODIGO_MITIGADOR | VALOR_PADRAO     | 1                        | NULL              | NULL          | 1          |
| AJUSTE_POPR             | CD_ELEMENTO      | 10                       | NULL              | NULL          | 1          |

As you can see, CONTA_DLO appears 2 times. So, how the hell can I list only those columns with more then 1 row_number, but returning all of them ( all duplicates ). in this case. it should return CONTA_DLO with row_number 1 and 2.

  • You are asking the question which you already gave answer to. Its showing all "duplicates" (if you consider duplicate when having same table name and column) and "it should return conta_dlo with row num 1 and 2" thats exactly what is displayed – S4V1N Dec 27 '17 at 12:40
  • No, Look at the question, it says duplicates when only those columns that has a different precision, size or etc. I will have a LOT of ID, but if they're all 100, I don't need them. So, when I select with row_number, and I find a RN=2, I need to bring the RN 2 and the previous RN which is 1. – Racer SQL Dec 27 '17 at 12:49
  • Why not simply where ROW_NUMBER() over ...etc, etc...>1? – Peter Vandivier Dec 27 '17 at 13:04
  • Because it will only list the rowNumber 2. I need both column that is duplicated, to compare the values. I need both RN 2 and RN 1 in this case. that's why in having nightmares with this simple query. – Racer SQL Dec 27 '17 at 13:06
5

So, you want to show only rows that there is more than one with same table and column name.

We can use COUNT(*) OVER (PARTITION BY table_name, column_name) to count how many rows have the same table and column name and then WHERE ... >= 2. Also:

  • I don't think you need the DISTINCT.
  • If you do need the DISTINCT there, it can be with either one more CTE to apply it or use GROUP BY as below.
  • Please don't use single quotes for table and column aliases. If you need to, use square brackets or double quotes.

The query:

  ;
WITH cte AS
(
    SELECT   
        table_name               AS Tabela, 
        column_name              AS Coluna,
        character_maximum_length AS Tamanho, 
        numeric_precision        AS Precisao_Numerica, 
        numeric_scale            AS Escala_Numerica,
        COUNT(*) OVER (PARTITION BY table_name, column_name) 
                                 AS Count_Duplicates
    FROM TBL_RELATORIO_TAMANHOS
    GROUP BY 
        table_name, column_name,  character_maximum_length,
        numeric_precision, numeric_scale
)
SELECT 
    cte.*,
    ROW_NUMBER() OVER (PARTITION BY Tabela, Coluna) AS Rn
FROM cte
WHERE Count_Duplicates >= 2
ORDER BY Tabela, Coluna, Rn 
  ;
  • hey yper. I think the problem is: I need the distinct because there will be a lot of rows with same column, size, and etc ( because i'm returning this data using information_schema.columns from all databases. I will update the question with what I found. Like, Column conta_DLO has a lot of duplicates, but there are 20 columns with x value, and only one with y value, and I need to bring only one with the X value, and the one with the Y value. I could find a way to list this. – Racer SQL Dec 27 '17 at 13:29
  • But thank you, I didnt know about count over. I will read about this. – Racer SQL Dec 27 '17 at 13:32
  • 1
    See my edit then. – ypercubeᵀᴹ Dec 27 '17 at 14:32
0

i could solve this with this query:

;WITH CTE AS
(
SELECT DISTINCT table_name 'Tabela', 
       column_name 'Coluna',
       character_maximum_length as 'Tamanho', 
       numeric_precision as 'Precisao_Numerica', 
       numeric_scale 'Escala_Numerica'
FROM TBL_RELATORIO_TAMANHOS
)
SELECT *,
    ROW_NUMBER() over ( partition by Tabela,Coluna order by Tabela,Coluna) RN
INTO #TEMP2
FROM CTE
WHERE
 Tabela not like '[V_]%' OR 
 Tabela NOT LIKE '[VI_]%' OR 
 Tabela NOT LIKE '[VW_]%'
ORDER BY 1,2
---------------------------------------------------------------------------------------------------

SELECT *
FROM #TEMP2 A
WHERE EXISTS (
            SELECT TABELA, COLUNA, TAMANHO
            FROM #TEMP2 B
            WHERE B.RN > 1
            AND A.TABELA = B.TABELA
            AND A.COLUNA = B.COLUNA
            )
ORDER BY A.TABELA, A.COLUNA

the result:

| AJUSTE_CODIGO_MITIGADOR   | CONTA_DLO     | 10    | NULL  | NULL  | 1     |
| AJUSTE_CODIGO_MITIGADOR   | CONTA_DLO     | 25    | NULL  | NULL  | 2     |
| AMBIENTE                  | NOME          | 100   | NULL  | NULL  | 1     |
| AMBIENTE                  | NOME          | 50    | NULL  | NULL  | 2     |
| ARQUIVO                   | NOME          | 255   | NULL  | NULL  | 1     |
| ARQUIVO                   | NOME          | 100   | NULL  | NULL  | 2     |
| ARQUIVO_CLIENTE           | NO_ARQUIVO    | 20    | NULL  | NULL  | 1     |
| ARQUIVO_CLIENTE           | NO_ARQUIVO    | 256   | NULL  | NULL  | 2     |
| ARQUIVO_CLIENTE           | TIPO_COSIF    | NULL  | 1     | 0     | 1     |
| ARQUIVO_CLIENTE           | TIPO_COSIF    | NULL  | 10    | 0     | 2     |
| BancoDeDados_X_Tabelas    | BancoDeDados  | 256   | NULL  | NULL  | 1     |
| BancoDeDados_X_Tabelas    | BancoDeDados  | 128   | NULL  | NULL  | 2     |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.