SQL Query to find out specific column from all database

SQL Query to find out specific column from all database

sql query to find column name in database
sql query to find column name in all tables
sql query to find tables with a specific value in any column
how to find table name from column value in sql server
sql query to list all tables and columns in a database
how to find column name from column value in sql server
sql query to get column names from table
query to get column names of a table in sql server

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

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).

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??

How to find specific column in SQL Server database?, How do I find a specific column in SQL database? In this article, we will show how to write a SQL Query to Find all Tables that Contain Specific Column Name with example. For this SQL Interview Question, We are using the SQL Tutorial database. The below screenshot will show you the tables inside that database.


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

Find all tables containing column with specified name, How do I find a specific column in all tables in SQL? Article for: SQL Server Azure SQL Database Oracle database MySQL PostgreSQL MariaDB IBM Db2 Amazon Redshift Snowflake Teradata Vertica Query below finds all tables that have 'ProductID' column. See also tables that don't have a column with specific name .


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 SERVER, How do I find a particular column name in all tables in SQL Server? How can I find all columns of a certain type (for example NTEXT) in all tables in a SQL Server database? I am looking for a SQL query. This will also work on Azure SQL (aug 2018) and I used it to convert columns to nvarchar (max) because NText will be deprecated. alter table [tablename] alter column [columnname] nvarchar (max).


How to find column names for all tables in all databases in SQL Server, I quickly wrote down following script which will go return all the tables containing specific column along with their schema name. If you want to find all the column name from your database run following script. You can down any condition in WHERE clause to get desired result. Reference : Pinal Dave ( https://blog.sqlauthority.com )


Find Column in All Databases, Here is a basic example to get all columns in all databases: --This query will select the Database name, all tables/views and their columns (in a comma COLUMNS).sql -- Utility to find all columns in all databases or find specific with a like  One row represents one column in a specific view in a database. Scope of rows: all columns in views in SQL Server database. Ordered by schema name, view name and column position.


List table columns in SQL Server database, Find Column in All Databases databases on a particular server for either columns with a specific name, or columns relating to a specific subject. It will then return you all the columns on the server that are named like the  From there, it’s a simple matter of selecting the ColumnName and TableName of our results, and finally, of course, only looking up records where sys.columns.name is equal to our ColumnName string. However, this query will only find exact matches of the column name.