Columns - SQL Server Forums

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 2000 Forums

SQL Server Administration (2000)

Columns

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-30 : 03:31:18

Dear All,
How can I get the column names and identity value for a particular table using one query

Dana

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-30 : 03:36:19

[code]select column_name, ident_incr('table_name')
from information_schema.columns
where table_name = 'table_name' and columnproperty(object_id(table_name), column_name, 'IsIdentity')=1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-30 : 05:27:23

Thanks Harsh,
But i need to list the column names ,not the count .Sorry if i am disturbing too much

Dana

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2007-05-30 : 06:52:39

The sql Harsh provided didnt provide the count. ident_incr - is the incremental value

Try this - this is not much different to what Harsh provided except it shows all columns


USE Northwind
GO

SELECT 	COLUMN_NAME, 
	COLUMNPROPERTY(object_id('Orders'),COLUMN_NAME,'IsIdentity') AS IsIdentity,
	IDENT_INCR('ORDERS') AS INCREMENT_VALUE,
	IDENT_CURRENT('ORDERS') AS CURRENT_VALUE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders'

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources