Finding a Named column present in a table

Question

I am in spree to find a SQL or T-SQL query to get names of all the tables in a Database/Schema which have a column of given name.

Can any one help me in this please?

Thanks in Advance :)

Answer 1

This will find the columns you're after in the current/active database. If you're in a different database, just prefix INFORMATION_SCHEMA.COLUMNS with the database name (e.g. dbname.INFORMATION_SCHEMA.COLUMNS)

DECLARE @Schema varchar(max)
DECLARE @Column varchar(max)

SET @Schema = NULL
SET @Column = 'primary_id'

SELECT DISTINCT
    TABLE_NAME, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = @Column
    AND
    (
        @Schema IS NULL
            OR TABLE_SCHEMA = @Schema
    )

This will work in SQL Server and should work in other DBMS's too since INFORMATION_SCHEMA is somewhat standardised (e.g. here it is in MySql: http://dev.mysql.com/doc/refman/5.0/en/columns-table.html)

Answer 2

This query will give you all tables with a column named foo

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'foo'

Answer 3

You can use the INFORMATION_SCHEMA.COLUMNS system view:

SELECT DISTINCT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Name of column';

Answer 4

select TABLE_NAME from Your_Database_Name.INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%ColumnName%' group by TABLE_NAME

Answer 5

Be aware that while correct for SQL*Server (and mySQL - and postGres) the answer is different for Oracle, as they don't support the Information schema (which is ANSI SQL-92, so they have had plenty of time to do so).

On Oracle you would use

SELECT table_name, column_name
FROM ALL_TAB_COLUMNS
WHERE column_name = :column_name

Or you could create a schema and view to do the job of the missing information_schema.columns.

Answer 6

in sybase;

declare @column varchar(30)
select @column = 'column_name'
select object_name(id), @column from DB_NAME..syscolumns where name = @column