CODEWITHSUNDEEP
sqlsql-servert-sqlvarbinaryvarbinarymax
user3115933
user3115933

Reputation: 4453

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

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?

Upvotes: 0

Views: 201

Answers (3)

Thomas Rushton
Thomas Rushton

Reputation: 5826

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';

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 82010

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

Upvotes: 4

Ram Grandhi
Ram Grandhi

Reputation: 409

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

Upvotes: 1

Related Questions