In recent consultancy project coordinator asked me – “can you tell me what is the isolation level for this database?” I have worked with different isolation levels but have not ever queried database for the same. I quickly looked up bookonline and found out the DBCC command which can give me the same details.
You can run the DBCC UserOptions command on any database to get few details about dateformat, datefirst as well isolation level.
Set Option Value --------------------------- -------------- textsize 2147483647 language us_english dateformat mdy datefirst 7 lock_timeout -1 quoted_identifier SET arithabort SET ansi_null_dflt_on SET ansi_warnings SET ansi_padding SET ansi_nulls SET concat_null_yields_null SET isolation level read committed
I thought this was very handy script, which I have not used earlier. Thanks Gary for asking right question.
Note: This optional will only give you isolation for current connection where the command has ran and not at server level.
Reference: Pinal Dave (http://blog.sqlauthority.com)