Finding dependent columns in SQL Azure

When fiddling with database relations in an Azure database you quickly notice you are on your own in terms of tooling in a way that you’re not used to from Microsoft. Metadata however is available so you can work around these things relatively easily.

So: when you are dropping and recreating tables as you design them – here’s a quick snippet to see which columns are depending on the particular table you are editing now:

create procedure show_referencing_relations 
@schemaname sysname, 
@tablename sysname 
as 
select 
 [DropSQL] = 'ALTER TABLE [' + @schemaname + '].[' 
 + ptbl.name + '] DROP CONSTRAINT [' + fks.name + '];',
  [AddSQL] = 'ALTER TABLE [' + @schemaname + '].[' 
 + ptbl.name + '] ADD CONSTRAINT [' + fks.name + 
  '] FOREIGN KEY (' + pcol.name + ') REFERENCES ['
 + @schemaname + '].[' + @tablename + '] (' + col.name + ')',        [ConstraintName] = fks.name,
[Source Column Name] = col.name,
[Parent Table Name] = ptbl.name,
[Parent Column Name] = pcol.name 
from sys.foreign_keys fks 
 inner join sys.all_objects ao on fks.referenced_object_id = ao.object_id 
 inner join sys.foreign_key_columns fkc on 
fks.object_id = fkc.constraint_object_id
 inner join sys.all_columns col on 
 col.object_id = ao.object_id and 
 fkc.referenced_column_id = col.column_id
 inner join sys.all_objects ptbl on fkc.parent_object_id = ptbl.object_id
 inner join sys.all_columns pcol on 
 fkc.parent_column_id = pcol.column_id and 
 pcol.object_id = fkc.parent_object_id
 --inner join sys.all_objects pcol on fkc.constraint_column_id = pcol.object_id
 where ao.name = @tablename

Run it with schema and table names, and you get some SQL to use before and after your planned change. First one to remove the constraints and the second one to restore them. The results of the stored procedure show the name of the constraint, the column in your table that is being referenced plus the parent table and column involved in the relationship.

Then you can quickly write code to drop and recreate these relations.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s