SQL Server: Jak uzyskać odniesienie do klucza obcego ze schematu informacyjnego?
W SQL Server, Jak mogę pobrać nazwę tabeli + kolumny z klucza obcego?
Uwaga: Nie tabela / kolumna, w której znajduje się klucz, ale klucz, do którego się odnosi.
Przykład:
Gdy klucz [FA_MDT_ID]
w tabeli [T_ALV_Ref_FilterDisplay]
.
odnosi się do [T_AP_Ref_Customer].[MDT_ID]
Takie jak podczas tworzenia ograniczenia jak to:
ALTER TABLE [dbo].[T_ALV_Ref_FilterDisplay] WITH CHECK ADD CONSTRAINT [FK_T_ALV_Ref_FilterDisplay_T_AP_Ref_Customer] FOREIGN KEY([FA_MDT_ID])
REFERENCES [dbo].[T_AP_Ref_Customer] ([MDT_ID])
GO
I need to get [T_AP_Ref_Customer].[MDT_ID]
gdy podano {[5] } jako wejście
3 answers
Nieważne, to jest prawidłowa odpowiedź:
http://msdn.microsoft.com/en-us/library/aa175805(SQL. 80). aspx
SELECT
KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME
,KCU1.TABLE_NAME AS FK_TABLE_NAME
,KCU1.COLUMN_NAME AS FK_COLUMN_NAME
,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION
,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME
,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME
,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-21 19:58:38
Jeśli możesz żyć z użyciem widoków katalogu schematu SQL Server, to zapytanie zwróci to, czego szukasz:
SELECT
fk.name,
OBJECT_NAME(fk.parent_object_id) 'Parent table',
c1.name 'Parent column',
OBJECT_NAME(fk.referenced_object_id) 'Referenced table',
c2.name 'Referenced column'
FROM
sys.foreign_keys fk
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
INNER JOIN
sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
Nie wiem , jak - jeśli w ogóle-można uzyskać te same informacje z widoków INFORMATION_SCHEMA....
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-10-12 05:07:26
Chciałem wersję a, która pozwoli mi znaleźć wszystkie kolumny " Key " i " ID " posiadające/brakujące ograniczenie. Więc chciałem wszystkie kolumny w porównaniu do listy wszystkich PK lub FK lub Null, oto moje zapytanie. Mam nadzieję, że to pomoże komuś innemu!
SELECT
c.table_schema
,c.table_name
,c.column_name
,KeyConstraints.constraint_type
,KeyConstraints.constraint_schema
,KeyConstraints.constraint_name
,KeyConstraints.referenced_table_schema
,KeyConstraints.referenced_table_name
,KeyConstraints.referenced_column_name
,KeyConstraints.update_rule
,KeyConstraints.delete_rule
FROM information_schema.columns AS c
LEFT JOIN
(
SELECT
FK.table_schema AS TABLE_SCHEMA
,FK.table_name
,CU.column_name
,FK.constraint_type
,c.constraint_schema
,C.constraint_name
,PK.table_schema AS REFERENCED_TABLE_SCHEMA
,PK.table_name AS REFERENCED_TABLE_NAME
,CCU.column_name AS REFERENCED_COLUMN_NAME
,C.update_rule
,C.delete_rule
FROM information_schema.referential_constraints AS C
INNER JOIN information_schema.table_constraints AS FK
ON C.constraint_name = FK.constraint_name
INNER JOIN information_schema.table_constraints AS PK
ON C.unique_constraint_name = PK.constraint_name
INNER JOIN information_schema.key_column_usage AS CU
ON C.constraint_name = CU.constraint_name
INNER JOIN information_schema.constraint_column_usage AS CCU
ON PK.constraint_name = CCU.constraint_name
WHERE ( FK.constraint_type = 'FOREIGN KEY' )
UNION
SELECT
ccu.table_schema
,ccu.table_name
,ccu.column_name
,tc.constraint_type
,ccu.constraint_schema
,ccu.constraint_name
,NULL
,NULL
,NULL
,NULL
,NULL
FROM information_schema.constraint_column_usage ccu
INNER JOIN information_schema.table_constraints tc
ON ccu.table_schema = tc.table_schema
AND ccu.table_name = tc.table_name
WHERE tc.constraint_type = 'PRIMARY KEY'
) AS KeyConstraints
ON c.table_schema = KeyConstraints.table_schema
AND c.table_name = KeyConstraints.table_name
AND c.column_name = KeyConstraints.column_name
WHERE c.column_name LIKE '%ID' OR c.column_name LIKE '%Key'
ORDER BY c.table_schema
,c.table_name
,c.column_name
;
Formatowanie dzięki uprzejmości: http://www.dpriver.com/pp/sqlformat.htm
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-21 19:51:49