是否可以查询包含以下列的表名
LIKE '%myName%'
?
是否可以查询包含以下列的表名
LIKE '%myName%'
?
当前回答
USE AdventureWorks
GO
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 '%EmployeeID%'
ORDER BY schema_name, table_name;
来自Pinal Sir博客
其他回答
要获取完整信息:列名、表名以及表的架构。。
SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%col_Name%'
它将返回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 c.name AS 'ColumnName'
,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%MyName%'
ORDER BY TableName
,ColumnName;
搜索表和视图:
SELECT COLUMN_NAME AS 'ColumnName'
,TABLE_NAME AS 'TableName'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%MyName%'
ORDER BY TableName
,ColumnName;
我出于同样的目的使用了这个,它起到了作用:
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG= 'theDatabase'
and COLUMN_NAME like 'theCol%'
SELECT table_schema + '.' + table_name,
column_name
FROM [yourdatabase].information_schema.columns
WHERE column_name LIKE '%myName%'
这将为您提供需要查找的列的表名。