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?

  • You could look at column level permission too... – gbn Feb 9 '12 at 10:28
  • @gbn How would I do that? Feel free to add an answer. – Andreas Ågren Feb 9 '12 at 10:30
  • Do NewID values show up as is_identity? – JNK Feb 9 '12 at 13:55
  • @JNK Isn't that a default value? – Andreas Ågren Feb 9 '12 at 17:58

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.