What’s the easiest way to preview data from an ima

问题:

I have some columns with image data type and I want to preview (or browse) the data in those tables. When I use Select top 1000 rows in SQL Server Management Studio, the value of image columns is displayed in hexadecimal. What’s the easiest way to preview those images since the hex-value is not useful to me?

PS.: database is not under my control, so changing data type is not an option.

回答1:

I would write a proc (or query; see below) to export the binary out to the file system and then use any old off the shelf photo management utility (i.e. Windows Photo Viewer) to take a look at what's inside.

If your clever in your file naming you could give yourself enough information about each image in the name to quickly find it in the database again once you've visually located what your looking for.

Here is a proc that will export binary to the file system. I modified from this sample code. It's untested but should be extremely close for concept. It's using BCP to export your binary. Check here for the full docs on the BCP utility.

The proc also gives you the ability to export everything in the table, or only a single row based on a the passed primarykey. It uses a cursor (yuck), as well as some dynamic sql (yuck, yuck) but sometimes you gotta do what you gotta do.

 CREATE PROCEDURE ExportMyImageFiles
 (   
   @PriKey INT,
   @OutputFilePath VARCHAR(500)
 ) 
 AS 
 BEGIN 
     DECLARE @sql VARCHAR(8000) 

     IF @PriKey IS NULL /* export all images */
     BEGIN
        DECLARE curExportBinaryImgs CURSOR FAST_FORWARD FOR

        SELECT 'BCP "SELECT MyImage FROM [dbo].[MyTable] 
           WHERE PrimaryKey =' + CAST(PrimaryKey AS VARCHAR(25)) + 
           '" queryout ' + @OutputFilePath + MyImageName + '.' + 
           MyImageType + ' -S MyServer\MyInstance -T -fC:\Documents.fmt'
        FROM [dbo].[MyTable]

        OPEN curExportBinaryImgs
        FETCH NEXT FROM curExportBinaryImgs INTO @sql

        WHILE @@FETCH_STATUS = 0
        BEGIN            
            EXEC xp_cmdshell @sql, NO_OUTPUT
            FETCH NEXT FROM curExportBinaryImgs INTO @sql
        END

        CLOSE curExportBinaryImgs
        DEALLOCATE curExportBinaryImgs
     END
     ELSE       /* Export only the primary key provided */     
     BEGIN
        SELECT @sql = 'BCP "SELECT MyImage FROM [dbo].[MyTable] 
        WHERE PrimaryKey =' + CAST(PrimaryKey AS VARCHAR(25)) + 
            '" queryout ' + @OutputFilePath
            + MyImageName + '.' + MyImageType + 
            ' -S MyServer\MyInstance -T -fC:\Documents.fmt'
        FROM [dbo].[MyTable]
        WHERE PrimaryKey = @PriKey

        EXEC xp_cmdshell @sql,NO_OUTPUT
     END 
 END

This is all assuming of course that what is stored in your Image column is actually an image and not some other file type. Hopefully if it is an image you also know the type, bmp, jpg, png, gif, etc.

If you don't want the hassle or reusability of a full blown proc try single query like this:

---

    DECLARE @OutputFilePath VarChar(500) = /* put output dir here */

    DECLARE @sql VARCHAR(8000)
    DECLARE curExportBinaryImgs CURSOR FAST_FORWARD FOR
    SELECT 'BCP "SELECT MyImage FROM [dbo].[MyTable] 
       WHERE PrimaryKey =' + CAST(PrimaryKey AS VARCHAR(25)) + 
       '" queryout ' + @OutputFilePath + MyImageName + '.' + 
       MyImageType + ' -S MyServer\MyInstance -T -fC:\Documents.fmt'
    FROM [dbo].[MyTable]

    OPEN curExportBinaryImgs
    FETCH NEXT FROM curExportBinaryImgs INTO @sql

    WHILE @@FETCH_STATUS = 0
    BEGIN            
        EXEC xp_cmdshell @sql, NO_OUTPUT
        FETCH NEXT FROM curExportBinaryImgs INTO @sql
    END

    CLOSE curExportBinaryImgs
    DEALLOCATE curExportBinaryImgs

回答2:

If you have LinqPad installed, previewing images is simple. Query your record, convert the binary data to an image, then dump the output to the preview window.

Edit: If you aren't aware, LinqPad is a free utility that can be used for many things, such as a replacement for management studio. Most of the time I use it as a scratch pad for .Net for throw-away programs, test code, and samples.

var entity = // fetch data

using (var ms = new MemoryStream(entity.Image.ToArray()))
{
    System.Drawing.Image.FromStream(ms).Dump();
}

Here's what the result looks like:

回答3:

The image type isn't for storing images, it's just 'variable-length binary data'. This type is deprecated and you should now use varbinary(max) for variable length binary data.

Since the SQL Server has no knowledge of what type of binary data has been stored (.zip, .exe, .jpg, .anything) it's not surprising Management Studio doesn't provide a preview.

You definitely can't preview these data types in Managment Studio, but I like the solution given by @RTomas.

回答4:

There is a really great add-in for SSMS SSMSBoost which which provides plenty of useful features, and of course the simplest way to preview images stored in SQL(at least in my opinion)

NOTE : You must restart SSMS after installing this add-in.

Install it and enjoy previewing images just with : RightClick > Visualize As > Picture

回答5:

I don't know of a way to accomplish this in Management Studio. You'd probably be better server writing a simple application that can query the database and then convert the hex into the correct image type (.jpg, .png, etc). There are also commercial applications that will do this for you.

回答6:

Using linqpad the code could become even simpler

Ones you have the entity/type with you on the binary data column you would see .ToImage() method

For my case I am looping through all the rows and publishing all binary columns to images.

Hope it helps.

var yourData_Or_List = // fetch data
DataItem_Or_ListItem.BinaryDataColumn.ToImage().Dump();