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
@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]
@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%'
