SQL et astuces

select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'>-' as rel,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
substring(column_names, 1, len(column_names)-1) as [fk_columns],
fk.name as fk_constraint_name
from sys.foreign_keys fk
inner join sys.tables fk_tab
on fk_tab.object_id = fk.parent_object_id
inner join sys.tables pk_tab
on pk_tab.object_id = fk.referenced_object_id
cross apply (select col.[name] + ', '
from sys.foreign_key_columns fk_c
inner join sys.columns col
on fk_c.parent_object_id = col.object_id
and fk_c.parent_column_id = col.column_id
where fk_c.parent_object_id = fk_tab.object_id
and fk_c.constraint_object_id = fk.object_id
order by col.column_id
for xml path ('') ) D (column_names)
order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name

DROP TABLE IF EXISTS tracks;
DROP TABLE IF EXISTS artists;

CREATE TABLE artists (
id INTEGER PRIMARY KEY,
name TEXT
);

CREATE TABLE tracks (
traid INTEGER,
title TEXT,
artist INTEGER,
FOREIGN KEY(artist) REFERENCES artists(id)
);

INSERT INTO artists VALUES (1, 'Tom Chapin');
INSERT INTO artists VALUES (2, 'Harry Chapin');

/* Tom’s songs */
INSERT INTO tracks VALUES (1, 'Great Big Words', 1);
INSERT INTO tracks VALUES (1, 'This Pretty Planet', 1);

/* Harry’s songs */
INSERT INTO tracks VALUES (2, 'Cat’s in the Cradle', 2);

/* It won’t let us do this, because 3 isn’t an artist id:
INSERT INTO tracks VALUES (2, 'Cat’s in the Cradle', 3);
*/

SELECT * FROM artists;
SELECT * FROM tracks;
SELECT T.title, A.name FROM tracks T, artists A WHERE T.artist=A.id;
SELECT T.title, A.name FROM tracks T INNER JOIN artists A ON T.artist=A.id;

DROP TABLE IF EXISTS tracks;
DROP TABLE IF EXISTS artists;
DROP TABLE IF EXISTS pays;

CREATE TABLE pays (
id INT NOT NULL AUTO_INCREMENT,
pays TEXT,
PRIMARY KEY (id)
);

CREATE TABLE artists (
id INTEGER PRIMARY KEY,
name TEXT,
paysid INT,
FOREIGN KEY(paysid) REFERENCES pays(id)
);

CREATE TABLE tracks (
traid INTEGER,
title TEXT,
artist INTEGER,
FOREIGN KEY(artist) REFERENCES artists(id)
);

INSERT INTO pays VALUES (NULL, 'France');
INSERT INTO pays VALUES (NULL, 'USA');

INSERT INTO artists VALUES (1, 'Tom Chapin', 1);
INSERT INTO artists VALUES (2, 'Harry Chapin', 2);

/* Tom’s songs */
INSERT INTO tracks VALUES (1, 'Great Big Words', 1);
INSERT INTO tracks VALUES (1, 'This Pretty Planet', 1);

/* Harry’s songs */
INSERT INTO tracks VALUES (2, 'Cat’s in the Cradle', 2);

/* It won’t let us do this, because 3 isn’t an artist id:
INSERT INTO tracks VALUES (2, 'Cat’s in the Cradle', 3);
*/

SELECT * FROM artists;
SELECT * FROM tracks;
SELECT T.title, A.name, P.pays FROM tracks T, artists A, pays P WHERE T.artist=A.id AND A.paysid=P.id;
SELECT T.title, A.name, P.pays FROM tracks T INNER JOIN artists A INNER JOIN pays P ON T.artist=A.id AND A.paysid=P.id;
SELECT A.name, P.pays FROM artists A left JOIN pays P ON A.paysid=P.id;