April 7, 2008 at 1:11 pm
I need to populate a field in the metadata_fields table that lists the lookup table associated with a field (if one exists). So, the metadata_fields table has:
Table_id – FK to link to another table (metadata_table)
Lookup_table_id – Will contain the name of the LUT if one is associated to the field
Field_name – Field name of each field for the table in Table_Name field (i.e. RESPONSE_ID)
Field_alias – Field names converted to title case (i.e. Response ID)
Field_type – Field type (int, varchar, etc)
Table_name – created from all of the tables in the DB
Field_name may be a FK that relates to the LUT. Is there an automated (scripted) way to determine if the field ties to another field in another table? Suggestions?
Thanks in advance…
Brad
April 7, 2008 at 1:14 pm
have a look at : "Generate Create FK-indexes" http://www.sqlservercentral.com/scripts/Indexing/61391/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 7, 2008 at 2:25 pm
Take a look at sys.foreign_keys. It's a system view, and it probably has what you're looking for.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2008 at 2:39 pm
When I look at that table (actually have to extract it from the dbo, since I can't see the dbo table (permissions)) I get this:
(column names = constid, fkeyid, rkeyid, fkey, rkey, keyno)
2786316621433467002015346244211
7586333743863223965578478211
91863394438632232015346244111
1287195113271916996719397311
1315315529953143867531324211
Not a lot of useful info yet, so how does it turn into me knowing the tables and relationships to put them into a column?
Brad
April 7, 2008 at 11:49 pm
that's because you get so see object_ids in stead of the actual names ...
Read the link I provided and you'll be able to generate a nice script after some modification.
it is based on sp_keys and sp_fkeys.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 8, 2008 at 9:04 am
I did in fact read the link and also ran the script in my DB, so I know that I can use it to get the info I need with some modification. But, it never hurts to look at the other reply (or replies) and see what other people think. 😉
Thanks for your input...hopefully today I can put together the solution. I've found (just by looking at the DB diagram) there some fields have a one-to-many relationship with look-up tables, so I'm not sure how I will need to alter things to handle that.
Again, thanks!
Brad
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply