Finding a the correct field in in many databases

Is there a way to find a certain fieldname with big SQL database with many tables.

For example.   Database01    has 50 tables.     Microsoft SQL 2016

how would i find a filed called  Node if i don't know the location of the table?  Since there are 50 tables, it would be very hard to go through each table looking for the filed Node.


any help would be great.
sirichaiphumiratAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
SELECT OBJECT_NAME(object_id) AS table_name, name AS column_name, column_id    
FROM sys.columns
WHERE name = 'Node' AND
    OBJECTPROPERTYEX(object_id, 'BaseType') = 'U'
0
 
Pawan KumarDatabase ExpertCommented:
There are two options for you. QUERY/FREE Tool
1. Write a query. For this you can refer
http://www.sqlmatters.com/Articles/Searching%20all%20columns%20in%20all%20tables%20in%20a%20database.aspx
2. Install a tool.
Eg. a. https://www.apexsql.com/sql_tools_search.aspx ,
      b. http://sqllocator.com/
Install and it will do a quick search for you.
0
 
Éric MoreauSenior .Net ConsultantCommented:
give a try to the free Red Gate SQL Search: https://www.red-gate.com/products/sql-development/sql-search/index
0
 
sirichaiphumiratConnect With a Mentor Author Commented:
Thanks everyone i was able to get it working with this code.

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%Node%'
ORDER BY    TableName
            ,ColumnName;


Hard part is now i'll have to narrow down the tables. But its better than searching everything.
1
 
sirichaiphumiratAuthor Commented:
thank you.. worked great.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.