Search database for specific datatypes

  • I'd like to determine which fields are LOB?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • TRY THIS

    select * from sys.syscolumns

    where length IN (16,8000,-1)

  • luckysql.kinda (9/7/2010)


    select * from sys.syscolumns

    where length IN (16,8000,-1)

    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)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This should work

    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)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • This was removed by the editor as SPAM

  • stewartc-708166 (9/8/2010)


    Microsoft recommends the use of the INFORMATION_SCHEMA views in lieu of system tables

    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 (http://msdn.microsoft.com/en-us/library/ms174365%28v=SQL.100%29.aspx):

    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.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the quick replies! 🙂

    I will try this out

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • If 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:

    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'

    Union

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

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply