sql - 查找包含具有指定名称的列的所有表 - MS SQL Server

Question

想要改进这篇文章吗？提供此问题的详细答案，包括引用和解释为什么你的答案是正确的。不够详细的答案可能会被编辑或删除。

是否可以查询包含以下列的表名

LIKE '%myName%'

？

Answer 1

搜索表:

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;

搜索表和 View :

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%MyName%'
ORDER BY    TableName
            ,ColumnName;