是否可以查询包含以下列的表名
LIKE '%myName%'
?
是否可以查询包含以下列的表名
LIKE '%myName%'
?
当前回答
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
其他回答
为此,我们可以使用sp_columns。
sp_columns 'table name', null, null, '%column name%'
SELECT table_schema + '.' + table_name,
column_name
FROM [yourdatabase].information_schema.columns
WHERE column_name LIKE '%myName%'
这将为您提供需要查找的列的表名。
这应该是有效的:
SELECT name
FROM sysobjects
WHERE id IN ( SELECT id
FROM syscolumns
WHERE name like '%column_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;
下面是Sybase数据库的工作解决方案
select
t.table_name,
c.column_name
from
systab as t key join systabcol as c
where
c.column_name = 'MyColumnName'