是否可以查询包含以下列的表名
LIKE '%myName%'
?
是否可以查询包含以下列的表名
LIKE '%myName%'
?
当前回答
此外,还可以找到具有指定模式的列名。
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%'
其他回答
DECLARE @columnName as varchar(100)
SET @columnName = 'ColumnName'
SELECT t.name AS Table, c.name AS Column,
ty.name AS Type, c.max_length AS Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.name LIKE @columnName
ORDER BY t.name, c.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'
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博客
您可以通过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 COLUMN_NAME like '%clientid%'
order by TABLE_NAME