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

LIKE '%myName%'

?


当前回答

您可以在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;

其他回答

Create table #yourcolumndetails(
DBaseName varchar(100), 
TableSchema varchar(50), 
TableName varchar(100),
ColumnName varchar(100), 
DataType varchar(100), 
CharMaxLength varchar(100))

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like ''origin'''

select * from #yourcolumndetails
Drop table #yourcolumndetails

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

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

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

  select * from INFORMATION_SCHEMA.COLUMNS
  where TABLE_CATALOG= 'theDatabase'
  and COLUMN_NAME like 'theCol%'

它将返回table_name、schema_name、column_name

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c
ON t.object_id = c.object_id
WHERE c.name LIKE '%colName%'
ORDER BY schema_name,
table_name;

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

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