How to Get Identity Column Values without mentioning Identity Column Name in Select - SQL Server / T-SQL Tutorial Part 46

Scenario:

You took a quick look on Table and Columns in SQL Server but could not find if Identity Property is enabled on one of the column. You don't want to use system tables to find out the column name if identity is enabled or not but want to get the column name with data. How would you do that?

Solution:

You can use $Identity in your select, that will return all the values for identity Column.

Let's say we have table customer with couple of records.

CREATE TABLE Customer (
    CustomerId INT Identity(1, 1)
    ,FirstName VARCHAR(50)
    ,LastName VARCHAR(50)
    ,Age SMALLINT
    ,PhoneNumber CHAR(9)
    ,DOB DATE
    ,Gender CHAR(1)
    )

insert into dbo.Customer(FirstName,LastName,Age)
    Values('Aamir','Shahzad',66),
    ('Raza','M',44)

To get Identity Column with Data, you can use below syntax

Select $identity from SchemaName.TableName

To get the identity column with values from our dbo.Customer table, we can use below script

Select $Identity from dbo.Customer

We will see below results.

How to get Identity Column Name and Values without using System tables in SQL Server


Video Demo : Get Identity Column values without mentioning Identity Column Name in Select