Search specific field name

you mean a column name or you mean a value stored in the data of tables?

column name

You can use this query:

SELECT C.name, T.name
FROM sys.columns AS C
INNER JOIN sys.tables AS T
ON T.object_id = C.object_id
WHERE C.name = 'YourColumnNameHere'

Select allOpen in new window

I am trying to find out the definition of these columns. I don't know where it save

But I suggest that you install this free tool: SQL Search - Find SQL fast in SQL Server Management Studio and Visual Studio 2017 (red-gate.com) 

>>I am trying to find out the definition of these columns. I don't know where it save 

I don't understand what you are asking for!

Description of the above columns. It stores somewhere in the database.

you want to see the table definition? Right click on your table and select Design.

There is another table in the database which holds the description. For instance, I just found one table name Origin which holds the information of column origin.

ORIGIN
------
origin   Description
384      Chicago
462      Memphis

Unless you have created Relationships when you created your tables (to define relations between 2 tables), you won't automatically get these descriptions. 

They are already created. There are over 500 tables. I don't know if there is a way I can search each column name in different tables.

you can't always rely on column names. Very often a column will be called something like ID in a table and CountryID in another one.

Who created the database? Were relations created? Maybe a schema has been created?

>>There is another table in the database which holds the description.

If I understand the problem:  You are looking for what are normally called lookup tables.  Where a main table holds the key value and what 'text' that gets resolved to is in a smaller lookup table.

Like states:  You store the state abbreviation in the employee_address table and to find the actual state name, you need to join to a state_lookup table.

IF, and this is a BIG IF, your database was designed properly, you should have foreign keys defined between the tables.

If so, you can generate a database diagram to visually see the tables and foreign key relationships:
https://www.mssqltips.com/sqlservertip/6269/sql-server-database-diagram-tool-in-management-studio/

Yes, I'm looking for lookup tables. Unfortunately, there's no foreign key.

I don't know SQL Server well enough to provide the query to pull the foreign keys for a given table from the data dictionary.  They are out there on the Web somewhere.

I would go with the database diagram.

To continue a comment Eric made about the ID column in the lookup and CountryID column in the parent table:  You cannot go by column names.  You have to go by constraints.

If your database is poorly designed and you don't have constraints, then you have to go by personal knowledge of how the database was designed.

If you have neither:
Then you get the fun of searching ALL tables and ALL columns for a specific value, THEN you get to guess you have the right lookup for the right parent table!

This is where source code can help:  Find the code that queries the information you are after and see how it joins the tables.

OR, look at the queries in the server's cache and hope it has the joins.

Do you have access to the Front-end, you could reverse engineer that to figure out which tables are use as lookups for which fields.

Do you have access to the Front-end

Unfortunately not.

Then you're going to have to start documenting and going through the tables/data and try and figure things out.  Not fun!

I didn't see where they confirmed the database doesn't have foreign keys defined?

If they exist, there is "magic" to see what lookup tables are used by other tables.  It's in the data dictionary.

How do I find the definition of a table?

Define "definition of a table"?

Eric already showed you the GUI way in #a43422537

I'm not an SSMS person but I bet it you click the + beside the table it will show you things like indexes, constraints, etc...

In another question from you there is a join used:

from dbo.PTPUR p inner join dbo.ACTXN a     on p.jeid = a.jeid 

Select allOpen in new window

so IF that is a useful precedent to follow I would assume that the column [mcaid] found in table ACTXN also links to a column of the same name in another table. IF that is true then the query given to you earlier would list the tables that ACTXN.mcaid might relate to:

SELECT C.name AS column_name, T.name AS table_name
FROM sys.columns AS C 
INNER JOIN sys.tables AS T ON T.object_id = C.object_id 
WHERE C.name = 'mcaid'
AND t.name <> 'ACTXN' -- this condition is optional

Select allOpen in new window

Thank You.