I'm currently trying to create a function to parse error messages to make them more helpful for the end user. Currently I'm working with SQLServer and VB.NET.
Right now I'm raising error 547 which looks like this:
DELETE statement conflicted with COLUMN REFERENCE
constraint Constraint Name. The conflict occurred
in database 'Database Name', table 'Table Name',
column 'Column Name'.
The statement that raises it is
DELETE FROM parentTable WHERE primaryKey = 5
I'm able to pull every piece of information I need from the error message except the name of the parentTable. I've already determined that the SqlException doesn't store the statement that caused the exception, and as far as I can tell it doesn't store information about the name of the parent table, only the table that is trying to reference it.
Is there an easy way to get the name of the parentTable?
Here's a routine I developed a few years ago to query ad hoc parent/child relationships for a given table:
---------------------------------------------------------------------
-- Name: FKRelationships
-- Purpose: Map all parent/child relationships to the selected table
-- Date: 1/19/2009
-- Author: John W. Dewey
-- Instructions: 1) Connect this script to the database containing
-- the table you want to analyze
-- 2) Set the schema name and table name variables
-- Initializations section below. Be sure the table
-- name variable does not contain the schema name
-- 3) You will get two result sets; the first will show
-- the relational parent tables to your selected table, and
-- the second will show the relational children to your
-- selected table.
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Declarations
declare @TableName nvarchar(128)
declare @SchemaName nvarchar(128)
declare @ParentTableSchema nvarchar(128)
declare @ParentTableName nvarchar(128)
declare @FKName nvarchar(128)
declare @Column nvarchar(128)
declare @Table nvarchar(128)
declare @Schema nvarchar(128)
declare @FKColumnString nvarchar(max)
declare @PKColumnString nvarchar(max)
declare @result table (
[Parent Table] nvarchar(128)
, [Child Table] nvarchar(128)
, [Constraint] nvarchar(128)
, [Relationship] nvarchar(max)
)
declare @FKTemp table (
[id] int identity(1,1)
, [Child Schema Name] nvarchar(128)
, [Child Table Name] nvarchar(128)
, [Child FK Name] nvarchar(128)
, [Column Name] nvarchar(128)
)
---------------------------------------------------------------------
-- Initializations
set @SchemaName = 'dbo'
set @TableName = 'MyTable'
---------------------------------------------------------------------
-- Tables referenced by the specified table's FKs
declare c1 cursor for
select a.CONSTRAINT_NAME
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
where b.TABLE_NAME = @TableName
and b.CONSTRAINT_SCHEMA = @SchemaName
open c1
fetch next from c1 into @FKName
while @@fetch_status=0 begin
-- Build a string of each FK's corresponding columns
set @FKColumnString = @SchemaName+'.'+@TableName+'.'
declare c2 cursor for
select a.COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
inner join INFORMATION_SCHEMA.COLUMNS b
on a.TABLE_NAME = b.TABLE_NAME
and a.COLUMN_NAME = b.COLUMN_NAME
where a.CONSTRAINT_NAME=@FKName
and a.TABLE_SCHEMA = @SchemaName
order by b.ORDINAL_POSITION
open c2
fetch next from c2 into @Column
while @@fetch_status=0 begin
set @FKColumnString = @FKColumnString + '+' + @Column
fetch next from c2 into @Column
end
set @FKColumnString = replace(@FKColumnString, '.+', '.')
close c2
deallocate c2
-- Build a string of each PK's corresponding columns
declare c3 cursor for
select distinct c.TABLE_SCHEMA
, c.table_name as [Parent Table Name]
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE c
on a.UNIQUE_CONSTRAINT_NAME = c.CONSTRAINT_NAME
where b.table_name = @TableName
and b.TABLE_SCHEMA = @SchemaName
and a.CONSTRAINT_NAME = @FKName
open c3
fetch next from c3 into @ParentTableSchema, @ParentTableName
while @@fetch_status=0 begin
set @PKColumnString = @ParentTableSchema+'.'+@ParentTableName+'.'
declare c4 cursor for
select a.COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
and b.CONSTRAINT_TYPE = 'PRIMARY KEY'
where a.TABLE_NAME = @ParentTableName
and a.TABLE_SCHEMA = @ParentTableSchema
order by a.ORDINAL_POSITION
open c4
fetch next from c4 into @Column
while @@fetch_status=0 begin
set @PKColumnString = @PKColumnString + '+' + @Column
fetch next from c4 into @Column
end
set @PKColumnString = replace(@PKColumnString, '.+', '.')
close c4
deallocate c4
fetch next from c3 into @ParentTableSchema, @ParentTableName
insert into @result ([Parent Table], [Child Table], [Constraint], [Relationship]) values (@ParentTableSchema+'.'+@ParentTableName, @SchemaName+'.'+@TableName, @SchemaName+'.'+@TableName+'.'+@FKName, @FKColumnString+' = '+@PKColumnString)
end
close c3
deallocate c3
fetch next from c1 into @FKName
end
close c1
deallocate c1
-- Display results
select
[Child Table] as [Selected Table as Child]
, [Parent Table]
, [Constraint] as [Child Constraint]
, [Relationship] as [Relationship to Parent]
from @result
---------------------------------------------------------------------
-- FKs pointing to the specified table
delete from @result
insert into @FKTemp ([Child Schema Name], [Child Table Name], [Child FK Name], [Column Name])
select b.TABLE_SCHEMA as [Child Schema Name]
, b.TABLE_NAME as [Child Table Name]
, a.CONSTRAINT_NAME as [Child FK Name]
, b.Column_Name as [Column Name]
from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE c
on a.UNIQUE_CONSTRAINT_NAME = c.CONSTRAINT_NAME
and c.TABLE_NAME = @TableName
and c.TABLE_SCHEMA = @SchemaName
inner join INFORMATION_SCHEMA.COLUMNS d
on d.TABLE_SCHEMA = c.TABLE_SCHEMA
and d.TABLE_NAME = c.table_name
and d.COLUMN_NAME = b.COLUMN_NAME
order by d.ORDINAL_POSITION
-- Selected table's PK
set @PKColumnString = @SchemaName+'.'+@TableName+'.'
declare c1 cursor for
select a.COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
on a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
and b.CONSTRAINT_TYPE = 'PRIMARY KEY'
where a.TABLE_NAME = @TableName
and a.TABLE_SCHEMA = @SchemaName
order by a.ORDINAL_POSITION
open c1
fetch next from c1 into @Column
while @@fetch_status=0 begin
set @PKColumnString = @PKColumnString + '+' + @Column
fetch next from c1 into @Column
end
set @PKColumnString = replace(@PKColumnString, '.+', '.')
close c1
deallocate c1
-- Child tables' FKs
declare c1 cursor for select distinct [Child Schema Name], [Child Table Name] from @FKTemp
open c1
fetch next from c1 into @Schema, @Table
while @@fetch_status=0 begin
set @FKColumnString = @SchemaName+'.'+@Table+'.'
declare c2 cursor for select [Child FK Name], [Column Name]
from @FKTemp
where [Child Table Name] = @Table
open c2
fetch next from c2 into @FKName, @Column
while @@fetch_status=0 begin
set @FKColumnString = @FKColumnString + '+' + @Column
fetch next from c2 into @FKName, @Column
end
close c2
deallocate c2
set @FKColumnString = replace(@FKColumnString, '.+', '.') + ' = ' + @PKColumnString
insert into @result ([Parent Table], [Child Table], [Constraint], [Relationship]) values (@SchemaName+'.'+@TableName, @Schema+'.'+@Table, @Schema+'.'+@Table+'.'+@FKName, @FKColumnString)
fetch next from c1 into @Schema, @Table
end
close c1
deallocate c1
-- Display results
select
[Parent Table] as [Selected Table as Parent]
, [Child Table]
, [Constraint] as [Child Constraint]
, [Relationship] as [Relationship Parent]
from @result