How to identify whether the table has identity column

Question

I want to find out whether the table has an identity column or not. Table is unknown to me. I have not done the structure of the table. Using Query?

I am using Sql Server Compact Edition.

Answer 1

This is the query which return identity column name

create procedure getidentity 
@tablename varchar(50)
begin
    SELECT   OBJECT_NAME(OBJECT_ID) AS TABLENAME, 
             NAME AS COLUMNNAME, 
             SEED_VALUE, 
             INCREMENT_VALUE, 
             LAST_VALUE, 
             IS_NOT_FOR_REPLICATION 
    FROM     SYS.IDENTITY_COLUMNS 
    WHERE OBJECT_NAME(OBJECT_ID) = @tablename
end

than form the code side

call this stored procedure using datareader than change datareader.hasrows() if the condition value is true than table has identity column if not than it no have an identity column

Answer 2

One way to do this would be to make use of the stored procedure sp_help. I.e:

sp_help MyTable

This will return a DataSet that has all the information you would need on the table. There is a specific Table that has information on identities.

I.e:

If it does not contain an identity field, the Identity column will say: "No identity column defined".