During the recent Comprehensive Database Performance Health Check, I was asked if I know any trick to identify column with a Specific Name in the database. It is very easy to do this task if we use information_schema.
One of the large organizations was using GUID as a primary key and during consultancy, we determined that performance is very poor due to this GUID. We did a month-long step by step project where we removed every single dependency of the GUID and created a more efficient primary key. Once the project was completed we needed to remove the GUID columns from every single table. The challenge was that every table had the GUID column with a different name. Only one thing which was common was that each column name contained the word GUID in it.
Here is the script which you can run for your database and Find All Tables Containing Specific Column Name.
SELECT Table_Name, Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'YOUR_DATABASE' AND COLUMN_NAME LIKE '%YOUR_COLUMN%'
There are many different ways to do a task but I find the method to use Information_schema very efficient. Let me know if you have ever faced situation where you have to find a column with a specific name in your database. If yes, I would love to know the situation where you have to do this.
Here are a few related blog posts which you may find interesting:
SQL SERVER – DMV to Get Host Information – sys.dm_os_host_info
SQL SERVER – Introduction to Log Space Usage DMV – sys.dm_db_log_space_usage
SQL SERVER – New DMV in SQL Server 2017 – sys.dm_os_enumerate_fixed_drives. A Replacement of xp_fixeddrives
DMV to Replace DBCC INPUTBUFFER Command – Interview Question of the Week #100
SQL SERVER – DMVs to Detect Performance Problems in SQL Server – Notes from the Field #135
SQL SERVER – SQL Server: Getting OS information using DMV
SQL SERVER – Identify Table Where Clustered Index is Not a Primary Key – DMV
Reference: Pinal Dave (https://blog.sqlauthority.com)