Finding tables with identity column using SQL

   Part - 27

Link for video answer:

SQL Scripts:

--**************************************************************************************

-- Q27 -- Query to find tables with Identity columns in SQL

--**************************************************************************************

--> Identity Column

--> IDENTITY [ (seed , increment) ]

CREATE TABLE new_employees   

(  

 Empid int IDENTITY(1,1),  --> Identity column

 EmpName varchar (20),  

 Salary int,  

 EmpLocation varchar(30)  

);  

sp_help new_employees

insert new_employees values ('Richard',10000,'US'),('John',20000,'UK'),('George',25000,'India')

insert new_employees values (4,'Richard',10000,'US')

-- IDENTITY_INSERT is ON

SELECT * FROM new_employees

--> Method 1:

--> To Find Table and column names with identity columns 

select object_name(object_id) AS TABLE_NAME, Name AS COLUMN_NAME, * from sys.identity_columns 

select object_name(object_id)AS TABLE_NAME, Name AS COLUMN_NAME,  seed_value, increment_value from sys.identity_columns 

--> Method 2:

select object_name(object_id) AS TABLE_NAME, name AS COLUMN_NAME,* from sys.columns where is_identity = 1

--> Method 3:

--> To fect the Table Name and Column Name of Identity Tables:

select TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS

where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

order by TABLE_NAME 

select * from INFORMATION_SCHEMA.COLUMNS

--> To fetch Table_name, identity column name, seed, increment, max identity value

select TABLE_NAME, COLUMN_NAME, IDENT_SEED(TABLE_SCHEMA + '.' + TABLE_NAME) AS Seed, 

IDENT_INCR(TABLE_SCHEMA + '.' + TABLE_NAME) AS Increment,

IDENT_CURRENT(TABLE_SCHEMA + '.' + TABLE_NAME) AS Current_Identity_Value

from INFORMATION_SCHEMA.COLUMNS

where COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

order by TABLE_NAME 

-- COLUMNPROPERTY( id , column , property )   

SELECT DISTINCT OBJECT_ID('tblCountry')

--> Method 4:

SELECT 

tables.TABLE_NAME,

IDENT_SEED(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) AS Seed,

IDENT_INCR(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) AS Increment,

IDENT_CURRENT(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME) AS Current_Identity_Value

FROM INFORMATION_SCHEMA.TABLES tables 

WHERE OBJECTPROPERTY(OBJECT_ID(tables.TABLE_SCHEMA + '.' + tables.TABLE_NAME), 'TableHasIdentity') = 1

AND tables.TABLE_TYPE = 'BASE TABLE'

GO

SELECT * FROM INFORMATION_SCHEMA.TABLES

--> OBJECT PROPERTIES

TableHasIdentity

TableHasPrimaryKey

TableHasForeignKey

TableHasIndex

TableHasClustIndex

TableHasNonclustIndex

TableIsMemoryOptimized

TableInsertTrigger

TableTemporalType