how to know if a column is an identity column

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)

how to know if a column is an identity column

Author

Topic

Peter Smith
Starting Member

8 Posts

Posted - 2009-12-17 : 16:55:38

I can select all columns from a table (see SQL below). However, I want to exclude the column that are identity columns

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value"
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id
WHERE SO.xtype = 'U' and SO.NAME='test'

--my pseudo code attempt:
and SO.type<>SYS.IDENTITY_COLUMNS

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-17 : 17:58:40

first of all, see this: http://msdn.microsoft.com/en-us/library/ms187997(SQL.90).aspx

to answer your question...look at sys.columns is_identity field

so something close to this will work

SELECT	SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value"
FROM 	sys.tables SO
INNER JOIN
	sys.columns SC
ON 	SO.id = SC.id 
LEFT JOIN
	syscomments SM
ON 	SC.cdefault = SM.id 
WHERE 	SO.NAME='test'
And	sc.is_identity = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources