SQL：查询特定值和返回表名的整个架构 [英] SQL: Query Entire Schema for Specific Value and Return Table Names

问题描述

更多的冒险，我不得不嵌入SQL在VBA代码从公司的数据库获取信息...我们的数据库可能有几千个数据库表，所以如果你正在寻找的东西，特别是，你必须做一个很多（特别是如果表的名称不像像ITEMS那样直观）。

more adventures of my having to embed SQL in VBA code to get info from the company's database... Our database has probably a couple thousand database tables, so if you are looking for something in-particular, you have to do a lot of poking around (especially if the name of the table isn't as intuitive as something like "ITEMS").

我有一个大致的想法，列标题我正在寻找被调用，并遇到这个代码找到它：

I had a general idea of what the column header I was looking for was called, and came across this code to find it:

SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'DATABASE'
AND COLUMN_NAME LIKE '%MFGR%';

这会返回所有包含％MFGR％的列的表名。找到表I需要很快....比任意导入看起来像它们可能是的表格更快。

This returned the names of all the tables with columns containing %MFGR%... Found the table I needed pretty quick.... much quicker than arbitrarily importing tables that look like they might be it.

无论如何，更好的是有一个查询返回在任何列中包含某个值的所有表的名称。通常我不知道什么列被命名，但知道什么值是。我只想知道什么表包含它，所以我可以看看他们。

Anyway, what would be better is to have a query that returned the names of all the tables containing a certain value in any column. Usually I have no clue what the column is named, but know what one of the values is. I just want to know what tables contain it so I can look at them.

结果是： http： //vyaskn.tripod.com/search_all_columns_in_all_tables.htm

听起来像可能会工作，但是有更简单的方法吗？我没有尝试把它放入VBA代码。

Sounds like it will probably work, but is there a simpler way? I haven't tried putting it into VBA code yet.

编辑：看看代码，看起来它涉及操纵数据库（创建过程，创建表等。）我没有访问权限。

Looking at the code, it looks like it involves manipulating the database (creating procedures, creating tables, etc.) which I don't have the access to do. Anyway to do this just as a query?

推荐答案

请使用下面的查询输出，其中database是数据库的名称： / p>

Pleas, use below query for output, where database is name of database:

SELECT DISTINCT T.name 
FROM   database.sys.columns C 
       LEFT OUTER JOIN database.sys.tables T 
                    ON T.object_id = C.object_id 
WHERE  C.name LIKE '%name%' 

这篇关于SQL：查询特定值和返回表名的整个架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！