SQL 在数据库的未知表中查找特定列条目？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5818598/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find a specific column entry in an unknown table in a database?
提问by Hal
I'm aware of this topic ( Find a specific column in an unknown table in a database?) and my problem is quite similar. The query I need is quite similar to this one (I think):
我知道这个主题（在数据库中的未知表中查找特定列？），我的问题非常相似。我需要的查询与这个非常相似（我认为）：
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE '%watcher%'
But what I need is a query where the column name is unknown, but I know what the content will be and I want to find out what the name of table/column is. (I know this sounds strange :-/ ). I this possible?
但我需要的是列名未知的查询，但我知道内容是什么，我想找出表/列的名称。（我知道这听起来很奇怪：-/）。我这可能吗？
回答by Timothy Walden
Try using ApexSQL Search– it searches for both objects and data and it's a free tool similar to SQL Search from Red Gate.
尝试使用ApexSQL 搜索——它搜索对象和数据，它是一个类似于 Red Gate 的 SQL 搜索的免费工具。
Another option if you don't want to deal with third party tools is query that will use cursors to iterate through all columns in all tables but I'm afraid that it would turn out too complex and performance intensive.
如果您不想使用第三方工具，另一种选择是查询，它将使用游标遍历所有表中的所有列，但我担心它会变得过于复杂且性能密集。
回答by Lamak
Ok, I think that for your problem, you are gonna need dynamic sql, so first take a look at this link. If that weren't enough, the only solution that came to mind involves cursors, so I advise you to keep looking for others implementation of your problem. That said, you can try the following code (but you should test it on small tables first).
好的，我认为对于您的问题，您将需要动态 sql，所以首先看看这个链接。如果这还不够，想到的唯一解决方案涉及游标，因此我建议您继续寻找其他人的问题实现。也就是说，您可以尝试以下代码（但您应该先在小表上进行测试）。
DECLARE @Query NVARCHAR(MAX), @Column NVARCHAR(100), @Table NVARCHAR(100)
DECLARE @Search NVARCHAR(100)
SET @Search = 'Your string'
CREATE TABLE #Results(Table_Name VARCHAR(100), Column_Name VARCHAR(100))
DECLARE Col CURSOR FOR
SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION
OPEN Col
FETCH NEXT FROM Col INTO @Table, @Column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Query = 'IF EXISTS (SELECT * FROM '+QUOTENAME(@Table)+' WHERE '+QUOTENAME(@Column)+'='''+@Search+''')
SELECT '''+@Table+''','''+@Column+''''
INSERT INTO #Results
EXEC sp_executesql @Query
FETCH NEXT FROM Col INTO @Table, @Column
END
CLOSE Col
DEALLOCATE Col
SELECT * FROM #Results
回答by marc_s
Have a look at the FREERed-Gate tool called SQL Searchwhich does this - it searches your entire database for any kind of string(s).
查看名为SQL Search的免费Red-Gate 工具，它可以执行此操作 - 它会在整个数据库中搜索任何类型的字符串。
It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREEto use for any kind of use??
对于任何 DBA 或数据库开发人员来说，它都是一个很棒的必备工具 - 我是否已经提到它对于任何类型的使用都是完全免费的？？
回答by a_horse_with_no_name
Using SQL Workbench/Jyou can run the following statement:
使用SQL Workbench/J，您可以运行以下语句：
WbGrepData -searchValue=watcher
it will search through all columns in all (accessible) tables and return all rows where the search term is found in at least one column.
它将搜索所有（可访问的）表中的所有列，并返回在至少一列中找到搜索词的所有行。
回答by Pinakin Patel
USE DBName GO SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%ColumName start from%' ORDER BY schema_name, table_name;
回答by Mauro Bilotti
It could help too
它也可以帮助
DECLARE @columnName as varchar(100)
SET @columnName = 'ColumnName'
SELECT t.name AS Table, c.name AS Column,
ty.name AS Tipo, c.max_length AS Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.name LIKE @columnName
ORDER BY t.name, c.name
