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.

SQL SERVER - Script to Find All Columns with a Specific Name in Database allcolumns0-800x212

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.

Solarwinds
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:

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

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 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)

Solarwinds
, ,
Previous Post
SQL SERVER – Always On Listener Failure – Provisioning Computer Object Failed With Error 5
Next Post
SQL SERVER – Display Rupee Symbol in SSMS

Related Posts

2 Comments. Leave new

  • Carter Cordingley
    January 22, 2020 8:00 am

    I have actually written a query that pulls this info from a link server (mysql)and generates openqueries for each table. This has saved me .

    Other query that is good are a search for key word(s) in a SP ,function, trigger, …

    Reply
  • Although it’s not DMV, it’s ANSI Standard and really quite good.

    SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, DATA_TYPE, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ‘unitprice’

    Reply

Leave a Reply

Menu