T-SQL Find all Tables with a Certain Data Type

Hi,

How can find all of the tables in a database that contains either "Image" or "Binary" data types

Thank you for all of your help in advance
thomasm1948Asked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Give this a whirl...
SELECT t.name as table_name, c.name as column_name, c.*
FROM sys.tables t
	JOIN sys.columns c ON t.object_id = c.object_id
	JOIN sys.types ty ON c.user_type_id = ty.user_type_id
WHERE ty.name IN ('binary', 'image')
ORDER BY t.name, c.name

Open in new window

0
 
Brian CroweDatabase AdministratorCommented:
SELECT o.name, o.type, c.name, t.name
FROM sys.columns AS c
INNER JOIN sys.objects AS o
      ON C.object_id = o.object_id
INNER JOIN sys.types AS t
      ON c.system_type_id = t.system_type_id
WHERE c.system_type_id IN (34, 165, 173)
      AND o.type = 'U'
0
 
Kevin CrossChief Technology OfficerCommented:
You already have the answer above.  Note the difference between the two is the second shows VARBINARY as well.  Anyway, I just wanted to share the INFORMATION_SCHEMA view.

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varbinary','binary','image')
;

Open in new window

0
 
thomasm1948Author Commented:
Thank you all for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.