SQL Server Unable to rename a table in Azure Synapse. It throws 'Object cannot be renamed because the object participates in enforced dependencies.' error

xqnpmsa8  于 2023-08-02  发布在  其他
关注(0)|答案(1)|浏览(125)

I'm trying to rename a table in Azure Synapse (Dedicated SQL Pool). But when I run the below SQL command, it throws an error saying:
Object '[dbo].[<table_name>]' cannot be renamed because the object participates in enforced dependencies.

I tried to find out the dependencies present on this object but I could find nothing. Many pages on the internet suggested to just use the 'View Dependencies' option when you right-click on a table in SQL Server Management Studio (SSMS). But when I tried that, turns out 'View Dependencies' option is not available in my SSMS.

So far I tried the below queries but they either failed or returned nothing.

select o.name as ObjName, r.name as ReferencedObj
from sys.sql_dependencies d
join sys.objects o on o.object_id=d.object_id
join sys.objects r on r.object_id=d.referenced_major_id
where d.class=1
AND r.name = @YourObjectName

Fails with:

Catalog view 'sql_dependencies' is not supported in this version.`

(Maybe this is only valid for SQL Server on-premise. I'm using Azure Synapse.)

SELECT * FROM sys.sql_expression_dependencies  WHERE referencing_id = OBJECT_ID(N'dbo.<table_name>')

-> returns empty

SELECT OBJECT_NAME(d.referencing_id) AS referencing_name, o.type_desc referencing_object_type,
 d.referencing_minor_id AS referencing_column_id, 
 d.referenced_entity_name, d.referenced_minor_id AS referenced_column_id, 
 cc.name as referenced_column_name
FROM sys.sql_expression_dependencies d 
 JOIN sys.all_columns cc 
  ON d.referenced_minor_id = cc.column_id AND d.referenced_id = cc.[object_id]
 JOIN sys.objects o 
  ON d.referencing_id = o.[object_id]
WHERE  d.is_schema_bound_reference = 1 and o.object_id = '12345678'

-> also returns empty

I'm really stuck here, unable to change the name of my table.

Can someone please help me on how to solve this and successfully rename the table?

ghhaqwfi

ghhaqwfi1#

As you can see using the Exec Sp_rename command in synapse gives me an error. Rename is not supported in Synapse.

EXEC sp_rename 'dbo.new_Customers',
'NewCustomers','OBJECT';

Error:

however you can try the below approach For example, I have a customer's tables and I am using the below Method to rename the table.

select * into New_customers from customers

Then you can drop the old table.

Drop table customers

The below is the query to check dependencies:

DECLARE @customer_ObjectName NVARCHAR(128) = 'customers';
SELECT
referencing_obj.name AS ReferencingObj,
referenced_obj.name AS ReferencedObj
FROM
sys.sql_expression_dependencies AS d
JOIN
sys.objects AS referencing_obj ON referencing_obj.object_id = d.referencing_id
JOIN
sys.objects AS referenced_obj ON referenced_obj.object_id = d.referenced_id
WHERE
d.referenced_entity_name = @customer_ObjectName;

Also as you have mentioned, while trying to execute the query for checking dependencies you are getting the error Catalog view ‘sql_dependencies’ is not supported in this version.`

You can try to use the below query to get the same

You can try the below query

SELECT
referencing_schema_name = SCHEMA_NAME(o.schema_id),
referencing_entity_name = o.name,
referencing_class_desc = o.type_desc
FROM
sys.sql_expression_dependencies AS d
JOIN
sys.objects AS o ON d.referencing_id = o.object_id
WHERE
d.referenced_entity_name = 'Customers'


Regarding the queries that are giving you empty results. You can try with the below query in Azure Synapse Dedicated Pool.

SELECT
    referencing_name = OBJECT_NAME(referencing_id),
    referencing_object_type = o.type_desc,
    referencing_column_id = referencing_minor_id,
    referenced_entity_name = referenced_entity_name,
    referenced_column_id = referenced_minor_id,
    referenced_column_name = COL_NAME(referenced_id, referenced_minor_id)
FROM
    sys.sql_expression_dependencies
JOIN
    sys.objects o ON referencing_id = o.object_id
WHERE
    is_schema_bound_reference = 0
    AND referenced_id = OBJECT_ID('dbo.Customers')

相关问题