如何修复损坏的SQL视图 [英] How to fix a broken SQL view

问题描述

我正在使用在CMS后端上运行的开源CMS pimcore(http://www.pimcore.org).

它使用相当复杂的视图来表示对象，并且当该视图所引用的另一个表中的列被重命名时，其中一个在某个阶段已损坏.每当我尝试通过SQL命令与表进行交互时，都会收到错误消息:

查看'barriste_website.object_6'引用无效的表或 列或函数或视图的定义器/调用者缺乏使用权 他们

我想简单地更新视图以引用重命名的列，但是在开始四处浏览之前，我需要了解视图的当前结构-断开视图后如何准确检索视图的结构?我已经尝试过

SHOW CREATE VIEW object_6

但是我收到同样的错误.

谢谢您的帮助！

解决方案

MySQL:

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';

参考: INFORMATION_SCHEMA VIEWS表

SQL Server:

USE databasename
GO

EXEC sp_helptext viewName

或类似的查询:

SELECT TABLE_NAME as ViewName,
VIEW_DEFINITION as ViewDefinition
FROM INFORMATION_SCHEMA.Views

您可以在其中添加WHERE仅检索一个视图

I am using the open source CMS pimcore (http://www.pimcore.org), which runs on a MySQL backend.

It uses fairly complicated views to represent objects and one of them has become broken at some stage when a column in another table being referenced by the view was renamed. Whenever I try to interact with the table via SQL commands I receive the error:

View 'barriste_website.object_6' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

I would like to simply update the view to reference the renamed columns, but I need to know the current structure of the view before I start poking around - how exactly do I retrieve the structure of the view once it is broken? I've tried

SHOW CREATE VIEW object_6

but I receive the same error.

Thanks for your help in advance!

解决方案

MySQL:

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';

reference: The INFORMATION_SCHEMA VIEWS Table

SQL Server:

USE databasename
GO

EXEC sp_helptext viewName

or also a query like this:

SELECT TABLE_NAME as ViewName,
VIEW_DEFINITION as ViewDefinition
FROM INFORMATION_SCHEMA.Views

where you could add a WHERE to only retrieve one view

这篇关于如何修复损坏的SQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！