Solved

T-SQL Find all Tables with a Certain Data Type

Posted on 2013-12-30
4
460 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

776 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