In this article i am goint to explain write a Scrits to find all tables in a db that have a specific column name. I quickly wrote down following script which will go return all the tables containing specific column along with their schema name.
Find All specific column in all tables:
USE[database]
SELECT
tbl.name AS 'TableName',
Col.name AS 'ColumnName',
SCHEMA_NAME(SCHEMA_ID) AS 'SchemaName'
FROM sys.columns AS Col
INNER JOIN sys.tables AS tbl ON Col.object_id = tbl.object_id
WHERE Col.name = 'questionid' -- Your Column Name
Find All specific column in all tables and View:
USE[database]
SELECT DISTINCT
TABLE_CATALOG AS 'DatabaseName',
TABLE_SCHEMA AS 'SchemaName',
TABLE_NAME AS 'TableNameAndViewName',
COLUMN_NAME AS 'ColumnName'
FROM INFORMATION_SCHEMA.columns
WHERE column_name = 'questionid' ---- Your Column Name
