Find all tables containing column with specified name - MS SQL Server

I used this for the same purpose and it worked:

select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_CATALOG= 'theDatabase'
and COLUMN_NAME like 'theCol%'

Just to improve on the answers above i have included Views as well and Concatenated the Schema and Table/View together making the Results more apparent.

DECLARE @COLUMNNAME AS VARCHAR(100);

SET @COLUMNNAME = '%Absence%';

SELECT CASE
WHEN [T].[NAME] IS NULL
THEN 'View'
WHEN [T].[NAME] = ''
THEN 'View'
ELSE 'Table'
END AS [TYPE], '[' + [S].[NAME] + '].' + '[' + CASE
WHEN [T].[NAME] IS NULL
THEN [V].[NAME]
WHEN [T].[NAME] = ''
THEN [V].[NAME]
ELSE [T].[NAME]
END + ']' AS [TABLE], [C].[NAME] AS [COLUMN]
FROM [SYS].[SCHEMAS] AS [S] LEFT JOIN [SYS].[TABLES] AS [T] ON [S].SCHEMA_ID = [T].SCHEMA_ID
LEFT JOIN [SYS].[VIEWS] AS [V] ON [S].SCHEMA_ID = [V].SCHEMA_ID
INNER JOIN [SYS].[COLUMNS] AS [C] ON [T].OBJECT_ID = [C].OBJECT_ID
OR
[V].OBJECT_ID = [C].OBJECT_ID
INNER JOIN [SYS].[TYPES] AS [TY] ON [C].[SYSTEM_TYPE_ID] = [TY].[SYSTEM_TYPE_ID]
WHERE [C].[NAME] LIKE @COLUMNNAME
GROUP BY '[' + [S].[NAME] + '].' + '[' + CASE
WHEN [T].[NAME] IS NULL
THEN [V].[NAME]
WHEN [T].[NAME] = ''
THEN [V].[NAME]
ELSE [T].[NAME]
END + ']', [T].[NAME], [C].[NAME], [S].[NAME]
ORDER BY '[' + [S].[NAME] + '].' + '[' + CASE
WHEN [T].[NAME] IS NULL
THEN [V].[NAME]
WHEN [T].[NAME] = ''
THEN [V].[NAME]
ELSE [T].[NAME]
END + ']', CASE
WHEN [T].[NAME] IS NULL
THEN 'View'
WHEN [T].[NAME] = ''
THEN 'View'
ELSE 'Table'
END, [T].[NAME], [C].[NAME];

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;

Like oracle you can find tables and columns with this:



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

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

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

If you’re more into third party tools there a lot of options there such as:

- [ApexSQL Search][1]
- [SSMS Toolpack][2]
- [Red Gate tools][3].

These come in very handy if your database contains encrypted objects (views, procedures, functions) because you can’t easily search for these using system tables.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

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

You can try this query:

USE AdventureWorks
GO
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 '%myName%'

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