identity column from sys tables??

identity column from sys tables??

Post by Thomas Murph » Wed, 05 Feb 2003 05:05:37



What is the proper way of finding whether or not a column
is an identity column using the system tables?

Right now I check syscolumns for autoval IS NOT NULL, but
this is being done as a result of my simplistic
deducations (i.e. assumptions), not hard-proof
documentation, so it may not be 100% correct.

Thanks

 
 
 

identity column from sys tables??

Post by Thomas Murph » Wed, 05 Feb 2003 05:29:29


Well, I continued searching after posting this and I've
found another way that seems to be more apropos. Its what
Sql Server uses in its sp_help sproc:



My only regret is that the column is described as "For
internal use only" in MSDN.

I hope this helps others as well...

Quote:>-----Original Message-----
>What is the proper way of finding whether or not a column
>is an identity column using the system tables?

>Right now I check syscolumns for autoval IS NOT NULL, but
>this is being done as a result of my simplistic
>deducations (i.e. assumptions), not hard-proof
>documentation, so it may not be 100% correct.

>Thanks
>.


 
 
 

identity column from sys tables??

Post by BP Margoli » Wed, 05 Feb 2003 11:36:33


Thomas,

create table dbo.Thomas
(
 c1 int NOT NULL primary key,
 c2 varchar (5) NULL,
 c3 int NOT NULL IDENTITY (1, 1),
 c4 datetime NULL
)
go

select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and TABLE_NAME = 'Thomas'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> Well, I continued searching after posting this and I've
> found another way that seems to be more apropos. Its what
> Sql Server uses in its sp_help sproc:



> My only regret is that the column is described as "For
> internal use only" in MSDN.

> I hope this helps others as well...

> >-----Original Message-----
> >What is the proper way of finding whether or not a column
> >is an identity column using the system tables?

> >Right now I check syscolumns for autoval IS NOT NULL, but
> >this is being done as a result of my simplistic
> >deducations (i.e. assumptions), not hard-proof
> >documentation, so it may not be 100% correct.

> >Thanks
> >.

 
 
 

1. how are identity columns defined in sys tables?

Look up syscolumns in BOL.  It shows that this information is maintained in
the status column.  Your bitmask to check would be 0x80.

select * from syscolumns where status & 0x80 = 0x80

HTH,
Doug

--
Douglas Bass
QualChoice of NC
Database Administrator


Does anyone know how identity columns are defined in the system tables?  I
need
to find out if a column is defined as identity for a given table.  I looked
at
syscolumns and there is no "isidentity" column.  Any other tables I should
look
at?

thanx

2. Problem: Index invalid

3. sys tables, column Identity info?

4. Getting a Structure Cube

5. How to change an identity column to non identity column

6. create table

7. sys tables, where's Identity property?

8. deadlock issue

9. how to alter an identity column to a non-identity column

10. How do make an identity column NOT an identity column

11. SYS ERROR MESSAGE DESCRIPTION without select on sys table

12. Identity Column lost its identity

13. Changing Identity column to Identity not for replication?