Find out which columns must be specified in insert-statement

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('') 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?

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.

StackExchange Database Administrators Q#12520, answer score: 4