Total Page Preview:   000000018082

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
Bowens
07-Jul-2021
Hello there! This post couldn't be written any better! Reading this post reminds me of my previous room mate! He always kept chatting about this. I will forward this article to him. Fairly certain he will have a good read. Thanks for sharing!
Bowens
07-Jul-2021
Hello there! This post couldn't be written any better! Reading this post reminds me of my previous room mate! He always kept chatting about this. I will forward this article to him. Fairly certain he will have a good read. Thanks for sharing!

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: