Link to home
Start Free TrialLog in
Avatar of tommym121
tommym121Flag for Canada

asked on

Searching data through all the tables in SQL Server

I was given a database with 100s of tables with no data schema and description.
I am trying to find out the lookup values for some of the field.

For example, I have a consumer table with a field called  'Title',  it has value of an int.   There should be a corresponding table that will give me the textual value of the title.   Is there any good tool that can help me to find out such lookup table.  

Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

can you not import the database schema into a modelling tool and see if any relationships are immediately
apparant...

in any case such a tool would assist in the area of documentation...

(Erwin, Powerdesigner, even Visio)

you could also examine the database DDL/relationships/constaints to see if foreign key relationships exist...

(look in the Information_schema tables//views/relationships etc)

good luck

Avatar of tommym121

ASKER

Lowfatspread,

For example,
How do I find out which table (like a tiitle table) hold the values like Mr, Mrs, Miss, Dr and etc. If in my consumer table has a title using a small integer that is the index to the title table.

Since it is not a constrain, how would you search through every field of all the tables in the database to locate, for example, the keyword  'Mr' in a particular table
You could find a list of all tables.columns that have "text" data in them then you could write dynamic code in a SQL stored proc to query all those tables/columns and check for the string you need - passed in a parameter for instance - but as Lowfatspread said...Good luck with that because it sounds like a

SELECT * from DATABASE where text like '%whatever%'

and this may take forever depending how much data you have - note that the stmnt above will NOT use ANY index even if it exists on a column.
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
lcohan:

When I execute this statement, I get the error below.  Do you know what that mean?

SELECT * from DATABASE where text like '%Mr%'

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'DATABASE'.
That is a piece of pseudo code that does not exists and you will have to mimic that by a SQL stored procedure or function as I said where you would/could pass the string "mr" as parameter.
And BTW...did you think how many extra hits you will get with a query like that because "mr" or other string you will look for could be part of some other words?

Icohan,

Thanks.   I do not think there will be too many field have the exact string "Mr".
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Execution of store procedure

exec GetAllTables 'Mrs.'

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for all your helps.