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.
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
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