Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 547 other subscribers

Blog Views

1.789.868 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • November 16th, 2015
  • 2
  • Database DMVs and Catalog Views Maintenance SQL Server

Identifying tables with IDENTITY columns in SQL Server

Views: 1.920
Reading Time: <1 minute

Hello people,
Good Morning!

In this quick post I will demonstrate how to identify tables that have IDENTITY columns in SQL Server. Often we need to identify which tables are these, what is the current value of identity and what is the value of the table seed and of course we will not look table by table.

For this, we can quickly identify this information with the query below:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
    IDENT_SEED(TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'
ORDER BY
    Current_Identity DESC

Example:
SQL Server - Tables with identity columns

Identifying the columns that have the Identity property:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    ORDINAL_POSITION,
    NUMERIC_PRECISION,
    NUMERIC_SCALE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY
    TABLE_NAME

Example:

To the next!

sql, sql server, tables with identity, columns identity, How to get current identity number of specific table, how to retrieve current value of identity, last value of identity

tags: columnidentitysqlsql servertable

You may also like ...

  • FREE Database Check-Up + Security Analysis: Do You Need It?

  • SQL Server - Using Resource Governor for greater control over server resources

  • SQL Server Security Checklist - An SP with more than 70 security items to validate your database.

  • Next How to convert milliseconds, seconds, or minutes to TIME in SQL Server
  • Previous Mapping Object Dependencies in SQL Server

Comments

  • Comments2
  • Pingbacks0
  1. Felipe Oliveira dos Santos said:
    2 July 2020 to 16: 08

    Hey Dirceu, how are you?

    First thanks for sharing your knowledge!

    I would like help with a question, in addition to bringing all this information related to identity, would you also have the name of the column that would be identity?

    Att
    Felipe Oliveira

    Reply
    • Dirceu Resende said:
      2 July 2020 to 16: 31

      Oops, I edited the post 🙂

      Reply

Leave a Comment Cancel reply

Dirceu Resende © 2021. All Rights Reserved.