TRUNGTQ

Think Big, Act Small, Fail Fast and Learn Rapidly

NAVIGATION - SEARCH

SQL Server - Tìm và xóa các đối tượng phụ thuộc vào 1 bảng trong Database

Chào mọi người!

Hôm nay mình ngồi kiểm tra và xóa các bảng không cần thiết trong 2 cái database, mỗi cái có tầm 200 tables, nếu View Dependencies bằng tay thì quá to tay, nghĩ tới đã thấy mệt rồi.

Mình đã search và tìm thấy một cách tương đối ổn, hôm nay chia sẻ mọi người.

1. Mình phân loại các bảng nào cần xóa vào 1 file Excel để quản lý chúng, có một cột đánh dấu là có các stored procedure hay functions nào phục thuộc vào không.

Mình dùng lệnh SQL sau để kiểm tra:

DECLARE @tableName VARCHAR(100) = '[dbo].[YourTableName]'

SELECT 
COALESCE(Referenced_server_name+'.','')+ --possible server name if cross-server
COALESCE(referenced_database_name+'.','')+ --possible database name if cross-database
COALESCE(referenced_schema_name+'.','')+ --likely schema name
COALESCE(referenced_entity_name,'') + --very likely entity name
COALESCE('.'+COL_NAME(referenced_ID,referenced_minor_id),'')AS [referencing],
COALESCE(OBJECT_SCHEMA_NAME(Referencing_ID)+'.','')+ --likely schema name
OBJECT_NAME(Referencing_ID)+ --definite entity name
COALESCE('.'+COL_NAME(referencing_ID,referencing_minor_id),'') AS [referenced]
FROM sys.sql_expression_dependencies
WHERE referenced_id =OBJECT_ID(@tableName)
ORDER BY [referenced]

 

2. Mình xây dựng các câu SQL dạng scripts, chỉ cần F5 là xóa cá stored procedure, functions phụ thuộc.

Mình dùng lệnh SQL sau để liệt kê các đối tượng phụ thuộc cần xóa:

DECLARE @tableName VARCHAR(100) = '[dbo].[YourTableName]'

DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

WITH cte AS(
SELECT
COALESCE(Referenced_server_name+'.','')+ --possible server name if cross-server
COALESCE(referenced_database_name+'.','')+ --possible database name if cross-database
COALESCE(referenced_schema_name+'.','')+ --likely schema name
COALESCE(referenced_entity_name,'') + --very likely entity name
COALESCE('.'+COL_NAME(referenced_ID,referenced_minor_id),'')AS [referencing],
COALESCE(OBJECT_SCHEMA_NAME(Referencing_ID)+'.','')+ --likely schema name
OBJECT_NAME(Referencing_ID)+ --definite entity name
COALESCE('.'+COL_NAME(referencing_ID,referencing_minor_id),'') AS [referenced]
FROM sys.sql_expression_dependencies
WHERE referenced_id =OBJECT_ID(@tableName)
)

SELECT @SQL =@SQL + 'DROP PROCEDURE ' + referenced + ';' + @crlf
FROM cte

PRINT @SQL

 

3. Sau đó sinh script xóa Table từ File Excel, F5 là xong

4. Chúc mọi người may mắn nếu gặp phải công việc tương tự.