主页 / dba / 问题 / 295195
Accepted
user2368632
Asked: 2021-07-03 10:04:36 +0800 CST

使用标识列标识所有表

  • 772

我可以运行一个查询来显示所有带有标识列的表吗？试图找出是否有任何基于身份的表，其当前身份接近 int32 或 int64 的上限。

sql-server sql-server-2014

3 个回答

  1. Best Answer

    上有一列sys.columns标识标识列 ( is_identity)。

    此外，IDENT_CURRENT()您可以使用一个函数来识别表的最近生成的标识值，以确定您是否正在接近 int max。（文档）

    您可以通过执行以下操作来简单地查询以识别具有标识列的每个表（以及标识列是什么）：

    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

    您可以添加到该WHERE子句以添加其他条件，以仅捕获距离太近而无法舒适的表作为生成警报的基础。

    • 7

  2. 这也是基于sys.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. 为此，我很久以前写了一个脚本（基于）。sys.identity_columns

    假设默认插入率（您可以配置此选项），我尝试包括估计您将用完标识值的日期。

    • 1

相关问题