从图像列预览数据的最简单方法是什么? [英] What’s the easiest way to preview data from an image column?

问题描述

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

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.: 数据库不在我的控制之下，所以改变数据类型不是一个选项.

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

推荐答案

我会编写一个 proc(或查询；见下文)将二进制文件导出到文件系统，然后使用任何旧的现成照片管理实用程序 <强>(即 Windows 照片查看器) 来看看里面有什么.

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.

这是一个将二进制文件导出到文件系统的过程.我修改自 这个示例代码.它未经测试，但应该非常接近概念.它使用 BCP 来导出您的二进制文件.在此处查看 有关 BCP 实用程序的完整文档.

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 还使您能够导出表中的所有内容，或者基于传递的主键仅导出一行.它使用游标 (yuck) 以及一些动态 sql (yuck, yuck) 但有时你必须做你必须做的事情.

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

当然，这一切都假设存储在您的 Image 列中的内容实际上是一个图像，而不是其他一些文件类型.希望如果是图片，您也知道类型，bmp、jpg、png、gif 等.

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.

如果您不想要一个完整的 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

这篇关于从图像列预览数据的最简单方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！