Welcome to MSDN Blogs Sign in | Join | Help

Benjamin Wright-Jones

SQL Server Lessons Learned and Notes from the Field (Microsoft Consultancy Services, UK)

News

  • This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified on Microsoft.com Locations of visitors to this page
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

Posted: Wednesday, September 19, 2007 12:48 PM by benjones

Comments

kalendelaney said:

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

# September 19, 2007 4:28 PM

benjones said:

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.

# September 19, 2007 5:43 PM

kalendelaney said:

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".

# September 20, 2007 6:15 PM

Grimpi said:

No all information is in sys.tables.

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

# September 21, 2007 10:38 AM
Anonymous comments are disabled
Page view tracker