SQL Tips #2 - Searching for a specific field name in a SQL table


Saturday, 16 January 2016

Share with: 
 

Ever wanted to find out which of your dozens of tables contains a certain fieldname or part of a fieldname? I did recently, as I needed to rename a field that was incorrectly named due to lack of coffee.

How it works

The query is very simple - it just searches the sys.tables collection for a column name that contains a string

The query looks joins the sys.columns< collection onto sys.tables, so that it can search a recordset containing all tables and their columns.

We're also aliasing the table name from t.name and column name from c.name to simplify the query.

The Query

This example query looks for any table that contains a field with the word userid in it.


SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%userid%' order by tablename

The Output

The query will return the matching column name and table in which its contained.

userid	tblAccountManagers
userid	tblUsers

Disclaimer

These queries are provided as a guide, and are by no means perfect. I use these on Microsoft SQL Server 2012, 2014 and 2016.

Tags

SQL, Programming
Useful SQL tips - how to list all tables that have column names that match a keyword
 
 

Popular Articles

What is Kutamo?
Kilimanjaro 2015
Exploring Lync and IoT
Exchange 2013 in 60 minutes
Monitoring Lync with MRTG
Lync UCWA Tutorial - Introduction
Tutorial Parts 1 | 2 | 3 | 4 | 5

Recent Articles

Australian Postal Codes
Skype Web SDK
Using the Skype Web SDK from any language or framework
Building a Skype for Business Auto Responder using the Skype Web SDK
Exporting Lync or Skype for Business Contacts with the Skype Web SDK

Favourite Links

Kutamo Studios
ErrLog.IO
Kutamo
Telco Together Foundation
Cloud on Kilimanjaro