Total Page Preview: 000000022560
SQL SERVER - write a query to find all tables in a Database that have a specific column name
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[db_ITHUBCITY]
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
OUTPUT :
Find All specific column in all tables and View:
USE[db_ITHUBCITY]
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
OUTPUT:
Thank You
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com