CODEWITHSUNDEEP
sql-server
pgee70
pgee70

Reputation: 3984

how find column in any database in a microsoft sql server

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

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?

Upvotes: 0

Views: 55

Answers (2)

otri
otri

Reputation: 580

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.

Upvotes: 0

Sandeep Kumar
Sandeep Kumar

Reputation: 315

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

Upvotes: 1

Related Questions