选择具有指定值的列名 [英] Selecting column names that have specified value

问题描述

我们收到了相当大的文件，我们无法控制其格式，这些文件正在通过 SSIS 批量加载到 SQL Server 表中，以便稍后导入到我们的内部结构中.这些文件可能包含 800 多个列，而且列名通常无法立即识别.

We are receiving rather large files, of which we have no control over the format of, that are being bulk-loaded into a SQL Server table via SSIS to be later imported into our internal structure. These files can contain over 800 columns, and often the column names are not immediately recognizable.

因此，我们有一个大表来表示包含超过 800 个 Varchar 列的文件内容.

As a result, we have a large table that represents the contents of the file with over 800 Varchar columns.

问题是:我知道我在这个数据中寻找什么特定值，但我不知道哪个列包含它.并且通过观察数据来查找所述列既不高效也不理想.

The problem is: I know what specific values I'm looking for in this data, but I do not know what column contains it. And eyeballing the data to find said column is neither efficient nor ideal.

我的问题是:是否可以通过某个值 N 搜索表并返回具有该值的列名?我会发布一些我尝试过的代码，但我真的不知道从哪里开始……或者是否有可能.

My question is: is it at all possible to search a table by some value N and return the column names that have that value? I'd post some code that I've tried, but I really don't know where to start on this one... or if it's even possible.

例如:

A   B   C   D   E   F   G   H   I   J   K   L   M   N   ...
------------------------------------------------------------
'a' 'a' 'a' 'a' 'a' 'b' 'a' 'a' 'a' 'b' 'b' 'a' 'a' 'c' ...

如果我要在此表中搜索值 'b'，我希望得到以下结果:

If I were to search this table for the value 'b', I would want to get back the following results:

Columns
---------
F
J
K

这样的事情可以做吗?

推荐答案

此脚本将在所有表和所有字符串列中搜索特定字符串.您或许可以根据自己的需要进行调整:

This script will search all tables and all string columns for a specific string. You might be able to adapt this for your needs:

DECLARE @tableName sysname
DECLARE @columnName sysname
DECLARE @value varchar(100)
DECLARE @sql varchar(2000)
DECLARE @sqlPreamble varchar(100)

SET @value = 'EDUQ4' -- *** Set this to the value you're searching for *** --

SET @sqlPreamble = 'IF EXISTS (SELECT 1 FROM '

DECLARE theTableCursor CURSOR FAST_FORWARD FOR 
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE' 
       AND TABLE_NAME NOT LIKE '%temp%' AND TABLE_NAME != 'dtproperties' AND TABLE_NAME != 'sysdiagrams'
     ORDER BY TABLE_NAME

OPEN theTableCursor
FETCH NEXT FROM theTableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0 -- spin through Table entries
BEGIN
    DECLARE theColumnCursor CURSOR FAST_FORWARD FOR
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_NAME = @tableName AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar')
         ORDER BY ORDINAL_POSITION

    OPEN theColumnCursor
    FETCH NEXT FROM theColumnCursor INTO @columnName

    WHILE @@FETCH_STATUS = 0 -- spin through Column entries
    BEGIN
        SET @sql = @tableName + ' WHERE ' + @columnName + ' LIKE ''' + @value + 
                   ''') PRINT ''Value found in Table: ' + @tableName + ', Column: ' +  @columnName + ''''
        EXEC (@sqlPreamble + @sql)
        FETCH NEXT FROM theColumnCursor INTO @columnName
    END
    CLOSE theColumnCursor
    DEALLOCATE theColumnCursor

    FETCH NEXT FROM theTableCursor INTO @tableName
END
CLOSE theTableCursor
DEALLOCATE theTableCursor

这篇关于选择具有指定值的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！