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

LIKE '%myName%'

?


当前回答

这应该是有效的:

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

其他回答

您可以在SQL Server中使用以下查询。

SELECT column_name AS 'ColumnName',
       table_name  AS 'TableName'
FROM   information_schema.columns
WHERE  column_name LIKE '%columnname%'
       AND table_schema = 'schema'
ORDER  BY tablename,
          columnname;

在MS SQL中,可以编写以下行来检查特定表的列名:

sp_help your_tablename

或者,您可以先在查询窗口中选择表名(突出显示模式和表名),然后按下下面的组合键:

Alt + F1

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

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%' 
SELECT COLUMN_NAME, TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS    
 WHERE COLUMN_NAME LIKE '%myName%'

用于显示具有指定列名的所有表的SQL查询:

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
  FROM sys.tables t
 INNER JOIN sys.columns c ON c.object_id = t.object_id
 WHERE c.name like '%ColumnName%'
 ORDER BY 'Table Name'