I want to show all tables that have specified column name

Question

How can I get a list of all the tables that have a specific column name

User · Answer

select table name from information schema columns where COLUMN NAME    MyColumn

User · Answer

You can find what you re looking for in the information schema   SQL Server 2005 System Tables and Views   I think you need SQL Server 2005 or higher to use the approach described in this article  but a similar method can be used for earlier versions

User · Answer

Pretty simple on a per database level  Use DatabaseName Select   From INFORMATION SCHEMA COLUMNS Where column name    ColName

User · Answer

SELECT t name AS table name  SCHEMA NAME schema id  AS schema name  c name AS column name   FROM sys tables AS t INNER JOIN sys columns c ON t OBJECT ID   c OBJECT ID  WHERE c name LIKE   YOUR COLUMN    ORDER BY schema name  table name    In depth article by SQL Authority

User · Answer

SELECT      T TABLE NAME  C COLUMN NAME FROM        INFORMATION SCHEMA COLUMNS C             INNER JOIN INFORMATION SCHEMA TABLES T ON T TABLE NAME   C TABLE NAME WHERE       TABLE TYPE    BASE TABLE              AND COLUMN NAME    ColName    This returns tables only and ignores views for anyone who is interested

User · Answer

--get tables that contains selected columnName  SELECT  c name AS ColName  t name AS TableName FROM sys columns c JOIN sys tables t ON c object id   t object id WHERE c name LIKE   batchno     its worked

User · Answer

You can use the information schema views   SELECT DISTINCT TABLE SCHEMA  TABLE NAME FROM Information Schema Columns WHERE COLUMN NAME    ID    Here s the MSDN reference for the  Columns  view  http   msdn microsoft com en-us library ms188348 aspx

User · Answer

If you re trying to query an Oracle database  you might want to use   select owner  table name  from all tab columns where column name    ColName

[sql] I want to show all tables that have specified column name

Examples related to sql

Examples related to sql-server-2008