@manhng

Welcome to my blog!

Tìm kiếm tên các bảng được sử dụng trong stored procedure

November 23, 2019 00:37

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

Categories