Database SQL Query: Find all table names with column name

SQL Server:

SELECT c.name as 'Column Name',t.name as 'Table Name'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%<ColumnName>%'
ORDER BY TableName,ColumnName asc;

SELECT COLUMN_NAME as 'Column Name', TABLE_NAME as 'Table Name'

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE '%<ColumnName>%'

ORDER BY    ColumnName, TableName asc;

SELECT name as 'Table Name'

FROM sysobjects 

WHERE id IN (SELECT id 

                          FROM syscolumns 

                          WHERE name like '%<ColumnName>%')

SELECT name as 'Table Name'

FROM sys.tables 

WHERE OBJECT_ID IN ( SELECT id 

                                               FROM syscolumns 

                                               WHERE name like '%<ColumnName>%' )

Oracle:

SELECT owner, table_name, column_name 

FROM all_tab_columns 

WHERE column_name LIKE '%<ColumnName>%' 

AND owner in ('<YOUR_SCHEMA_NAME>');

In above query replace <ColumnName> with any other column name.

If you simply want the table name you can run:

select object_name(object_id) from sys.columns where name like '%received_at%'


If you want the Schema Name as well (which in a lot of cases you will, as you'll have a lot of different schemas, and unless you can remember every table in the database and where it belongs this can be useful) run:

select OBJECT_SCHEMA_NAME(object_id),object_name(object_id) from sys.columns where name like '%received_at%'


and finally if you want it in a nicer format (although this is where the code (In my opinion) is getting too complicated for easy writing):

select concat(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) from sys.columns where name like '%received_at%'


note you can also create a function based on what I have:

CREATE PROCEDURE usp_tablecheck
--Scan through all tables to identify all tables with columns that have the provided string
--Stephen B
@name nvarchar(200)
AS
SELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS [Table Name], name AS [Column] FROM sys.columns
WHERE name LIKE CONCAT('%',@name,'%')
ORDER BY [Table Name] ASC, [Column] ASC
GO

It is worth noting that the concat feature was added in 2012. For 2008r2 and earlier use + to concatenate strings.

I've re-formatted the proc a bit since I posted this. It's a bit more advanced now but looks a lot messier (but it's in a proc so you'll never see it) and it's formatted better.

This version allows you to have it in an administrative database and then search through any database. Change the decleration of @db from 'master' to whichever you want the default database to be (NOTE: using the CONCAT() function will only work with 2012+ unless you change the string concatenation to use the + operators).

CREATE PROCEDURE [dbo].[usp_tablecheck]
    --Scan through all tables to identify all tables in the specified database with columns that have the provided string
    --Stephen B
    @name nvarchar(200)
    ,@db nvarchar(200) = 'master'
AS
    DECLARE @sql nvarchar(4000) = CONCAT('
        SELECT concat(OBJECT_SCHEMA_NAME(col.object_id,DB_ID(''',@db,''')),''.'',object_name(col.object_id,DB_ID(''',@db,'''))) AS [Table Name]
            ,col.name AS [Column] 
        FROM ',@db,'.sys.columns col
        LEFT JOIN ',@db,'.sys.objects ob 
            ON ob.object_id = col.object_id
        WHERE 
            col.name LIKE CONCAT(''%'',''',@name,''',''%'') 
            AND ob.type =''U''
        ORDER BY [Table Name] ASC
            ,[Column] ASC')
    EXECUTE (@sql)
GO

Hopefully this isn't a duplicate answer, but what I like to do is generate a sql statement within a sql statement that will allow me to search for the values I am looking for (not just the tables with those field names ( as it's usually necessary for me to then delete any info related to the id of the column name I am looking for):

  SELECT  'Select * from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

Then I can copy and paste run my 1st column "SQLToRun"... then I replace the "Select * from ' with 'Delete from ' and it allows me to delete any references to that given ID! Write these results to file so you have them just in case.
NOTE**** Make sure you eliminate any bakup tables prior to running your your delete statement...

  SELECT  'Delete from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'