Hello, readers, and welcome to Code2Night! In today's blog post, we're going to delve into the world of SQL Server and explore a common challenge that many developers face while working with large databases. Have you ever found yourself in a situation where you needed to find all the tables where specific columns were being used? If so, you're in the right place.

Working with extensive databases can be quite daunting, especially when you're trying to track down specific columns across numerous tables. Whether you're debugging an issue, optimizing performance, or simply trying to understand the database structure, finding the tables where certain columns are used can save you significant time and effort.

Fortunately, SQL Server provides us with powerful tools and techniques to tackle this challenge effectively. In this blog post, we'll explore various methods that will help us locate all the tables in our database that contain specific columns. From built-in functions to querying system tables, we'll cover a range of techniques that cater to different scenarios.

By the end of this post, you'll have a solid understanding of how to find tables with specific columns in SQL Server, empowering you to navigate your database with ease and efficiency. So, let's dive in and unlock the secrets of uncovering the tables where those sought-after columns reside.

Remember to bookmark this page or subscribe to our newsletter to stay updated with the latest tips, tricks, and insights for your SQL Server journey. Let's get started and master the art of discovering tables with specific columns in SQL Server!

SQL

So, in SQL server we often have to find all the tables by column name or you can say where we have used a specific column name. So for that purpose, we can use the following query. 

select * from INFORMATION_SCHEMA.COLUMNS 
where COLUMN_NAME like '%key%' 
order by TABLE_NAME

This query will return to us all the tables where we have used the Key column anywhere. You can have a look at the following image where we have found all tables  where specified column as use

So you can modify your column name in the place of the "key" accordingly and fetch out the correct results. This is how to find all tables by column name in the SQL server.