是否可以查询包含以下列的表名
LIKE '%myName%'
?
是否可以查询包含以下列的表名
LIKE '%myName%'
?
当前回答
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)我一直使用它来查找给定数据库(模式)中列名的所有实例。
其他回答
我们还可以使用以下语法:-
select * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%clientid%'
order by TABLE_NAME
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)我一直使用它来查找给定数据库(模式)中列名的所有实例。
我想要一些不会让我眼睛流血的桌子和视图。
查询
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
下面是Sybase数据库的工作解决方案
select
t.table_name,
c.column_name
from
systab as t key join systabcol as c
where
c.column_name = 'MyColumnName'
您可以通过column_name过滤器从INFORMATION_SCHEMA.COLUMNS中找到它
Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'