DEV Community

Cover image for SQL-Quick tip #6 - Find table or column
Allan Simonsen
Allan Simonsen

Posted on

2 1

SQL-Quick tip #6 - Find table or column

Sql Server tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Find table by name

If you have ever worked on a big database with a huge number of tables and you are not yet familiar with the whole data model, you know how hard it is to locate that specific table you are looking for. Or maybe you just need to get a list of tables that have similar names to put into that Power Point presentation you are working on.
Then the following query may be useful to you.

SELECT *
  FROM sys.tables
 WHERE name LIKE '%partOfTableName%'
Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Find table that have a specific column

If you have been a programmer for some time and worked on different projects for different customers then you have probably some across databases with few or entirely without foreign key constraints. There can be a lot of reasons for not having foreign keys constraint and I won't go into them here, but one consequence of not having them is that finding tables that is related to other tables becomes hard so you need another way of finding related tables i to hope that the person who implemented the tables named the columns relating the tables by the same name or a similar name. And if this is the case you can query all your database tables to find all tables containing columns with a specific name. This query below does that.

USE Northwind

DECLARE @searchText VARCHAR(200) = 'address'

 SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS [Table name],
        c.name AS [Column name]
   FROM sys.tables AS t
   JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
  WHERE c.name LIKE '%'+ @searchText +'%'
  ORDER BY [Table name];
Enter fullscreen mode Exit fullscreen mode

Sql Server Management Studio screenshot

Image of Docusign

Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

Top comments (0)

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay