CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Suha Alhabaj
Suha Alhabaj

Reputation: 101

Search for column name in multiple databases on SQL Server

I need to find specific columns in many databases, located on the same SQL Server 2008. I have tried many solutions over the internet, but none worked for more than system databases. What is the best way to extend the search to look inside all databases on one server?

Upvotes: 2

Views: 3422

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67281

Try it like this:

This solution uses the SP sp_MSforeachdb. This SP allows you, to use the same statement for alle existing databases. The Questionmark stand as place holder for the actual database name.

As we need to get the result in one single table I declare one first and use INSERT INTO SomeTable EXEC SomeSP-syntax to insert the result(s) of a SP into a fitting table (-variable):

Attention This SP is known for skipping some tables. Here's a thread to read more about this (including a solution for one special case).

DECLARE @ColumnName VARCHAR(100)='SomeColumnName';

DECLARE @Command VARCHAR(MAX)='USE [?]; SELECT TABLE_CATALOG
                                              ,TABLE_SCHEMA
                                              ,TABLE_NAME
                                              ,COLUMN_NAME 
                                        FROM INFORMATION_SCHEMA.COLUMNS 
                                        WHERE COLUMN_NAME=''' + @ColumnName + ''';';     

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

INSERT INTO @tbl
EXECUTE master.sys.sp_MSforeachdb @Command

SELECT * FROM @tbl;

UPDATE Another approach without sp_MSforeachdb

DECLARE @ColumnName VARCHAR(100)='SomeColumnName';

DECLARE @Template VARCHAR(MAX)='USE ***; SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME=''' + @ColumnName + ''';';  

DECLARE @Command VARCHAR(MAX)=
(
    SELECT REPLACE(@Template,'***', QUOTENAME(name))
    FROM sys.databases
    FOR XML PATH('')
);

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

INSERT INTO @tbl
EXEC (@Command);

SELECT * FROM @tbl

Upvotes: 4

Related Questions