SQL SERVER – Script to Find All Columns with a Specific Name in Database

My biggest pleasure for my job is meeting new people every day while doing a Comprehensive Database Performance Health Check. Recently, one of the clients asked me if I have a Script to Find All Columns with a Specific Name in Database. Yes, of course, I do have it and let us see it today.

Here is the script which can help us to identify any column with the column name from the database.

SELECT	OBJECT_SCHEMA_NAME(ac.object_id) SchemaName, 
		OBJECT_NAME(ac.object_id) TableName, 
		ac.name as ColumnName, tp.name DataType
FROM sys.all_columns ac
INNER JOIN sys.types tp ON tp.user_type_id = ac.user_type_id
WHERE ac.name = 'YourColumnName'
GO

For example, when I ran the above script with the database WideWorldImporters sample database to search for the column uniteprice, I got the following results which list all columns with the specific name.

USE WideWorldImporters;
SELECT	OBJECT_SCHEMA_NAME(ac.object_id) SchemaName, 
		OBJECT_NAME(ac.object_id) TableName, 
		ac.name as ColumnName, tp.name DataType
FROM sys.all_columns ac
INNER JOIN sys.types tp ON tp.user_type_id = ac.user_type_id
WHERE ac.name = 'unitprice'
GO

Here is the output of the query:

You can also change the where clause to show you the output of all the columns from a single table as well. You just have to change the where condition to OBJECT_NAME(ac.object_id) TableName = ‘YourPreferredName’.

Here are a few recent blog posts which you may find helpful as well.

• SQL SERVER – How to Identify Columns Used In A View?
• SQL SERVER – How to Find UNIQUE Key Columns? – sp_special_columns
• SQL SERVER – Query Listing All the Indexes Key Column with Included Column
• SQL SERVER – How to Count a Particular Value Across All Columns?
• SQL SERVER – Swap Column Values In Table
• SQL SERVER – Find All Tables Containing Specific Column Name
• SQL SERVER – Optimize Key Lookup by Creating Index with Include Columns
• How Default Value and Nullable Column Works? – Interview Question of the Week #129
• SQL SERVER – Group By Orders Data by Columns Ascending

SQL Server fundamentals can never get old. Just the other day, while working with a client on Comprehensive Database Performance Health Check, the DBA asked me an interesting question referring to my older blog post How to Know If Index is Forced on Query?. The question was about Group By Orders Data by Columns Ascending.

If you know any such tip, do let me know and I will post this on the blog with due credit to you. You may add me to your LinkedIn here.

Reference: Pinal Dave (https://blog.sqlauthority.com)