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


Saturday, 16 January 2016

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
Share with: 

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


Support this Site



Popular Articles

What is Kutamo?
Kilimanjaro 2023
Kilimanjaro 2015
Kilimanjaro 2013
Australian Postcodes
New Zealand Postcodes
Worldwide City Database

Recent Articles

Favourite Links

Kutamo Studios
ErrLog.IO
Kutamo
AfterTheCloud
Kilimanjar 2023