What is the T-SQL syntax for finding table/s in my SQL Server database which has a VARBINARY column type?

Question

I have a database running on SQL Server 2012 and I need to locate the tables which have varbinary column type in them.

In fact, I need to retrieve some scanned documents which have been stored in the database. I have found one table called 'ScannedDocument' and it does have a varbinary column, called 'SCD_DOCIMAGE'. However, when I run the following:

Select * from ScannedDocument

, I don't get any results in the output window in SSMS.

My first guess is that the scanned documents I am looking for are not stored in this table. What would be the T-SQL syntax to locate all tables with varbinary columns?

Also, what would be the T-SQL syntax to retrieve the documents from that column once the correct table has been identified?

Now that John has shown you how to find the column you want to extract the document found there. This is no simple feat because varbinary is just binary data. You need some details in order to have a chance. You need to what the data there represents. Is it a pdf? jpg? Maybe a word document. — Sean Lange, Apr 11 '17 at 13:27

score 4 · Accepted Answer · edited May 23 '17 at 11:54

4

Take a peek at INFORMATION_SCHEMA.COLUMNS

Select * From INFORMATION_SCHEMA.COLUMNS where data_type='varbinary'

To Download or Save to Disk, this may help Script to save varbinary data to disk

edited May 23 '17 at 11:54

Community

1
1

answered Apr 11 '17 at 13:19

John Cappelletti

79,615
7
44
66

Thanks John, exactly what I was looking for. Any pointers/suggestions as to my second question? – user3115933 Apr 11 '17 at 13:22
@user3115933 depends on your environment ... perhaps http://stackoverflow.com/questions/4056050/script-to-save-varbinary-data-to-disk – John Cappelletti Apr 11 '17 at 13:25

score 1 · Answer 2 · answered Apr 11 '17 at 13:22

1

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE data_type='varbinary'

answered Apr 11 '17 at 13:22

Ram Grandhi

397
10
27

score 0 · Answer 3 · edited Jul 29 '20 at 18:43

Aaron Bertrand has blogged about some shortcomings in the INFORMATION_SCHEMA objects.

Here's a query to identify varbinary fields using the SQL Server system objects:

SELECT OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) AS tablename,
    c.name AS columnname,
    t.name + '(' + CASE
                       WHEN c.max_length = -1 THEN
                           'MAX'
                       ELSE
                           CONVERT(VARCHAR(10), c.max_length)
                   END + ')' AS columntype
FROM sys.columns c
    INNER JOIN sys.types t
        ON t.system_type_id = c.system_type_id
WHERE t.name = 'varbinary';

What is the T-SQL syntax for finding table/s in my SQL Server database which has a VARBINARY column type?

3 Answers3