T-SQL Find all Tables with a Certain Data Type

Posted on 2013-12-30
Medium Priority
Last Modified: 2013-12-31

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
Question by:thomasm1948
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 66

Accepted Solution

Jim Horn earned 668 total points
ID: 39747151
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

LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 664 total points
ID: 39747153
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'
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 668 total points
ID: 39748088
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.

WHERE DATA_TYPE IN ('varbinary','binary','image')

Open in new window


Author Closing Comment

ID: 39748272
Thank you all for your help.

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question