CODEWITHSUNDEEP
c#sqlsql-server
Asaf Shazar
Asaf Shazar

Reputation: 1065

How get the auto increment column name from the table

I tried this query:

 SELECT 
    IDENT_SEED('{0}'), IDENT_INCR ('{0}'), IDENT_CURRENT('{0}') AS Identity_Seed 
 FROM 
    information_schema.COLUMNS 
 WHERE 
    table_name = '{0}' AND column_name = '{1}'

name[i] is the table name, dt.Columns[j].ColumnName is the column name, but it working for every column and not only the specific the identity one

Maybe someone know how I can in that query:

SELECT 
   IDENT_SEED('{0}'), IDENT_INCR ('{0}'), IDENT_CURRENT('{0}') AS Identity_Seed ", 
   name[i]

add into the select the column name???

I just need to get the column name or add expression that where the column name (to get only one option)

Upvotes: 1

Views: 2115

Answers (1)

marc_s
marc_s

Reputation: 754488

How about

SELECT
    OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id),
    name
FROM 
    sys.identity_columns

That will output all tables (in schema.table format) and the identity column (if any) for that table.

Update: OK, so you obviously want to do this for a single table - OK then use this code:

string sqlStmt = "SELECT name FROM sys.identity_columns " +
                 "WHERE object_id = OBJECT_ID(@TableName)";

using (SqlConnection conn = new SqlConnection(your-connection-string-here))
using (SqlCommand cmd = new SqlCommand (sqlStmt, conn))
{
     // add table name as parameter
     cmd.Parameters.Add("@TableName", SqlDbType.VarChar, 100).Value = name[i];

     // open, execute, close
     conn.Open();

     object result = cmd.ExecuteScalar();
     conn.Close();

     // if result is NULL --> no identity column
     if (result != null) 
     {
          string identityColumn = result.ToString();
     }
}

That will return the name of the IDENTITY column for the table with name name[i] - or it will return NULL if that table doesn't have an IDENTITY column ....

Upvotes: 1

Related Questions