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]

Re: sql dinamic

raulgz (ragaza@ozu.es) writes:[color=blue]
> 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 ?[/color]

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(rke yid),
pk_col = col_name (rkeyid, rkey),
keyno,
fkname = object_name(con stid)
FROM sysforeignkeys
WHERE fkeyid = object_id('Orde rs')
ORDER BY parent_table, fkname, keyno

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Les Matheson]

Re: sql dinamic

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.Cust ID
JOIN Orders O ON O.AcctID=A.Acct ID
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

ragaza@ozu.es (raulgz) wrote in message news:<9b551742. 0403011016.f612 247@posting.goo gle.com>...[color=blue]
> 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.[/color]

[raul garcia zamarra]

Re: sql dinamic



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]

Re: sql dinamic



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(rke yid),
pk_col = col_name (rkeyid, rkey),
keyno,
fkname = object_name(con stid)
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!