Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
That's that then, thanks! –  Andreas Ågren Feb 13 '12 at 8:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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