sql dinamic

[raulgz]

hi

I need generate dinamic sql:
I need a select sentence with all fields of one table 't1'
and all fields of tables which 't1' have foreings keys 't1 ( reflexive ) ,t2,t3,...'

Now, l have de select part, de select from...
but I don`t know how create de join clauses ?

Some idea about this ?

Thank very much.
RaulGZ.

[Erland Sommarskog]

raulgz (ra****@ozu.es) writes:

I need generate dinamic sql: I need a select sentence with all fields of
one table 't1' and all fields of tables which 't1' have foreings keys
't1 ( reflexive ) ,t2,t3,...'

Now, l have de select part, de select from...
but I don`t know how create de join clauses ?

Well, the JOIN clause might depend on the logic you want to implement,
so without information, this is a little difficult to guess.

But assuming that you want join FKs to PKs, the key table for you to use
is sysforiegn keys. Here is a an example on how retrieving the data:

SELECT fkcol = col_name(fkeyid, fkey),
parent_table = object_name(rkeyid),
pk_col = col_name (rkeyid, rkey),
keyno,
fkname = object_name(constid)
FROM sysforeignkeys
WHERE fkeyid = object_id('Orders')
ORDER BY parent_table, fkname, keyno

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Les Matheson]

Raul,

I'm not sure what you're asking. If you simply want to join
multiple tables, here's an example:

SELECT A.*,C.*,O.* FROM
Accounts A JOIN Customers C ON A.CustID=C.CustID
JOIN Orders O ON O.AcctID=A.AcctID
WHERE C.Name LIKE 'SMITH%'

If that's not the answer you need, please try to clarify the
problem...

Thanks,
Les Matheson
Integral Concepts, Inc,
http://www.ivsds.com

ra****@ozu.es (raulgz) wrote in message news:<9b*************************@posting.google.c om>...

hi

I need generate dinamic sql:
I need a select sentence with all fields of one table 't1'
and all fields of tables which 't1' have foreings keys 't1 ( reflexive ) ,t2,t3,...'

Now, l have de select part, de select from...
but I don`t know how create de join clauses ?

Some idea about this ?

Thank very much.
RaulGZ.

[raul garcia zamarra]

Hi Erland:

Thank very much for your solution.
You really save me. This is what I was looking for.

Thanks again,

R.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[raul garcia zamarra]

Hi Les:

Thank you very much, but i need work with sql server
catalog tables. This is a good solution:

SELECT fkcol = col_name(fkeyid, fkey),
parent_table = object_name(rkeyid),
pk_col = col_name (rkeyid, rkey),
keyno,
fkname = object_name(constid)
FROM sysforeignkeys
WHERE fkeyid = object_id('RTCM.PERMISO')
ORDER BY parent_table, fkname, keyno;

Thank again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!