[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

649 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