Get the table name which doesnot have IdentityKey

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get the table name which doesnot have IdentityKey

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-11-16 : 06:33:48

Hi,

How can able to get the table name that does not contain IDENTITY key column.

Thanks,
Babu Kumarasamy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-16 : 07:37:49

I think, this should work:

SELECT 
distinct table_name
 FROM information_schema.columns t1
 WHERE not exists(select 1 from sys.columns sc 
                  where [object_id]=object_id(t1.table_name) 
                  and is_identity=1 )
order by table_name

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-16 : 08:50:01

Another method

SELECT table_name from information_schema.columns
group by table_name
having max(COLUMNPROPERTY( OBJECT_ID(table_name),column_name,'Isidentity')) =0
order by table_name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources