How do I find out if Large Values out of Row is enabled?


I thought the SQL Server 2005 Large Values out of Row would be a sp_tableoption config value similar to text in row – apparently not!  It took a while to find out that this is hidden in sys.tables.  The following query returns the value I was looking for:

SELECT [name] AS tablename, large_value_types_out_of_row FROM sys.tables


Comments (4)

  1. Hi Ben

    I’m a bit confused as to what you were looking for.

    sp_tableoption is only used for setting a value, not for finding out what value has been set. This procedure can be used to set the value for either ‘text in row’ or ‘large value types out of row’.

    To inspect the value for either of these options we cannot use sp_tableoption, but must look in sys.tables.

    Regards,

    Kalen

  2. benjones says:

    Yes, apologies.  I didn’t explain myself very clearly.  

    In summary, once I’ve set large values out of row with sp_tableoption, I needed a way to see what tables have this setting enabled.  As you say, sys.tables is the place to look. It would be nice to see an object property for this config value.  

    Thanks.

  3. Have you seen ANY new SQL 2005 features show up in property functions?

    I think property functions are not being updated because of the abundance of data available in the catalog views, which are much easier to work with. At least the BOL page for sp_tableoption shows sys.tables as the top of the list to "See Also".

  4. Grimpi says:

    No all information is in sys.tables.

    The new feature in SP2, vardecimal, is only in the property function.