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) )
To get Identity Column with Data, you can use below syntaxinsert into dbo.Customer(FirstName,LastName,Age) Values('Aamir','Shahzad',66), ('Raza','M',44)
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.CustomerWe will see below results.
Video Demo : Get Identity Column values without mentioning Identity Column Name in Select