@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

Integration Tests (Microsoft.VisualStudio.TestTools.UnitTesting + Dapper + Stored Procedure + XML)

November 13, 2019 13:17

Integration Tests (Microsoft.VisualStudio.TestTools.UnitTesting + Dapper + Stored Procedure + XML) (edit)

https://www.teamscs.com/2018/10/simple-c-model-to-sql-server-stored-procedure-mapping-using-xml/

Entity Framework with MySql Stored Procedure

January 26, 2018 17:01

Entity Framework Code First and MySQL Stored Procedure

EF code-first approach expects that there is no logic in the database. That means no stored procedures and no database views. Because of that code-first approach doesn't provide any mechanism to generate such constructs automatically for you. How could it do that if it means generating logic?

You must create them yourselves in custom database initializer by manual executing creation scripts. I don't think that this custom SQL constructs can be handled by SQL migrations.

https://dev.mysql.com/

https://msdn.microsoft.com/en-us/library/dn468673(v=vs.113).aspx

https://dzone.com/articles/how-to-use-stored-procedure-with-entity-framework

http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

http://www.entityframeworktutorial.net/EntityFramework4.3/execute-stored-procedure-using-dbcontext.aspx

http://www.dotnetodyssey.com/2015/03/12/calling-stored-procedure-from-entity-framework-6-code-first/

https://sunali.com/2012/10/10/querying-mysql-using-entity-framework-and-mysql-stored-procedures/

https://dotnetthoughts.net/how-to-execute-a-stored-procedure-with-entity-framework-code-first/

https://www.mikesdotnetting.com/article/299/entity-framework-code-first-and-stored-procedures

https://www.red-gate.com/simple-talk/dotnet/net-framework/using-entity-framework-with-an-existing-database-data-access/

Categories

Recent posts