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)
6 Comments. Leave new
I found this query to be useful when I’m looking for bad SQL. One of the most common errors is to give the same data element different names and declarations in different tables.
AND (COLUMN_NAME LIKE ’emp_id’
OR COLUMN_NAME LIKE ’employee_id’
OR COLUMN_NAME LIKE ’employee_inbr’
OR …)
After you decide on the data element name, then you need to go back through and check to see their all declared the same way
Hi, how do you make a primary key scheme when the application is used at multiple remote places and gets synced to single database.
Sir,
Any script available to replace all GUID column as primary or foreign key be replaced by identity bigint in all database columns and pk, fk relationship without breaking referential integrity programmatically. Most of the tables have ui as primary key, and tables referring these related other table ui columns have that table name + _ui as fk
Pinal, I thought the information_schema objects were deprecated? Or at least I read that at one point on MSDN. Cannot seem to find it any more.
I do not think it is deprecated and I searched again and did not find anything as well.
Hello Pinal, I usually prefer using sys.tables and sys.columns, since these objects align well with other system views – sys.databases, sys.indexes etc.