Is there anything that would give intellisense like awareness to data relationships in an Sql Server Database
-
I'm trying to make sure that we've removed all references to several deprecated columns from a large number of stored procedures. Unfortunately one of the columns being nuked is an FK to a widely used table; meaning that text searches like the below sproc and up returning a lot of false positives for instances of `OtherTable.TableNameId` when all I'm interested in are references to `TableIwantToRemoveTheColumnFrom.TableNameId`.
select OBJECT_NAME(object_id), [definition]
from sys.sql_modules
where [definition] like '%TableNameId%'That culls the list of files I'd need to manually examine from >100 to several dozen. it is at least a bit of a start; but is still a lot of painful manual review, and far worse than the application code/ Intellisense has let me confirm that the only remaining references to the old value there are the data migration functions and a few spots where existing values need to be nulled out so FK constraints don't blow up when doing deletes. (All of which is safe to remove from the C# once the final DB nuking is done.)
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
-
I'm trying to make sure that we've removed all references to several deprecated columns from a large number of stored procedures. Unfortunately one of the columns being nuked is an FK to a widely used table; meaning that text searches like the below sproc and up returning a lot of false positives for instances of `OtherTable.TableNameId` when all I'm interested in are references to `TableIwantToRemoveTheColumnFrom.TableNameId`.
select OBJECT_NAME(object_id), [definition]
from sys.sql_modules
where [definition] like '%TableNameId%'That culls the list of files I'd need to manually examine from >100 to several dozen. it is at least a bit of a start; but is still a lot of painful manual review, and far worse than the application code/ Intellisense has let me confirm that the only remaining references to the old value there are the data migration functions and a few spots where existing values need to be nulled out so FK constraints don't blow up when doing deletes. (All of which is safe to remove from the C# once the final DB nuking is done.)
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
Abandon all hope, ye who code here...
Software Zen:
delete this; -
I'm trying to make sure that we've removed all references to several deprecated columns from a large number of stored procedures. Unfortunately one of the columns being nuked is an FK to a widely used table; meaning that text searches like the below sproc and up returning a lot of false positives for instances of `OtherTable.TableNameId` when all I'm interested in are references to `TableIwantToRemoveTheColumnFrom.TableNameId`.
select OBJECT_NAME(object_id), [definition]
from sys.sql_modules
where [definition] like '%TableNameId%'That culls the list of files I'd need to manually examine from >100 to several dozen. it is at least a bit of a start; but is still a lot of painful manual review, and far worse than the application code/ Intellisense has let me confirm that the only remaining references to the old value there are the data migration functions and a few spots where existing values need to be nulled out so FK constraints don't blow up when doing deletes. (All of which is safe to remove from the C# once the final DB nuking is done.)
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
-
I'm trying to make sure that we've removed all references to several deprecated columns from a large number of stored procedures. Unfortunately one of the columns being nuked is an FK to a widely used table; meaning that text searches like the below sproc and up returning a lot of false positives for instances of `OtherTable.TableNameId` when all I'm interested in are references to `TableIwantToRemoveTheColumnFrom.TableNameId`.
select OBJECT_NAME(object_id), [definition]
from sys.sql_modules
where [definition] like '%TableNameId%'That culls the list of files I'd need to manually examine from >100 to several dozen. it is at least a bit of a start; but is still a lot of painful manual review, and far worse than the application code/ Intellisense has let me confirm that the only remaining references to the old value there are the data migration functions and a few spots where existing values need to be nulled out so FK constraints don't blow up when doing deletes. (All of which is safe to remove from the C# once the final DB nuking is done.)
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
Maybe this can be of some help? View the Dependencies of a Table - SQL Server | Microsoft Docs[^]
-
Maybe this can be of some help? View the Dependencies of a Table - SQL Server | Microsoft Docs[^]
unfortunately not. I suspect whatever is used to fill the result dialog does have what I'm looking for; but as is all it tells me is that most tables in the system have a dependency on the users table (created/updated by fields so they have userId FKs); but not what column the dependency is on so I could filter it by the one I want to get rid of.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
-
unfortunately not. I suspect whatever is used to fill the result dialog does have what I'm looking for; but as is all it tells me is that most tables in the system have a dependency on the users table (created/updated by fields so they have userId FKs); but not what column the dependency is on so I could filter it by the one I want to get rid of.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
You need to add a few joins to make it useful. Here's something I found on SO and modified for my own use:
USE MyDatabase
DECLARE @SchemaName nvarchar(128) = 'dbo'
DECLARE @TableName nvarchar(128) = 'TableWithColumnToRemove'
DECLARE @ColumnName nvarchar(128) = 'ColumnToRemove'
;SELECT SCHEMA_NAME(o1.schema_id) + '.' + o1.name ReferencingObject
,SCHEMA_NAME(o2.schema_id) + '.' + ed.referenced_entity_name ReferencedObject
,c.name ColumnName
,o2.type ReferencedObjectType
,o1.type ReferencingObjectType
FROM sys.sql_expression_dependencies ed
JOIN sys.objects o1 ON ed.referencing_id = o1.object_id
JOIN sys.objects o2 ON ed.referenced_id = o2.object_id
JOIN sys.sql_dependencies d ON ed.referencing_id = d.object_id
AND d.referenced_major_id = ed.referenced_id
JOIN sys.columns c ON c.object_id = ed.referenced_id
AND d.referenced_minor_id = c.column_id
WHERE 1=1
AND SCHEMA_NAME(o2.schema_id) = @SchemaName
AND o2.name = @TableName
AND c.name = @ColumnName
ORDER BY ReferencedObject
,c.column_id;Wrong is evil and must be defeated. - Jeff Ello
-
You need to add a few joins to make it useful. Here's something I found on SO and modified for my own use:
USE MyDatabase
DECLARE @SchemaName nvarchar(128) = 'dbo'
DECLARE @TableName nvarchar(128) = 'TableWithColumnToRemove'
DECLARE @ColumnName nvarchar(128) = 'ColumnToRemove'
;SELECT SCHEMA_NAME(o1.schema_id) + '.' + o1.name ReferencingObject
,SCHEMA_NAME(o2.schema_id) + '.' + ed.referenced_entity_name ReferencedObject
,c.name ColumnName
,o2.type ReferencedObjectType
,o1.type ReferencingObjectType
FROM sys.sql_expression_dependencies ed
JOIN sys.objects o1 ON ed.referencing_id = o1.object_id
JOIN sys.objects o2 ON ed.referenced_id = o2.object_id
JOIN sys.sql_dependencies d ON ed.referencing_id = d.object_id
AND d.referenced_major_id = ed.referenced_id
JOIN sys.columns c ON c.object_id = ed.referenced_id
AND d.referenced_minor_id = c.column_id
WHERE 1=1
AND SCHEMA_NAME(o2.schema_id) = @SchemaName
AND o2.name = @TableName
AND c.name = @ColumnName
ORDER BY ReferencedObject
,c.column_id;Wrong is evil and must be defeated. - Jeff Ello
Thank you, that looks like it does exactly what I was looking for. :cool:
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius
-
I'm trying to make sure that we've removed all references to several deprecated columns from a large number of stored procedures. Unfortunately one of the columns being nuked is an FK to a widely used table; meaning that text searches like the below sproc and up returning a lot of false positives for instances of `OtherTable.TableNameId` when all I'm interested in are references to `TableIwantToRemoveTheColumnFrom.TableNameId`.
select OBJECT_NAME(object_id), [definition]
from sys.sql_modules
where [definition] like '%TableNameId%'That culls the list of files I'd need to manually examine from >100 to several dozen. it is at least a bit of a start; but is still a lot of painful manual review, and far worse than the application code/ Intellisense has let me confirm that the only remaining references to the old value there are the data migration functions and a few spots where existing values need to be nulled out so FK constraints don't blow up when doing deletes. (All of which is safe to remove from the C# once the final DB nuking is done.)
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason? Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful? --Zachris Topelius