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

Other Forums

SQL Server 6.5 \ SQL Server 7.0

Identity

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-22 : 04:52:55

Dear All,
I am using this query to retrive the column information of a table.How can decide whether a column has identity property or not /

select * from information_schema.columns where table_name='Mytable'

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-22 : 05:07:40

I am using sql server 2000..it is having columnproperty() function..chect it in 7.0

SELECT COLUMNPROPERTY( OBJECT_ID('your table'),'ur column','IsIdentity')

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-22 : 05:10:41

Thanks pbGuy,
But i dont the know the which column is has indentity to true.If i know then i can use this.But without knowing this how can i?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-22 : 05:15:46

select * from information_schema.columns where table_name='Mytable'
and COLUMNPROPERTY( OBJECT_ID(table_name),column_name,'Isidentity')=1

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 05:16:50

try

select name as column_name from syscolumns where autoval is not null and id = object_id('Mytable')

Not sure these existed in SQL Server 7

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-22 : 05:24:02

Dear Madhi and Khtan,
Both of them worked.Can you please explain the codings
Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 05:31:53

think this is self explainary.

From SQL Server 2000 BOL.


OBJECT_ID ( 'object' ) 
Returns the database object identification number.

COLUMNPROPERTY ( id , column , property ) 
Returns information about a column or procedure parameter.

IsIdentity 
The column uses the IDENTITY property. 1 = TRUE, 0 = FALSE, NULL = Invalid input 

syscolumns
Contains one row for every column in every table and view, and a row for each parameter in a stored procedure. This table is in each database.

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-23 : 02:12:44

Thanks Khan,
How can get the autoincreament value(Wheather it is 1 or etc)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-23 : 02:20:54

[code]Select IDENT_INCR('TABLE-NAME')[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-23 : 06:29:04

Thanks Harsh,
But I faced on problem.In my table i have two fileds as identity.One's valus is set as the 1 as in the increment value,and another one has been set as 2 as the increment value.In this case this query returns the value as 1,which is wrong.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-23 : 06:42:38

quote:
Originally posted by danasegarane76

Thanks Harsh,
But I faced on problem.In my table i have two fileds as identity.One's valus is set as the 1 as in the increment value,and another one has been set as 2 as the increment value.In this case this query returns the value as 1,which is wrong.

Thats incorrect
Only one identity column per table is allowed

Madhivanan

Failing to plan is Planning to fail

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-05-23 : 06:54:19

Sorry Madhi,
While i tried to convert the other column as the indetity,it automatically changed the other columns identity to false.I didnt relized that.Sorry.It is working now.

Thanks once again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources