SQL 搜索表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13085655/
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
Search of table names
提问by whytheq
I use the following to search for strings in my stored procedures:
我使用以下命令在我的存储过程中搜索字符串：
use DBname
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%xxx%'
Is it easy to amend the above so that it searches Table names in a specific db "DBname" ?
是否容易修改上述内容以便它在特定的数据库“DBname”中搜索表名？
回答by NeshaSerbia
I'm using this and works fine
我正在使用它并且工作正常
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%%'
回答by RichardTheKiwi
select name
from DBname.sys.tables
where name like '%xxx%'
and is_ms_shipped = 0; -- << comment out if you really want to see them
回答by ransems
If you want to look in all tables in all Databases server-wide and get output you can make use of the undocumented sp_MSforeachdbprocedure:
如果要查看服务器范围内所有数据库中的所有表并获得输出，可以使用未记录的sp_MSforeachdb过程：
sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%Table_Names%'''
回答by GarethD
I am assuming you want to pass the database name as a parameter and not just run:
我假设您想将数据库名称作为参数传递，而不仅仅是运行：
SELECT *
FROM DBName.sys.tables
WHERE Name LIKE '%XXX%'
If so, you could use dynamic SQL to add the dbname to the query:
如果是这样，您可以使用动态 SQL 将 dbname 添加到查询中：
DECLARE @DBName NVARCHAR(200) = 'YourDBName',
@TableName NVARCHAR(200) = 'SomeString';
IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE Name = @DBName)
BEGIN
PRINT 'DATABASE NOT FOUND';
RETURN;
END;
DECLARE @SQL NVARCHAR(MAX) = ' SELECT Name
FROM ' + QUOTENAME(@DBName) + '.sys.tables
WHERE Name LIKE ''%'' + @Table + ''%''';
EXECUTE SP_EXECUTESQL @SQL, N'@Table NVARCHAR(200)', @TableName;
回答by Rafi
Adding on to @[RichardTheKiwi]'s answer.
添加到@[RichardTheKiwi] 的答案。
Whenever I search for a list of tables, in general I want to select from all of them or delete them. Below is a script that generates those scripts for you.
每当我搜索表格列表时，通常我想从所有表格中进行选择或删除它们。下面是为您生成这些脚本的脚本。
The generated select script also adds a tableName column so you know what table you're looking at:
生成的选择脚本还添加了一个 tableName 列，以便您知道您正在查看哪个表：
select 'select ''' + name + ''' as TableName, * from ' + name as SelectTable,
'delete from ' + name as DeleteTable
from sys.tables
where name like '%xxxx%'
and is_ms_shipped = 0;
回答by Naseeruddin V N
you can also use the show command.
您也可以使用 show 命令。
show tables like '%tableName%'
回答by live-love
You can also use the Filter button to filter tables with a certain string in it. You can do the same with stored procedures and views.
您还可以使用“过滤器”按钮过滤包含特定字符串的表格。您可以对存储过程和视图执行相同的操作。
回答by Tim Stack
I know this is an old thread, but if you prefer case-insensitive searching:
我知道这是一个旧线程，但如果您更喜欢不区分大小写的搜索：
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE Lower(TABLE_NAME) LIKE Lower('%%')
回答by Marcello Marino
I want to post a simple solution for every schema you've got. If you are using MySQL DB, you can simply get from your schema all the table's name and add the WHERE-LIKE condition on it. You also could do it with the usual command line as follows:
我想为您拥有的每个架构发布一个简单的解决方案。如果您使用的是 MySQL DB，您可以简单地从您的架构中获取所有表的名称并在其上添加 WHERE-LIKE 条件。您也可以使用通常的命令行来完成，如下所示：
SHOW TABLES WHERE tables_in_<your_shcema_name> LIKE '%<table_partial_name>%';
where tables_in_<your_shcema_name>returns the column's name of SHOW TABLEScommand.
wheretables_in_<your_shcema_name>返回SHOW TABLES命令的列名。
