determine if autoincrement is set in a table, form asp.net

I want to access sqlserver table properties from asp.net .

How do i know that a table defined in sqlserver has autoincrement field or not.

Actually i have to access all the tables in a database and execute different function for table with auto increment on and off.

I am not being able identify this property from codes.
Please help.
thank you

Hey,

When you connect to the database, you can't know that from ADO.NET; you have to recreate identities manually on the ADO.NET side. However, if you have SQL Server 2005 API's installed, you can use the SMO objects to connect to a 7.0, 2000, or 2005 database and find this information out.

|||Thanks brian

I am using sql 2000 and SqlDataAdapter object

I think the following code should work but its not working.
any ideas

Dim dsAs DataSet ' = ( got dataset from a table)
Dim hasAutoIncrementAsBoolean =FalseForEach columnAs DataColumnIn ds.Tables(0).Columns If column.AutoIncrementThen

hasAutoIncrement =

True
ExitFor EndIfNext|||

Hey,

The problem is there isn't any autoincrement returned back from the dataset; you have to establish that programatically. So you need to do:

dataSet.Tables(0).Columns(0).AutoIncrement = true
dataSet.Tables(0).Columns(0).AutoIncrementStep = 1

Etc. You have to set this up by yourself; it doesn't pull it back for you.

|||

SELECT *,COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')AS IS_IDENTITYFROM INFORMATION_SCHEMA.COLUMNS

|||

Which would lead you to this query:

SELECT COUNT(*)

FROM (

SELECT

*,COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsIdentity')AS IS_IDENTITY

FROM

INFORMATION_SCHEMA.COLUMNS

) t1

WHERETABLE_NAME=@.MyTable AND IS_IDENITITY=1

|||Thank you very muchMotley

exactly what i was looking for.

Thanks

Design Opinions Please

I have written a DTS package to import a table from Oracle into SQL Server.
This table is defined as Data_Group (which is an employee id), Element_Code
(a numberic code that tells what data this row has, ie. Name, Address,
Status, etc), and then the Value. There are several elements that I will no
t
use, but I import them anyway for later use. My next step is to take these
data elements at build 1 record in my SQL Server table.
I am thinking about creating a SP that accepts the employee id and then
builds the record by using a bunch of SQL Update statements. Then write
another SP that opens a cursur on my input table (SELECT DISTINCT Data_Group
From T1) and call the SP from within the loop.
Is this a good approach or is there a better way? Thanks for your advice.insert newtbl
select id ,
(select s.name from staging s where s.id = t.id) ,
(select s.address from staging s where s.id = t.id) ,
...
from (select distinct id from staging) t
"Phill" wrote:

> I have written a DTS package to import a table from Oracle into SQL Server
.
> This table is defined as Data_Group (which is an employee id), Element_Cod
e
> (a numberic code that tells what data this row has, ie. Name, Address,
> Status, etc), and then the Value. There are several elements that I will
not
> use, but I import them anyway for later use. My next step is to take thes
e
> data elements at build 1 record in my SQL Server table.
> I am thinking about creating a SP that accepts the employee id and then
> builds the record by using a bunch of SQL Update statements. Then write
> another SP that opens a cursur on my input table (SELECT DISTINCT Data_Gro
up
> From T1) and call the SP from within the loop.
> Is this a good approach or is there a better way? Thanks for your advice.|||oops
insert newtbl (id, name, address, ...)
select id ,
name = (select s.value from staging s where s.id = t.id and s.code = 1) ,
address = (select s.value from staging s where s.id = t.id and s.code = 2) ,
...
from (select distinct id from staging) t
"Phill" wrote:

> I have written a DTS package to import a table from Oracle into SQL Server
.
> This table is defined as Data_Group (which is an employee id), Element_Cod
e
> (a numberic code that tells what data this row has, ie. Name, Address,
> Status, etc), and then the Value. There are several elements that I will
not
> use, but I import them anyway for later use. My next step is to take thes
e
> data elements at build 1 record in my SQL Server table.
> I am thinking about creating a SP that accepts the employee id and then
> builds the record by using a bunch of SQL Update statements. Then write
> another SP that opens a cursur on my input table (SELECT DISTINCT Data_Gro
up
> From T1) and call the SP from within the loop.
> Is this a good approach or is there a better way? Thanks for your advice.