是否可以查询包含以下列的表名
LIKE '%myName%'
?
是否可以查询包含以下列的表名
LIKE '%myName%'
?
当前回答
我想要一些不会让我眼睛流血的桌子和视图。
查询
SELECT
t.TABLE_TYPE AS [Type],
c.TABLE_NAME AS [Object],
c.COLUMN_NAME AS [Column]
FROM
INFORMATION_SCHEMA.COLUMNS AS c
LEFT JOIN INFORMATION_SCHEMA.TABLES AS t ON
t.TABLE_CATALOG = c.TABLE_CATALOG AND
t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
t.TABLE_NAME = c.TABLE_NAME
WHERE
c.COLUMN_NAME LIKE '%myName%'
ORDER BY
[Type],
[Object],
[Column]
后果
Type Object Column
----------------------------
BASE TABLE Table1 myName1
BASE TABLE Table2 myName2
VIEW View1 myName1
VIEW View2 myName2
其他回答
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 '%Label%'
ORDER BY schema_name, table_name;
用于显示具有指定列名的所有表的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'
您可以通过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>%'
SQL Server:
SELECT Table_Name, Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'YOUR_DATABASE'
AND COLUMN_NAME LIKE '%YOUR_COLUMN%'
神谕:
SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE column_name LIKE '%YOUR_COLUMN_NAME%'
AND OWNER IN ('YOUR_SCHEMA_NAME');
就这么简单!!(SQL,PL/SQL)我一直使用它来查找给定数据库(模式)中列名的所有实例。