SQL Server Compact Code Snippet of the Week #2 : locate the IDENTITY column in a table

The next instalment in this series also concerns a column type that only exists once per table, the IDENTITY column. The type of the column must be either bigint or int, and you must also specify an initial value (seed) and the value for the difference between each value (the increment), the default values for both being 1.

In order to detect which column is the IDENTITY column, you can use the following SQL statement (an code):

        public bool HasIdentityColumn(string tableName)
        {
            return (GetIdentityOrdinal(tableName) > -1);
        }

        public int GetIdentityOrdinal(string tableName)
        {
            object value = ExecuteScalar("SELECT ordinal_position FROM information_schema.columns WHERE TABLE_NAME = N'" + tableName + "' AND AUTOINC_SEED IS NOT NULL");
            if (value != null)
            {
                return (int)value - 1;
            }
            return -1;
        }