can i seach in SQL to find specific tables/rows easily?

Question

i have a massive HR SQL database and i need to pull out 3 job code values (position, location & shift), along with the employee ID#.

is there a simple way to search the entire SQL database to find the specific table/array name where these points of information are stored?
i know that i can view the tables/columns/etc individually but w/over 100 of them and each having multiple columns/labels it will take me a very long time to find the specifc one i want.

it seems that this should be easy and that i am just not seeing/thinking of something obvious.

thanks in advance.

Answer 1

Are you saying that you want to find the tables these columns are in? If you are, I can only answer for Oracle, but I'm sure whatever RDBMS you are using has a similar method. In the data dictionary, there are several tables that can help, specifically here, DBA_TAB_COLUMNS. You can select the table name and the column name using a like clause
Something like
SELECT table_name, column_name from dba_tab_columns where column_name like '%SHIFT%'

Answer 2

individually???!!!! wow. I do not know where you got your sql from. so here goes some basics. first, (i hope) your database has been properly normalized. you have to have an employees table let's say, along with another table of shifts, positions and locations.

lets call them(hr_ for human resources):

hr_employees(employeeId, em_names, em_surnames, shiftId, locationId, positionId)
hr_shifts(shiftId, sh_description)
hr_locations(locationId, lo_description)
hr_position(positionId, po_description)

Each of the tables have unique id's that can distinguish each and every record in that table. with this in mind, your employee table will have all of the information for that employee stored in the table's fields, You have to perform joins to grab the description information for the shift, loc. and position, like described above on the table description. if you don't know the table relationships for the employee table either use your visual dba tool to search for foreign keys or type sp_help hr_employees(replace this table name with your table).

when you know which fields tie the information, you use the left and inner join(depending if you want to show what records are on both tables, or just on the table that's left of the join)

select * from hr_employee as e
inner join hr_shifts as s on e.shiftId=s.shiftId
inner join hr_position as p on e.positionId=p.positionId
inner join hr_location as l on e.locationId = l.locationId

The left join if you use it instead of inner, will result in showing all the records from the table to the left that were the result of the where conditioning filter (if any) and the fields that you were joining but with null values if no resulting record was found when joining between the fk and pk.

Hope this helps, cheers.

Answer 3

yes just use the predefined sql stored procedures.
OR open query analyzer and run this code (which will return databases in sql):
select * from sysdatabases

OR the code given below
use [db name] exec sp_tables default,default,default,'''table name'''"

you should put " " for table name as above.