Solved

T-SQL Find all Tables with a Certain Data Type

Posted on 2013-12-30
4
458 Views
Last Modified: 2013-12-31
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
0
Comment
Question by:thomasm1948
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 167 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

0
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 166 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'
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 167 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.

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

Open in new window

0
 

Author Closing Comment

by:thomasm1948
ID: 39748272
Thank you all for your help.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now