InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 01:22:16 GMT20http://www.sqlservercentral.com/Forums/Topic982006-391-1.aspxIf you are still interested, I have a query I have been working on (still working on it to get it faster) that will report show you indexes with LOB columns, the size there and the allocation unit type associated with that column. It does not return the same number of columns as Gail's query - but that is likely due to the integration with indexes that I am trying to report on with this one.Work in progress:[code="sql"]CREATE TABLE #indstats ( indstatsid INT IDENTITY(1,1) PRIMARY KEY CLUSTERED ,database_id BIGINT ,Object_id BIGINT ,index_id BIGINT ,IndexSizeMB DECIMAL(16,1)) INSERT INTO #indstats (database_id,object_id,index_id,IndexSizeMB) SELECT database_id,object_id,index_id ,CONVERT(DECIMAL(16,1) ,(SUM(ps.avg_record_size_in_bytes * ps.record_count) / (1024.0 * 1024))) AS IndexSizeMB FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps GROUP BY database_id,object_id,index_id;With LOBCols as (Select FileGroupName = filegroup_name(a.data_space_id) ,TableName = object_name(p.object_id) ,p.object_id ,IndexName = i.name ,ColumnName = c.name ,c.column_id ,LOBUsedPages = a.used_pages ,LOBTotalPages = a.total_pages ,LOBDataSizeMB = a.used_pages * 8/1024 , ps.IndexSizeMB , (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests , (us.user_updates) AS UserUpdates , us.last_user_update AS LastUpdate , CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL) / CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates ,a.type_desc as AllocUnitType From sys.allocation_units a Inner Join sys.partitions p On p.partition_id = a.container_id And a.type = 2 --LOB data is stored in pages of type Text/Image Left Outer Join sys.dm_db_index_usage_stats us On us.object_id = p.object_id And us.index_id = p.index_id And us.database_id = db_id() Left Outer Join #indstats ps ON us.index_id = ps.index_id And us.database_id = ps.database_id And us.object_id = ps.object_id Left Outer Join sys.indexes i On i.object_id = p.object_id And i.index_id = p.index_id Left Outer Join sys.index_columns ic On i.index_id = ic.index_id And i.object_id = ic.object_id Left Outer Join sys.columns c On ic.object_id = c.object_id And ic.column_id = c.column_id Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0 --And a.data_pages > 0 --And filegroup_name(a.data_space_id) = 'Primary'UnionSelect FileGroupName = filegroup_name(a.data_space_id) ,TableName = object_name(p.object_id) ,p.object_id ,IndexName = i.name ,ColumnName = c.name ,c.column_id ,LOBUsedPages = a.used_pages ,LOBTotalPages = a.total_pages ,LOBDataSizeMB = a.used_pages * 8/1024 , ps.IndexSizeMB , (us.user_seeks + us.user_scans + us.user_lookups) AS UserRequests , (us.user_updates) AS UserUpdates , us.last_user_update AS LastUpdate , CAST(us.user_seeks + us.user_scans + us.user_lookups AS REAL) / CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL) AS RatioRequestsToUpdates ,a.type_desc as AllocUnitType From sys.allocation_units a Inner Join sys.partitions p On p.hobt_id = a.container_id And a.type = 3 --Overflow data is stored in pages of type Text/Image Left Outer Join sys.dm_db_index_usage_stats us On us.object_id = p.object_id And us.index_id = p.index_id And us.database_id = db_id() Left Outer Join #indstats ps ON us.index_id = ps.index_id And us.database_id = ps.database_id And us.object_id = ps.object_id Left Outer Join sys.indexes i On i.object_id = p.object_id And i.index_id = p.index_id Left Outer Join sys.index_columns ic On i.index_id = ic.index_id And i.object_id = ic.object_id Left Outer Join sys.columns c On ic.object_id = c.object_id And ic.column_id = c.column_id Where OBJECTPROPERTY(p.object_id,'IsMSShipped') = 0 --And filegroup_name(a.data_space_id) = 'Primary' --And a.data_pages > 0 )Select L.FileGroupName,L.TableName,L.IndexName,L.ColumnName,L.LOBUsedPages,L.LOBTotalPages,L.LOBDataSizeMB,L.IndexSizeMB ,L.UserRequests,L.LastUpdate,L.RatioRequestsToUpdates,L.AllocUnitType,t.name From LOBCols L Inner Join sys.columns c On c.object_id = L.object_id And c.column_id = L.column_id Inner Join sys.types t On t.user_type_id = c.user_type_id Order By L.TableName asc,L.AllocUnitType[/code]Fri, 10 Sep 2010 19:51:01 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic982006-391-1.aspxThanks for the quick replies! :-)I will try this outWed, 08 Sep 2010 10:40:19 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic982006-391-1.aspx[quote][b]stewartc-708166 (9/8/2010)[/b][hr]Microsoft recommends the use of the INFORMATION_SCHEMA views in lieu of system tables[/quote]Got a reference that states that?sys.columns, sys.tables, etc are not system tables. They're catalog views and they exist so that MS can change the underlying system tables (which aren't even visible) without breaking code. From Books Online ([url]http://msdn.microsoft.com/en-us/library/ms174365%28v=SQL.100%29.aspx[/url]):[quote]Catalog views return information that is used by the SQL Server Database Engine. We recommend that you use catalog views because they are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user-available catalog metadata is exposed through catalog views.[/quote]Wed, 08 Sep 2010 01:50:43 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic982006-391-1.aspxMicrosoft recommends the use of the INFORMATION_SCHEMA views in lieu of system tables, as they reserve the right to change said tables, while the views remain relatively static.[code="sql"]select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNSwhere DATA_TYPE in ('text','ntext','image','xml') OR (DATA_TYPE in ('varchar','nvarchar','varbinary') AND CHARACTER_MAXIMUM_LENGTH = -1)[/code]Wed, 08 Sep 2010 00:57:50 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic982006-391-1.aspxThis should work[code="sql"]SELECT OBJECT_NAME(c.object_id), c.name, t.name FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id WHERE t.name IN ('text','ntext','image','xml') OR (t.name IN ('varchar','nvarchar','varbinary') AND c.max_length = -1)[/code]Tue, 07 Sep 2010 23:35:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic982006-391-1.aspx[quote][b]luckysql.kinda (9/7/2010)[/b][hr]select * from sys.syscolumnswhere length IN (16,8000,-1)[/quote]Syscolumns is deprecated, should not be used for new development and will be removed in a future version of SQL.That query also returns all Uniqueidentifier columns (which are 16 bytes), varchar and varbinary 8000 (which are not LOB) and any varchar(16) or varbinary (16)Tue, 07 Sep 2010 23:26:44 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic982006-391-1.aspxTRY THISselect * from sys.syscolumnswhere length IN (16,8000,-1)Tue, 07 Sep 2010 22:57:03 GMTluckysql.kindahttp://www.sqlservercentral.com/Forums/Topic982006-391-1.aspxI'd like to determine which fields are LOB?Tue, 07 Sep 2010 19:51:14 GMTMyDoggieJessie