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

https://dba.stackexchange.com/questions/194878/get-the-list-of-tables-used-in-a-stored-procedure-for-a-database

;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

https://docs.microsoft.com/vi-vn/sql/relational-databases/tables/view-foreign-key-properties?view=sql-server-2017

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)

https://stackoverflow.com/questions/8094156/know-relationships-between-all-the-tables-of-database-in-sql-server

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