Share via

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

  • Article

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

  • Anonymous
    September 19, 2007
    Hi BenI'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
  • Anonymous
    September 19, 2007
    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.
  • Anonymous
    September 20, 2007
    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".
  • Anonymous
    September 21, 2007
    No all information is in sys.tables.The new feature in SP2, vardecimal, is only in the property function.