Total Page Preview:   000000016782

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 Column With Table Name
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:

Find All Column With Table Name And View
 
 
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



Comments

Anuj Panwar
01-Sep-2016
Nice article...
ITHUBCITY LTD.
01-Sep-2016
Thanks Anuj

                           
                           

                           

                           

Facebook User: