Sqlserver driver cannot describe View #6501
Description
MSSQL in Cakephp 3. I defined a UsersTable class to use a view in Sqlserver called 'vUser'. I receive the error "Cannot describe vUser. It has 0 columns." Looking at the generated SQL, this is because it is only looking in the sys.[tables] system view to query the metadata. Here is the original SQL:
SELECT DISTINCT
AC.column_id AS [column_id]
,AC.name AS [name]
,TY.name AS [type]
,AC.max_length AS [char_length]
,AC.precision AS [precision]
,AC.scale AS [scale]
,AC.is_identity AS [autoincrement]
,AC.is_nullable AS [null]
,OBJECT_DEFINITION(AC.default_object_id) AS [default]
FROM sys.[tables] T
INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]
WHERE T.[name] = 'vUser' AND S.[name] = 'dbo'
ORDER BY column_id
Here is SQL that instead looks at the sys.[objects] system view first and then joins to the table or view based on the [object_id]:
SELECT DISTINCT
AC.column_id AS [column_id]
,AC.name AS [name]
,TY.name AS [type]
,AC.max_length AS [char_length]
,AC.precision AS [precision]
,AC.scale AS [scale]
,AC.is_identity AS [autoincrement]
,AC.is_nullable AS [null]
,OBJECT_DEFINITION(AC.default_object_id) AS [default]
FROM sys.[objects] O
INNER JOIN (
SELECT [object_id], [schema_id] from sys.[tables]
UNION
SELECT [object_id], [schema_id] from sys.[views]
) AS T on O.[object_id] = T.[object_id]
INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]
INNER JOIN sys.[all_columns] AC ON O.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]
WHERE O.[name] = 'vUser' AND S.[name] = 'dbo'
ORDER BY column_id
I'm not sure if joining to sys.[tables] or sys.[views] is even necessary; here is SQL that just uses sys.[objects]:
SELECT DISTINCT
AC.column_id AS [column_id]
,AC.name AS [name]
,TY.name AS [type]
,AC.max_length AS [char_length]
,AC.precision AS [precision]
,AC.scale AS [scale]
,AC.is_identity AS [autoincrement]
,AC.is_nullable AS [null]
,OBJECT_DEFINITION(AC.default_object_id) AS [default]
FROM sys.[objects] O
INNER JOIN sys.[schemas] S ON S.[schema_id] = O.[schema_id]
INNER JOIN sys.[all_columns] AC ON O.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]
WHERE O.[name] = 'vUser' AND S.[name] = 'dbo'
ORDER BY column_id
I write my apps against legacy/third party databases, so I use views a lot. It would be nice if I could continue using CakePHP but I don't know enough about it to fix this myself.