How do you determine what SQL Tables have an identity column programmatically

Question

I want to create a list of columns in SQL Server 2005 that have identity columns and their corresponding table in T-SQL   Results would be something like   TableName  ColumnName

User · Answer

In SQL 2005   select object name object id   name from sys columns where is identity   1

User · Answer

This query seems to do the trick   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

User · Answer

By some reason sql server save some identity columns in different tables  the code that work for me  is the following   select      TABLE NAME tabla COLUMN NAME columna from        INFORMATION SCHEMA COLUMNS where       COLUMNPROPERTY object id TABLE SCHEMA     TABLE NAME   COLUMN NAME   IsIdentity     1 union all select      o name tabla  c name columna from        sys objects o  inner join  sys columns c on o object id   c object id where       c is identity   1

User · Answer

The following query work for me   select  TABLE NAME tabla COLUMN NAME columna from    INFORMATION SCHEMA COLUMNS where   COLUMNPROPERTY object id TABLE SCHEMA     TABLE NAME   COLUMN NAME   IsIdentity     1 order by TABLE NAME

User · Answer

I think this works for 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

User · Answer

List of tables without Identity column based on Guillermo answer   SELECT DISTINCT TABLE NAME FROM            INFORMATION SCHEMA COLUMNS WHERE         TABLE SCHEMA    dbo   AND  OBJECTPROPERTY OBJECT ID TABLE NAME    TableHasIdentity     0  ORDER BY TABLE NAME

User · Answer

here s a working version for MSSQL 2000  I ve modified the 2005 code found here  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 amp 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  lt  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  gt    threshold             THEN  warning  approaching max limit          ELSE  okay          END AS  id status  FROM  identityStatus ORDER BY percentLeft      Clean up after ourselves    DROP TABLE  identityStatus

User · Answer

sys columns is identity   1  e g    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

User · Answer

sys columns is identity   1  e g    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

User · Answer

This worked for SQL Server 2005  2008  and 2012   I found that the sys identity columns did not contain all my tables with identity columns   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    Looking at the documentation page the status column can also be utilized  Also you can add the four part identifier and it will work across different servers   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    Source  https   msdn microsoft com en-us library ms186816 aspx

User · Answer

here s a working version for MSSQL 2000  I ve modified the 2005 code found here  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 amp 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  lt  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  gt    threshold             THEN  warning  approaching max limit          ELSE  okay          END AS  id status  FROM  identityStatus ORDER BY percentLeft      Clean up after ourselves    DROP TABLE  identityStatus

User · Answer

sys columns is identity   1  e g    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

User · Answer

Another way  for 2000   2005 2012 2014    IF   SELECT OBJECTPROPERTY  OBJECT ID N table name here     TableHasIdentity      1      PRINT  Yes  ELSE     PRINT  No    NOTE  table name here should be schema table  unless the schema is dbo

User · Answer

The following query work for me   select  TABLE NAME tabla COLUMN NAME columna from    INFORMATION SCHEMA COLUMNS where   COLUMNPROPERTY object id TABLE SCHEMA     TABLE NAME   COLUMN NAME   IsIdentity     1 order by TABLE NAME

User · Answer

List of tables without Identity column based on Guillermo answer   SELECT DISTINCT TABLE NAME FROM            INFORMATION SCHEMA COLUMNS WHERE         TABLE SCHEMA    dbo   AND  OBJECTPROPERTY OBJECT ID TABLE NAME    TableHasIdentity     0  ORDER BY TABLE NAME

User · Answer

This query seems to do the trick   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

User · Answer

This worked for me using Sql Server 2008   USE  lt database name gt   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

User · Answer

sys columns is identity   1  e g    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

User · Answer

Another way  for 2000   2005 2012 2014    IF   SELECT OBJECTPROPERTY  OBJECT ID N table name here     TableHasIdentity      1      PRINT  Yes  ELSE     PRINT  No    NOTE  table name here should be schema table  unless the schema is dbo

User · Answer

I think this works for 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

User · Answer

Another way  for 2000   2005 2012 2014    IF   SELECT OBJECTPROPERTY  OBJECT ID N table name here     TableHasIdentity      1      PRINT  Yes  ELSE     PRINT  No    NOTE  table name here should be schema table  unless the schema is dbo

User · Answer

In SQL 2005   select object name object id   name from sys columns where is identity   1

User · Answer

This worked for me using Sql Server 2008   USE  lt database name gt   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

User · Answer

I think this works for 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

User · Answer

This query seems to do the trick   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

User · Answer

Another potential way to do this for SQL Server  which has less reliance on the system tables  which are subject to change  version to version  is to use the INFORMATION SCHEMA views   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

User · Answer

Another potential way to do this for SQL Server  which has less reliance on the system tables  which are subject to change  version to version  is to use the INFORMATION SCHEMA views   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

User · Answer

In SQL 2005   select object name object id   name from sys columns where is identity   1

User · Answer

By some reason sql server save some identity columns in different tables  the code that work for me  is the following   select      TABLE NAME tabla COLUMN NAME columna from        INFORMATION SCHEMA COLUMNS where       COLUMNPROPERTY object id TABLE SCHEMA     TABLE NAME   COLUMN NAME   IsIdentity     1 union all select      o name tabla  c name columna from        sys objects o  inner join  sys columns c on o object id   c object id where       c is identity   1

User · Answer

Another way  for 2000   2005 2012 2014    IF   SELECT OBJECTPROPERTY  OBJECT ID N table name here     TableHasIdentity      1      PRINT  Yes  ELSE     PRINT  No    NOTE  table name here should be schema table  unless the schema is dbo

User · Answer

This worked for SQL Server 2005  2008  and 2012   I found that the sys identity columns did not contain all my tables with identity columns   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    Looking at the documentation page the status column can also be utilized  Also you can add the four part identifier and it will work across different servers   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    Source  https   msdn microsoft com en-us library ms186816 aspx

User · Answer

Use this    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

User · Answer

This query seems to do the trick   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

User · Answer

Another potential way to do this for SQL Server  which has less reliance on the system tables  which are subject to change  version to version  is to use the INFORMATION SCHEMA views   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

User · Answer

Another potential way to do this for SQL Server  which has less reliance on the system tables  which are subject to change  version to version  is to use the INFORMATION SCHEMA views   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

User · Answer

In SQL 2005   select object name object id   name from sys columns where is identity   1

User · Answer

I think this works for 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

User · Answer

Use this    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

[sql-server] How do you determine what SQL Tables have an identity column programmatically

Examples related to sql-server

Examples related to tsql

Examples related to metadata

Examples related to identity-column