Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
355 views
in Technique[技术] by (71.8m points)

sql server - find column name or table name of a specific value

I want search a specific value in my database that i don't know where is it exactly. Is there any query exist that returned column name or table name of a specific value in SQL server? Assume that I have a value of a column like 123, but I don't know 123 belongs to which table and I don't know any about its column name. Can i write a query to find table names that this value is in it? I need a query not a procedure!!!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

This might do it for you. Note that if you have a lot of tables/columns this might take quite a while. If you're not searching in (N)VARCHAR columns, you might want to add those types to the c.DATA_TYPE NOT IN(... clause. Or any other type you're not looking in (like FLOAT or DECIMAL).

SET NOCOUNT ON;
DECLARE @value NVARCHAR(MAX)='123';
CREATE TABLE #found(table_name SYSNAME,column_name SYSNAME);
DECLARE @sql NVARCHAR(MAX)=(
    SELECT
        'INSERT INTO #found(table_name,column_name) ' +
        'SELECT TOP 1 '+
            'table_name='''+REPLACE(t.TABLE_NAME,'''','''''')+''','+
            'column_name='''+REPLACE(c.COLUMN_NAME,'''','''''')+''' '+
        'FROM '+
            QUOTENAME(t.TABLE_SCHEMA)+'.'+QUOTENAME(t.TABLE_NAME)+' '+
        'WHERE '+
            QUOTENAME(c.COLUMN_NAME)+'='''+REPLACE(@value,'''','''''')+''';'
    FROM
        INFORMATION_SCHEMA.TABLES AS t
        INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c ON
            c.TABLE_SCHEMA=t.TABLE_SCHEMA AND
            c.TABLE_NAME=t.TABLE_NAME
    WHERE
        t.TABLE_TYPE='BASE TABLE' AND 
        c.DATA_TYPE NOT IN('BIT','NTEXT','TEXT','IMAGE','BINARY','VARBINARY','DATETIME','DATE','DATETIME2','TIME','SMALLDATETIME','DATETIMEOFFSET')
    FOR XML
        PATH('')
);

EXECUTE (@sql);
SELECT * FROM #found ORDER BY table_name,column_name;
DROP TABLE #found;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...