How to fix a broken SQL view

Question

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!

Answer 1

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

Answer 2

Simply delete the view with "drop view object_6", then go into the pimcore backend and save the class again. The view is then regenerated automatically.