Click here to Skip to main content
15,885,777 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to fetch whether Primary Key exists for tables with Identity Insert in Sql Server?
I am using below query to fetch tables with Identity insert. I want to check whether Primary Key exists for the same query wise.
SQL
select                        
	c.TABLE_SCHEMA  ,  
	t.TABLE_NAME ,  
	case when charindex('.', c.COLUMN_NAME) > 0 then '"' + c.COLUMN_NAME + '"';
	else  c.COLUMN_NAME end ,          
	 c.DATA_TYPE ,  
	NUMERIC_PRECISION,  
	NUMERIC_SCALE   
	from                         
	information_schema.columns c  
	inner join      
	information_schema.tables    t               
	on t.TABLE_NAME = c.TABLE_NAME  
	where  
	columnproperty(object_id(c.TABLE_NAME),c.COLUMN_NAME, 'IsIdentity') = 1


What I have tried:

Tried query to fetch Tables having columns with Identity Insert with below query
SQL
select                        
	c.TABLE_SCHEMA  ,  
	t.TABLE_NAME ,  
	case when charindex('.', c.COLUMN_NAME) > 0 then '"' + c.COLUMN_NAME +'"'
	else  c.COLUMN_NAME end ,          
	 c.DATA_TYPE ,  
	NUMERIC_PRECISION,  
	NUMERIC_SCALE   
	from                         
	information_schema.columns c  
	inner join      
	information_schema.tables    t               
	on t.TABLE_NAME = c.TABLE_NAME  
	where  
	columnproperty(object_id(c.TABLE_NAME),c.COLUMN_NAME, 'IsIdentity') = 1
Posted
Updated 1-May-16 19:19pm
v2
Comments
George Jonsson 2-May-16 1:11am    
What is the actual problem?
Don't you get expected output, an error message or what happens?
ranio 2-May-16 1:14am    
I want to check whether primary keys exists for tables with Identity Insert Columns query wise

1 solution

 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900