sql-server - 需要在SQL Server存储过程的try catch block 内产生错误的对象名称

Question

我创建一个存储过程来更新数据库的结构。在try/catch块中，如果发生错误，我可以获取ERROR_MESSAGE()值，但它没有指定错误发生的位置或对象。 ERROR_NUMBER()函数是无用的，因为行号很多了。

无论如何，是否可以标识正在创建的对象或失败的语句？
以下是try catch块的一个小示例:

BEGIN TRY
BEGIN TRANSACTION 

ALTER TABLE [dbo].[Activity] WITH CHECK ADD CONSTRAINT [FK_dbo.Activity_dbo.Application_ApplicationId] FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[Application] ([ApplicationId]) ON DELETE CASCADE 
ALTER TABLE [dbo].[Activity] CHECK CONSTRAINT [FK_dbo.Activity_dbo.Application_ApplicationId] 

ALTER TABLE [dbo].[ApplicationCategory] WITH CHECK ADD CONSTRAINT [FK_ApplicationCategory_Application] FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[Application] ([ApplicationId]) 
ALTER TABLE [dbo].[ApplicationCategory] CHECK CONSTRAINT [FK_ApplicationCategory_Application] 

ALTER TABLE [dbo].[Contacts] WITH CHECK ADD CONSTRAINT [FK_Contacts_Product] FOREIGN KEY ([ProductId]) REFERENCES [dbo].[Product] ([ProductId]) 
ALTER TABLE [dbo].[Contacts] CHECK CONSTRAINT [FK_Contacts_Product] 

ALTER TABLE [dbo].[Frame] WITH CHECK ADD CONSTRAINT [FK_dbo.Frame_dbo.Product_ProductId] FOREIGN KEY ([ProductId]) REFERENCES [dbo].[Product] ([ProductId]) 
ALTER TABLE [dbo].[Frame] CHECK CONSTRAINT [FK_dbo.Frame_dbo.Product_ProductId] 

ALTER TABLE [dbo].[FramesMarkup] WITH CHECK ADD CONSTRAINT [FK_dbo.FramesMarkup_dbo.Manufacturer_ManufacturerId] FOREIGN KEY ([ManufacturerId]) REFERENCES [dbo].[Manufacturer] ([ManufacturerId]) 
ALTER TABLE [dbo].[FramesMarkup] CHECK CONSTRAINT [FK_dbo.FramesMarkup_dbo.Manufacturer_ManufacturerId] 

ALTER TABLE [dbo].[InventoryDecrement] WITH CHECK ADD CONSTRAINT [FK_dbo.InventoryDecrement_dbo.Inventory_InventoryId] FOREIGN KEY ([InventoryId]) REFERENCES [dbo].[Inventory] ([InventoryId]) 
ALTER TABLE [dbo].[InventoryDecrement] CHECK CONSTRAINT [FK_dbo.InventoryDecrement_dbo.Inventory_InventoryId] 

ALTER TABLE [dbo].[Lens] WITH CHECK ADD CONSTRAINT [FK_dbo.Lens_dbo.Rx_RxId] FOREIGN KEY ([RxId]) REFERENCES [dbo].[Rx] ([RxId]) ON DELETE CASCADE 
ALTER TABLE [dbo].[Lens] CHECK CONSTRAINT [FK_dbo.Lens_dbo.Rx_RxId] 

COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION
END CATCH

错误消息是: Could not create constraint. See previous errors.我想知道哪个约束失败。

Answer 1

函数ERROR_LINE()可能会对您有所帮助。它返回发生脚本错误的行号。您可以使用该脚本，只要不经常对其进行编辑，从而导致行号四处移动。