Início / dba / Perguntas / 295195
Accepted
user2368632
Asked: 2021-07-03 10:04:36 +0800 CST

Identifique todas as tabelas com uma coluna de identidade

  • 772

Existe uma consulta que eu possa executar que me mostre todas as tabelas com uma coluna de identidade? Tentando descobrir se existem tabelas baseadas em identidade cuja identidade atual está se aproximando do limite superior de int32 ou int64.

sql-server sql-server-2014

3 respostas

  1. Best Answer

    Há uma coluna em sys.columnsque identifica as colunas de identidade ( is_identity).

    Além disso, há uma IDENT_CURRENT()função que você pode usar para identificar o valor de identidade gerado mais recentemente para uma tabela para determinar se você está se aproximando do int max. ( documentos )

    Você pode simplesmente consultar isso para identificar todas as tabelas com uma coluna de identidade (e qual é a coluna de identidade) fazendo algo assim:

    SELECT 
    TableName       = o.name,
    ColumnName      = c.name,
    IdentityCurrent = IDENT_CURRENT( o.name ) ,
    TypeName        = t.name
FROM sys.objects AS o
JOIN sys.columns AS c
    ON c.object_id = o.object_id
    AND c.is_identity = 1
JOIN sys.types AS t
    ON t.system_type_id = c.system_type_id
WHERE o.type = 'U'; --User tables

    Você pode adicionar à WHEREcláusula para adicionar critérios adicionais para capturar apenas as tabelas que estão se aproximando demais para o conforto como base para gerar seu alerta.

    • 7

  2. Isso também se baseiasys.identity_columns :

    SELECT 
    Q1.*,
    inserts_remaining = 
        CONVERT(decimal(38), 
            (V.max_value - V.last_value) / V.increment_value),
    percentage_used = 
        CONVERT(decimal(5, 2),
        V.last_value * 100 / V.max_value)
FROM 
(
    SELECT
        [object_name] =
            OBJECT_SCHEMA_NAME(IC.[object_id]) + 
            N'.' +
            OBJECT_NAME(IC.[object_id]),
        column_name = IC.[name],
        data_type = TYPE_NAME(IC.user_type_id),
        IC.seed_value,
        IC.increment_value,
        IC.last_value,
        max_value = 
            CASE SQL_VARIANT_PROPERTY(IC.seed_value, 'BaseType')
                WHEN N'tinyint' THEN '255'
                WHEN N'smallint' THEN '32767'
                WHEN N'int' THEN '2147483647'
                WHEN N'bigint' THEN '9223372036854775807'
                ELSE REPLICATE('9', IC.[precision])
            END
    FROM sys.identity_columns AS IC
    WHERE
        OBJECTPROPERTYEX(IC.[object_id], 'IsUserTable') = 1
) AS Q1
CROSS APPLY
(
    VALUES
        (
            CONVERT(decimal(38), Q1.last_value),
            CONVERT(decimal(38), Q1.increment_value),
            CONVERT(decimal(38), Q1.max_value)
        )
) AS V (last_value, increment_value, max_value)
ORDER BY
    percentage_used DESC;
    • 2

  3. Eu escrevi um script (baseado em sys.identity_columns) há muito tempo para essa finalidade.

    Fiz uma tentativa de incluir uma estimativa para a data em que você ficará sem valores de identidade, assumindo uma taxa de inserção padrão (você pode configurar esta opção).

    • 1

relate perguntas