是否可以查询包含以下列的表名

LIKE '%myName%'

?


当前回答

这应该是有效的:

SELECT name 
FROM sysobjects 
WHERE id IN ( SELECT id 
              FROM syscolumns 
              WHERE name like '%column_name%' )

其他回答

为此,我们可以使用sp_columns。

sp_columns 'table name', null, null, '%column name%'

此外,还可以找到具有指定模式的列名。

SELECT 'DBName' AS DBName,
       column_name,
       table_name,
       table_schema
FROM   dbname.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'
       AND table_schema IN ( 'YourSchemaName' ) 

您还可以在多个数据库中找到同一列。

SELECT 'DBName1' AS DB,
       column_name,
       table_name,
       table_schema
FROM   dbname1.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%'
UNION
SELECT 'DBName2' AS DB,
       column_name,
       table_name,
       table_schema
FROM   dbname2.information_schema.columns
WHERE  column_name LIKE '%YourColumnName%' 

像oracle一样,您可以通过以下方式查找表和列:

select table_name, column_name
from user_tab_columns 
where column_name 
like '%myname%';

这个简单的查询对我有用。

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'schemaName'
AND column_name like '%model%';
SELECT COLUMN_NAME, TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'