是否可以查询包含以下列的表名
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
其他回答
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
为了改进上面的答案,我还包括了视图,并将模式和表/视图连接在一起,使结果更加明显。
DECLARE @COLUMNNAME AS VARCHAR(100);
SET @COLUMNNAME = '%Absence%';
SELECT CASE
WHEN [T].[NAME] IS NULL
THEN 'View'
WHEN [T].[NAME] = ''
THEN 'View'
ELSE 'Table'
END AS [TYPE], '[' + [S].[NAME] + '].' + '[' + CASE
WHEN [T].[NAME] IS NULL
THEN [V].[NAME]
WHEN [T].[NAME] = ''
THEN [V].[NAME]
ELSE [T].[NAME]
END + ']' AS [TABLE], [C].[NAME] AS [COLUMN]
FROM [SYS].[SCHEMAS] AS [S] LEFT JOIN [SYS].[TABLES] AS [T] ON [S].SCHEMA_ID = [T].SCHEMA_ID
LEFT JOIN [SYS].[VIEWS] AS [V] ON [S].SCHEMA_ID = [V].SCHEMA_ID
INNER JOIN [SYS].[COLUMNS] AS [C] ON [T].OBJECT_ID = [C].OBJECT_ID
OR
[V].OBJECT_ID = [C].OBJECT_ID
INNER JOIN [SYS].[TYPES] AS [TY] ON [C].[SYSTEM_TYPE_ID] = [TY].[SYSTEM_TYPE_ID]
WHERE [C].[NAME] LIKE @COLUMNNAME
GROUP BY '[' + [S].[NAME] + '].' + '[' + CASE
WHEN [T].[NAME] IS NULL
THEN [V].[NAME]
WHEN [T].[NAME] = ''
THEN [V].[NAME]
ELSE [T].[NAME]
END + ']', [T].[NAME], [C].[NAME], [S].[NAME]
ORDER BY '[' + [S].[NAME] + '].' + '[' + CASE
WHEN [T].[NAME] IS NULL
THEN [V].[NAME]
WHEN [T].[NAME] = ''
THEN [V].[NAME]
ELSE [T].[NAME]
END + ']', CASE
WHEN [T].[NAME] IS NULL
THEN 'View'
WHEN [T].[NAME] = ''
THEN 'View'
ELSE 'Table'
END, [T].[NAME], [C].[NAME];
您可以在SQL Server中使用以下查询。
SELECT column_name AS 'ColumnName',
table_name AS 'TableName'
FROM information_schema.columns
WHERE column_name LIKE '%columnname%'
AND table_schema = 'schema'
ORDER BY tablename,
columnname;
SELECT [TABLE_NAME] ,
[INFORMATION_SCHEMA].COLUMNS.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE '%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)我一直使用它来查找给定数据库(模式)中列名的所有实例。