March 27, 2014 at 10:01 pm
How to find the tables in a database which is having LOB columns. There are so many available in web, but each one provides different results, different rows. Anyone has an exact query?
March 28, 2014 at 2:24 am
USE [AdventureWorks2012]
GO
SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, t.TABLE_TYPE
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE (DATA_TYPE IN ('FILESTREAM','XML','VARBINARY','TEXT','NTEXT','IMAGE')
OR(DATA_TYPE IN ('VARCHAR', 'NVARCHAR') AND CHARACTER_MAXIMUM_LENGTH = -1))
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME;
March 28, 2014 at 2:57 am
You can also use:
select * from sys.columns where max_length = -1
/*Where -1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml. */
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 31, 2014 at 9:10 am
Thanks all for the query. It is working.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply