从图像列预览数据最简单的方法是什么?

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?

我有一些具有图像数据类型的列，我想预览(或浏览)这些表中的数据。当我在SQL Server Management Studio中使用Select top 1000行时，图像列的值显示在十六进制中。既然十六进制值对我没有用处，那么预览这些图像最简单的方法是什么呢?

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

PS.:数据库不在我的控制之下，所以改变数据类型不是一个选项。

5 个解决方案

#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.

我会写一个proc(或查询;将二进制文件导出到文件系统，然后使用任何旧的书架照片管理工具(即Windows照片查看器)来查看里面的内容。

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.

这是一个将二进制文件导出到文件系统的proc。我从这个示例代码中进行了修改。它没有经过测试，但在概念上应该非常接近。它使用BCP导出二进制文件。请在这里查看BCP实用程序的完整文档。

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.

proc还允许您导出表中的所有内容，或者仅基于传递的primarykey导出一行。它使用一个游标(yuck)，以及一些动态sql (yuck, yuck)，但是有时候你必须做你该做的事情。

 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.

当然，这都是假设存储在映像列中的实际上是一个映像，而不是其他文件类型。如果它是一个图像，你也知道类型，bmp, jpg, png, gif等。

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

如果你不想麻烦或重复使用一个完整的proc尝试这样的单一查询:

    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.

如果你安装了LinqPad，预览图像是很简单的。查询记录，将二进制数据转换为图像，然后将输出转储到预览窗口。

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.

编辑:如果你不知道，LinqPad是一个免费的实用程序，可以用于很多事情，比如管理工作室的替代品。大多数时候，我都把它作为。net的一个草稿板，用来编写一次性的程序、测试代码和示例。