如何在microsoft sql server中的任何数据库中查找列

I know that I can find the columns of any table in a microsoft sql server database using:

我知道我可以使用以下命令在microsoft sql server数据库中找到任何表的列:

USE dbname;
SELECT      COLUMN_NAME AS 'ColumnName',TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%search-for%'
ORDER BY    TableName,ColumnName;

Is it possible to search all databases in an instance with one query?

是否可以使用一个查询搜索实例中的所有数据库?

2 个解决方案

#1

sp_MSForEachDB @command1='USE ?;
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like ''%ColumnNameHere%'''

#2

DECLARE @Results TABLE(
     TABLE_CATALOG VARCHAR(50),
     TABLE_SCHEMA VARCHAR(50),
     TABLE_NAME VARCHAR(50),
     COLUMN_NAME VARCHAR(100)
);

INSERT INTO @Results
EXEC sp_MSforeachdb
@command1 = 'USE [?]; SELECT TABLE_CATALOG
                             ,TABLE_SCHEMA
                             ,TABLE_NAME
                             ,COLUMN_NAME
                      FROM INFORMATION_SCHEMA.COLUMNS 
                      WHERE COLUMN_NAME= ''YourColumnName'' ;';

SELECT * FROM @Results

Please refer to the comment from @Larnu.

请参阅@Larnu的评论。