查找包含指定名称列的所有表

技术背景

在数据库开发和管理过程中，有时需要查找包含特定列名的所有表。这在进行数据库架构审查、数据迁移、数据清理等操作时非常有用。不同的数据库系统提供了不同的方法来实现这一需求。

实现步骤

SQL Server

• 仅查找表

SELECT t.name AS table_name
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'column_name';

• 查找表和视图

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]

• 包含模式信息

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;

Oracle

SELECT owner, table_name, column_name 
FROM all_tab_columns 
WHERE column_name LIKE '%YOUR_COLUMN_NAME%'
AND OWNER IN ('YOUR_SCHEMA_NAME');

Sybase

select 
    t.table_name, 
    c.column_name 
from 
    systab as t key join systabcol as c 
where 
    c.column_name = 'MyColumnName';

核心代码

以下是在 SQL Server 中查找包含指定列名的表的通用代码：

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%'
ORDER BY TABLE_NAME;

最佳实践

• 使用存储过程：可以创建存储过程来封装查询逻辑，提高代码的复用性。

CREATE PROCEDURE usp_tablecheck
    @name nvarchar(200)
AS
SELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS [Table Name], name AS [Column] FROM sys.columns
WHERE name LIKE CONCAT('%',@name,'%')
ORDER BY [Table Name] ASC, [Column] ASC
GO

• 结合第三方工具：对于包含加密对象的数据库，可以使用第三方工具如 ApexSQL Search、SSMS Toolpack、Red Gate tools 等。

常见问题

• 性能问题：在大型数据库中，查询系统表可能会导致性能问题。可以考虑对系统表创建索引或优化查询语句。
• 兼容性问题：不同数据库系统的系统表和查询语法可能不同，需要根据实际使用的数据库进行调整。