Find all tables containing column with specified n

Question

Is it possible to query for table names which contain columns being

LIKE '%myName%'

?

Answer 1

To get full information: column name, table name as well as schema of the table..

SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%col_Name%'

Answer 2

You can find it from INFORMATION_SCHEMA.COLUMNS by column_name filter

Select DISTINCT TABLE_NAME as TableName,COLUMN_NAME as ColumnName
     From INFORMATION_SCHEMA.COLUMNS Where column_name like '%myname%'

Answer 3

DECLARE @columnName as varchar(100)
SET @columnName = 'ColumnName'

SELECT t.name AS Table, c.name AS Column,
ty.name AS Type, c.max_length AS Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
WHERE c.name LIKE @columnName
ORDER BY t.name, c.name

Answer 4

Create table #yourcolumndetails(
DBaseName varchar(100), 
TableSchema varchar(50), 
TableName varchar(100),
ColumnName varchar(100), 
DataType varchar(100), 
CharMaxLength varchar(100))

EXEC sp_MSForEachDB @command1='USE [?];
    INSERT INTO #yourcolumndetails SELECT
    Table_Catalog
    ,Table_Schema
    ,Table_Name
    ,Column_Name
    ,Data_Type
    ,Character_Maximum_Length
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME like ''origin'''

select * from #yourcolumndetails
Drop table #yourcolumndetails

Answer 5

Like oracle you can find tables and columns with this:

select table_name, column_name
from user_tab_columns 
where column_name 
like '%myname%';

Answer 6

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 '%Label%'
ORDER BY schema_name, table_name;