SQL Query to find out specific column from all database

Question

I wanted to find out the columns which contains the word "VAP" or cancellation. but the problem is we have several number of databases in our server. I wanted to know in which database/table contains the columns name which consist these words. Can anyone let us know if there is any query which can be used to search column name from all databases? I have tried below query but it will only help to find from a single database.

select distinct
    t.name as TableName,
    SCHEMA_NAME(t.schema_id)   as TableSchema,
    c.name as ColumnName,
    ct.name as ColumnDataType,
    c.is_nullable as IsNullable
from 
    sys.tables t with(nolock)
inner join 
    sys.columns c with(nolock) on t.object_id = c.object_id
inner join 
    sys.types ct with(nolock) on ct.system_type_id = c.system_type_id
where 
    c.name like '%VAP%'
order by 
    t.name

Run same query for all databases: https://stackoverflow.com/questions/18462410/how-to-run-the-same-query-on-all-the-databases-on-an-instance — Alex, Nov 15 '17 at 05:27

score 0 · Answer 1 · answered Nov 15 '17 at 05:54

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

enter image description here

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

score 0 · Answer 2 · answered Nov 15 '17 at 06:19

I know this is little lengthy, But You can use this query if you do not wish to use any third party applications

USE [master]
GO
DECLARE @Search VARCHAR(50)='A',@SQL VARCHAR(MAX)
DECLARE @Min INT,@Max INT
DECLARE @Table TABLE
(
    SeqNo INT IDENTITY(1,1),
    DatabaseNm VARCHAR(255),
    TableName VARCHAR(255),
    ColumnName VARCHAR(255)
)
DECLARE @TEMP TABLE
(
    SeqNo INT IDENTITY(1,1),
    Qry VARCHAR(MAX)
)

INSERT INTO @TEMP
(
    Qry
)
SELECT 
    Qry = 'SELECT 
        DbNm = '''+name+''',
        TblNm = [TABLE_SCHEMA]+''.''+[TABLE_NAME],
        COlNm = COLUMN_NAME
        FROM ['+name+'].INFORMATION_SCHEMA.COLUMNS 
            WHERE COLUMN_NAME LIKE ''%'+ISNULL(@Search,'')+'%''' 
    FROM sys.databases

SELECT
    @Min = MIN(SeqNo),
    @Max = MAX(SeqNo)
    FROM @TEMP

WHILE ISNULL(@Min,0)<=ISNULL(@Max,0)
BEGIN

    SELECT
        @SQL = Qry
        FROM @TEMP
            WHERE SeqNo = @Min

    INSERT INTO @Table
    (
        DatabaseNm,
        TableName,
        ColumnName
    )
    EXEC(@SQL)


    SELECT
        @Min = ISNULL(@Min,0)+1
END


SELECT
    *
    FROM @Table

use [`QUOTENAME`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql?view=sql-server-2017). Your script is subject to SQL Injection via the metadata names. — Remus Rusanu, Jun 06 '18 at 14:34

score 0 · Answer 3 · edited Jun 06 '18 at 13:59

Use sp_MSForeachdb for getting details. Try below query.

-------------------------

create table #temp
(dbname varchar(100),
tablename varchar(100)
) 

EXECUTE master..sp_MSForeachdb ' 
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN


IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.columns where COLUMN_NAME = ''VAP''))
BEGIN
insert into #temp
select ''?'',table_name FROM INFORMATION_SCHEMA.columns where COLUMN_NAME = ''VAP''



end   


END
'

select * from #temp

SQL Query to find out specific column from all database

3 Answers3