sql server - vista - ¿Cómo se determina qué tablas SQL tienen una columna de identidad mediante programación?

modificar columna auto_increment sql server

Question

Quiero crear una lista de columnas en SQL Server 2005 que tengan columnas de identidad y su tabla correspondiente en T-SQL.

Los resultados serían algo así como:

TableName, ColumnName

Answer 1

Creo que esto funciona para SQL 2000:

SELECT 
    CASE WHEN C.autoval IS NOT NULL THEN
        ''Identity''
    ELSE
        ''Not Identity''
    AND
FROM
    sysobjects O
INNER JOIN
    syscolumns C
ON
    O.id = C.id
WHERE
    O.NAME = @TableName
AND
    C.NAME = @ColumnName

Answer 2

En SQL 2005:

select object_name(object_id), name
from sys.columns
where is_identity = 1

Answer 3

Esta consulta parece hacer el truco:

SELECT 
    sys.objects.name AS table_name, 
    sys.columns.name AS column_name
FROM sys.columns JOIN sys.objects 
    ON sys.columns.object_id=sys.objects.object_id
WHERE 
    sys.columns.is_identity=1
    AND
    sys.objects.type in (N''U'')

Answer 4

Esto funcionó para SQL Server 2005, 2008 y 2012. Encontré que sys.identity_columns no contenía todas mis tablas con columnas de identidad.

SELECT a.name AS TableName, b.name AS IdentityColumn
FROM sys.sysobjects a 
JOIN sys.syscolumns b 
ON a.id = b.id
WHERE is_identity = 1
ORDER BY name;

En cuanto a la página de documentación, también se puede utilizar la columna de estado. También puede agregar el identificador de cuatro partes y funcionará en diferentes servidores.

SELECT a.name AS TableName, b.name AS IdentityColumn
FROM [YOUR_SERVER_NAME].[YOUR_DB_NAME].sys.sysobjects a 
JOIN [YOUR_SERVER_NAME].[YOUR_DB_NAME].sys.syscolumns b 
ON a.id = b.id
WHERE is_identity = 1
ORDER BY name;

Fuente: https://msdn.microsoft.com/en-us/library/ms186816.aspx

Answer 5

Esto funcionó para mí usando Sql Server 2008:

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    , t.name AS table_name
    , c.name AS column_name
FROM sys.tables AS t
JOIN sys.identity_columns c ON t.object_id = c.object_id
ORDER BY schema_name, table_name;
GO

Answer 6

Lista de tablas sin columna de identidad basada en la respuesta de Guillermo :

SELECT DISTINCT TABLE_NAME
FROM            INFORMATION_SCHEMA.COLUMNS
WHERE        (TABLE_SCHEMA = ''dbo'') AND (OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ''TableHasIdentity'') = 0)
ORDER BY TABLE_NAME

Answer 7

Otra forma (para 2000 / 2005/2012/2014):

IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N''table_name_here''), ''TableHasIdentity'')) = 1)
    PRINT ''Yes''
ELSE
    PRINT ''No''

NOTA: schema.table debe ser schema.table , a menos que el esquema sea dbo .

Answer 8

Otra forma potencial de hacer esto para SQL Server, que tiene menos confianza en las tablas del sistema (que están sujetas a cambios, versión a versión) es usar las vistas INFORMATION_SCHEMA:

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_SCHEMA+''.''+TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1
order by TABLE_NAME 

Answer 9

Utilizar esta :

DECLARE @Table_Name VARCHAR(100) 
DECLARE @Column_Name VARCHAR(100)
SET @Table_Name = ''''
SET @Column_Name = ''''

SELECT  RowNumber = ROW_NUMBER() OVER ( PARTITION BY T.[Name] ORDER BY T.[Name], C.column_id ) ,
    SCHEMA_NAME(T.schema_id) AS SchemaName ,
    T.[Name] AS Table_Name ,
    C.[Name] AS Field_Name ,
    sysType.name ,
    C.max_length ,
    C.is_nullable ,
    C.is_identity ,
    C.scale ,
    C.precision
FROM    Sys.Tables AS T
    LEFT JOIN Sys.Columns AS C ON ( T.[Object_Id] = C.[Object_Id] )
    LEFT JOIN sys.types AS sysType ON ( C.user_type_id = sysType.user_type_id )
WHERE   ( Type = ''U'' )
    AND ( C.Name LIKE ''%'' + @Column_Name + ''%'' )
    AND ( T.Name LIKE ''%'' + @Table_Name + ''%'' )
ORDER BY T.[Name] ,
    C.column_id

Answer 10

aquí hay una versión funcional para MSSQL 2000. He modificado el código 2005 que se encuentra aquí: http://sqlfool.com/2011/01/identity-columns-are-you-nearing-the-limits/

/* Define how close we are to the value limit
   before we start throwing up the red flag.
   The higher the value, the closer to the limit. */
DECLARE @threshold DECIMAL(3,2);
SET @threshold = .85;

/* Create a temp table */
CREATE TABLE #identityStatus
(
      database_name     VARCHAR(128)
    , table_name        VARCHAR(128)
    , column_name       VARCHAR(128)
    , data_type         VARCHAR(128)
    , last_value        BIGINT
    , max_value         BIGINT
);

DECLARE @dbname sysname;
DECLARE @sql nvarchar(4000);

-- Use an cursor to iterate through the databases since in 2000 there''s no sp_MSForEachDB command...

DECLARE c cursor FAST_FORWARD FOR
SELECT
    name
FROM
    master.dbo.sysdatabases 
WHERE 
    name NOT IN(''master'', ''model'', ''msdb'', ''tempdb'');

OPEN c;

FETCH NEXT FROM c INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sql = N''Use ['' + @dbname + ''];
    Insert Into #identityStatus
    Select '''''' + @dbname + '''''' As [database_name]
        , Object_Name(id.id) As [table_name]
        , id.name As [column_name]
        , t.name As [data_type]
        , IDENT_CURRENT(Object_Name(id.id)) As [last_value]
        , Case 
            When t.name = ''''tinyint''''   Then 255 
            When t.name = ''''smallint''''  Then 32767 
            When t.name = ''''int''''       Then 2147483647 
            When t.name = ''''bigint''''    Then 9223372036854775807
          End As [max_value]
    From 
        syscolumns As id
        Join systypes As t On id.xtype = t.xtype
    Where 
        id.colstat&1 = 1    -- this identifies the identity columns (as far as I know)
    '';

    EXECUTE sp_executesql @sql;

    FETCH NEXT FROM c INTO @dbname;
END

CLOSE c;
DEALLOCATE c;

/* Retrieve our results and format it all prettily */
SELECT database_name
    , table_name
    , column_name
    , data_type
    , last_value
    , CASE 
        WHEN last_value < 0 THEN 100
        ELSE (1 - CAST(last_value AS FLOAT(4)) / max_value) * 100 
      END AS [percentLeft]
    , CASE 
        WHEN CAST(last_value AS FLOAT(4)) / max_value >= @threshold
            THEN ''warning: approaching max limit''
        ELSE ''okay''
        END AS [id_status]
FROM #identityStatus
ORDER BY percentLeft;

/* Clean up after ourselves */
DROP TABLE #identityStatus;

Answer 11

sys.columns.is_identity = 1

p.ej,

select o.name, c.name
from sys.objects o inner join sys.columns c on o.object_id = c.object_id
where c.is_identity = 1