查找具有特定值的列名或表名 [英] find column name or table name of a specific value
问题描述
我想在我的数据库中搜索一个不知道确切值的特定值.是否有任何查询返回SQL Server中特定值的列名或表名? 假设我有一个像123这样的列的值,但是我不知道123属于哪个表,也不知道它的列名.我可以编写查询来查找该值所在的表名吗? 我需要查询而不是程序!!!
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!!!
推荐答案
这可能为您做到了.请注意,如果您有很多表/列,则可能要花一些时间.如果您不在(N)VARCHAR
列中进行搜索,则可能需要将这些类型添加到c.DATA_TYPE NOT IN(...
子句中.或其他您不需要的类型(例如FLOAT
或DECIMAL
).
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;
这篇关于查找具有特定值的列名或表名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!