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

LIKE '%myName%'

?


当前回答

要获取完整信息:列名、表名以及表的架构。。

SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%col_Name%'

其他回答

我出于同样的目的使用了这个,它起到了作用:

  select * from INFORMATION_SCHEMA.COLUMNS
  where TABLE_CATALOG= 'theDatabase'
  and COLUMN_NAME like 'theCol%'
SELECT COLUMN_NAME, TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'

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

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%' 

您可以通过column_name过滤器从INFORMATION_SCHEMA.COLUMNS中找到它

Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
     From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'

在MS SQL Server数据库中,使用此查询获取包含输入文本的表和相应的列名:

SELECT t.name AS tableName, c.name AS columnName 
FROM sys.tables as t 
INNER JOIN sys.columns AS c ON t.object_id=c.object_id 
WHERE c.name LIKE '%<your_search_string>%'