I am using powershell SMO DependencyWalker and Scripter to find the all dependencies of a table in a SQL Server database and then generating scripts to CREATE and DROP the table and those dependencies. DependencyWalker is successfully finding most of the dependencies, but does not find certain stored procedures that I believe it should.
For example, given these table definitions:
CREATE TABLE MyTable(
[UniqueID] [uniqueidentifier] NOT NULL
)
CREATE TABLE MyOtherTable(
[UniqueID] [uniqueidentifier] NOT NULL
)
ALTER TABLE [dbo].[MyTable]
WITH CHECK ADD CONSTRAINT [FK_MyTable_MyOtherTable] FOREIGN KEY([UniqueID])
REFERENCES [dbo].[MyOtherTable] ([UniqueID])
And the following stored procedures that reference the table:
CREATE PROCEDURE [dbo].[MyTableResumeConstraintChecking]
AS
ALTER TABLE dbo.MyTable CHECK CONSTRAINT FK_MyTable_MyOtherTable
RETURN
GO
CREATE PROCEDURE [dbo].[MyTableSuspendConstraintChecking]
AS
ALTER TABLE dbo.MyTable NOCHECK CONSTRAINT FK_MyTable_MyOtherTable
RETURN
GO
I would expect that the following powershell script would find the stored procedures as dependencies:
$SmoServer = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $server
$dbObject = $SmoServer.Databases["MyDatabase"]
$tables = $dbObject.Tables | where { !$_.IsSystemObject }
$urns = $tables | foreach { $_.Urn }
$walker = New-Object 'Microsoft.SqlServer.Management.SMO.DependencyWalker' $SmoServer
$tree = $walker.DiscoverDependencies($urns, $false)
$depCollection = $walker.WalkDependencies($tree)
$dependencies = $depCollection | foreach { $_.Urn }
This will find all other stored procedures that use MyTable (ones with typical INSERT, UPDATE, etc. calls). However, it will NOT find these *ConstraintChecking procedures that reference the table only via ALTER TABLE.
It should be noted that SSMS does not recognize these procedures as a dependency of the table either (when using View Dependencies).
I have tried to use other mechanisms for finding dependencies such as sp_depends and sys.dm_sql_referenced_entities, but those behave the same as DependencyWalker in this situation.
Using SMO in powershell and given a table URN, how do I find the URN of dependencies such as this?