Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search database for specific datatypes


Search database for specific datatypes

Author
Message
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
I'd like to determine which fields are LOB?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
luckysql.kinda
luckysql.kinda
Old Hand
Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)Old Hand (314 reputation)

Group: General Forum Members
Points: 314 Visits: 659
TRY THIS

select * from sys.syscolumns
where length IN (16,8000,-1)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47305 Visits: 44392
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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47305 Visits: 44392
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


Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5800 Visits: 7137
Microsoft 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.

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE in ('text','ntext','image','xml')
OR (DATA_TYPE in ('varchar','nvarchar','varbinary') AND CHARACTER_MAXIMUM_LENGTH = -1)



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47305 Visits: 44392
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


MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
Thanks for the quick replies! :-)

I will try this out

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21101 Visits: 18259
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search