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?
1条答案
按热度按时间ghhaqwfi1#
As you can see using the Exec Sp_rename command in synapse gives me an error. Rename is not supported in Synapse.
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.
Then you can drop the old table.
The below is the query to check dependencies:
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
Regarding the queries that are giving you empty results. You can try with the below query in Azure Synapse Dedicated Pool.