Tìm kiếm tên các bảng được sử dụng trong stored procedure (edit)
Tìm tên các bảng trong Stored Procedure: https://manhng.com/blog/tables-in-stored-procedure/
Tìm quan hệ của một bảng với các bảng khác: https://manhng.com/blog/relationship/
Cách thực hiện
https://www.devtrends.co.uk/blog/handling-errors-in-asp.net-core-web-api
;WITH stored_procedures AS ( SELECT o.name AS proc_name, oo.name AS table_name, ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row FROM sysdepends d INNER JOIN sysobjects o ON o.id=d.id INNER JOIN sysobjects oo ON oo.id=d.depid WHERE o.xtype = 'P') SELECT proc_name, table_name FROM stored_procedures WHERE row = 1 ORDER BY proc_name,table_name
Cách khác
;WITH src AS
(
SELECT [procedure] = QUOTENAME(s.name) + N'.' + QUOTENAME(o.name),
ref =
COALESCE(QUOTENAME(d.referenced_server_name) + N'.', N'')
+ COALESCE(QUOTENAME(d.referenced_database_name) + N'.', N'')
+ QUOTENAME(d.referenced_schema_name) + N'.'
+ QUOTENAME(d.referenced_entity_name)
FROM sys.sql_expression_dependencies AS d
INNER JOIN sys.objects AS o
ON d.referencing_id = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = N'P'
)
SELECT [procedure],ref
FROM src
GROUP BY [procedure],ref;
Hiển thị quan hệ của một bảng với các bảng khác
SELECT f.name AS foreign_key_name,
OBJECT_NAME(f.parent_object_id) AS table_name,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name,
OBJECT_NAME (f.referenced_object_id) AS referenced_object,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name,
is_disabled,
delete_referential_action_desc,
update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
WHERE f.parent_object_id = OBJECT_ID('HumanResources.Employee');
Cách khác xem các khóa ngoại và khóa chính của một bảng
https://stackoverflow.com/questions/7335141/sql-server-table-relationships
DECLARE @TableName varchar(250)
SET @TableName='YourTableName'
;WITH AllInfo AS
(
SELECT
tc.TABLE_NAME,tc.CONSTRAINT_NAME, ccu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE tc.TABLE_NAME =@TableName
UNION
SELECT
FK.TABLE_NAME,C.CONSTRAINT_NAME,CU.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME=@TableName
)
SELECT DISTINCT
t1.TABLE_NAME,t1.CONSTRAINT_NAME
,STUFF(
(SELECT
', ' + t2.COLUMN_NAME
FROM AllInfo t2
WHERE t1.TABLE_NAME=t2.TABLE_NAME AND t1.CONSTRAINT_NAME=t2.CONSTRAINT_NAME
ORDER BY t2.COLUMN_NAME
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,2, ''
) AS ColumnNames
FROM AllInfo t1
ORDER BY 1,2,3
Cách khác xem quan hệ của một bảng (SIMPLE)
SELECT fk.name 'FK Name', tp.name 'Parent table', cp.name, cp.column_id, tr.name 'Refrenced table', cr.name, cr.column_id FROM sys.foreign_keys fk INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id INNER JOIN sys.tables tr ON fk.referenced_object_id = tr.object_id INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id INNER JOIN sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id ORDER BY tp.name, cp.column_id
