Find out which columns must be specified in insert-statement

Question

Sometimes when inserting data in a table with many columns it could be useful to know which columns must be specified if the insert-statement shouldn't fail.

I wrote this query to find out which columns are not nullable, identity, computed, timestamp and have no default value.

select  *
from    sys.columns
where   object_id = object_id('<table>') and
        is_nullable = 0 and
        is_identity = 0 and
        is_computed = 0 and
        default_object_id = 0 and
        type_name(system_type_id) <> 'timestamp'

Are there any other properties that should be included in this check?

Answer 1

The BOL documentation for the INSERT grammar has this to say about column_list

If a column is not in column_list, the Database Engine must be able to provide a value based on the definition of the column; otherwise, the row cannot be loaded. The Database Engine automatically provides a value for the column if the column:

• Has an IDENTITY property. The next incremental identity value is used.

• Has a default. The default value for the column is used.

• Has a timestamp data type. The current timestamp value is used.

• Is nullable. A null value is used.

• Is a computed column. The calculated value is used.

It looks as though your query covers all these cases to me.