{"action":"created","issue":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/6501","labels_url":"https://api.github.com/repos/cakephp/cakephp/issues/6501/labels{/name}","comments_url":"https://api.github.com/repos/cakephp/cakephp/issues/6501/comments","events_url":"https://api.github.com/repos/cakephp/cakephp/issues/6501/events","html_url":"https://github.com/cakephp/cakephp/issues/6501","id":73659341,"number":6501,"title":"Sqlserver driver cannot describe View","user":{"login":"cburch-mmsd","id":12276804,"avatar_url":"https://avatars.githubusercontent.com/u/12276804?v=3","gravatar_id":"","url":"https://api.github.com/users/cburch-mmsd","html_url":"https://github.com/cburch-mmsd","followers_url":"https://api.github.com/users/cburch-mmsd/followers","following_url":"https://api.github.com/users/cburch-mmsd/following{/other_user}","gists_url":"https://api.github.com/users/cburch-mmsd/gists{/gist_id}","starred_url":"https://api.github.com/users/cburch-mmsd/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cburch-mmsd/subscriptions","organizations_url":"https://api.github.com/users/cburch-mmsd/orgs","repos_url":"https://api.github.com/users/cburch-mmsd/repos","events_url":"https://api.github.com/users/cburch-mmsd/events{/privacy}","received_events_url":"https://api.github.com/users/cburch-mmsd/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/cakephp/cakephp/labels/sqlserver","name":"sqlserver","color":"FFFFFF"}],"state":"open","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/cakephp/cakephp/milestones/39","html_url":"https://github.com/cakephp/cakephp/milestones/3.0.4","labels_url":"https://api.github.com/repos/cakephp/cakephp/milestones/39/labels","id":1094093,"number":39,"title":"3.0.4","description":"","creator":{"login":"lorenzo","id":37621,"avatar_url":"https://avatars.githubusercontent.com/u/37621?v=3","gravatar_id":"","url":"https://api.github.com/users/lorenzo","html_url":"https://github.com/lorenzo","followers_url":"https://api.github.com/users/lorenzo/followers","following_url":"https://api.github.com/users/lorenzo/following{/other_user}","gists_url":"https://api.github.com/users/lorenzo/gists{/gist_id}","starred_url":"https://api.github.com/users/lorenzo/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lorenzo/subscriptions","organizations_url":"https://api.github.com/users/lorenzo/orgs","repos_url":"https://api.github.com/users/lorenzo/repos","events_url":"https://api.github.com/users/lorenzo/events{/privacy}","received_events_url":"https://api.github.com/users/lorenzo/received_events","type":"User","site_admin":false},"open_issues":15,"closed_issues":22,"state":"open","created_at":"2015-05-03T15:16:55Z","updated_at":"2015-05-06T17:43:05Z","due_on":"2015-05-16T22:00:00Z","closed_at":null},"comments":1,"created_at":"2015-05-06T15:39:36Z","updated_at":"2015-05-06T17:45:19Z","closed_at":null,"body":"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:\r\n\r\n```\r\nSELECT DISTINCT\r\n AC.column_id AS [column_id]\r\n,AC.name AS [name]\r\n,TY.name AS [type]\r\n,AC.max_length AS [char_length]\r\n,AC.precision AS [precision]\r\n,AC.scale AS [scale]\r\n,AC.is_identity AS [autoincrement]\r\n,AC.is_nullable AS [null]\r\n,OBJECT_DEFINITION(AC.default_object_id) AS [default] \r\nFROM sys.[tables] T \r\n    INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]\r\n    INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]\r\n    INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]\r\nWHERE T.[name] = 'vUser' AND S.[name] = 'dbo'\r\nORDER BY column_id\r\n```\r\n\r\nHere 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]:\r\n\r\n```\r\nSELECT DISTINCT\r\n AC.column_id AS [column_id]\r\n,AC.name AS [name]\r\n,TY.name AS [type]\r\n,AC.max_length AS [char_length]\r\n,AC.precision AS [precision]\r\n,AC.scale AS [scale]\r\n,AC.is_identity AS [autoincrement]\r\n,AC.is_nullable AS [null]\r\n,OBJECT_DEFINITION(AC.default_object_id) AS [default] \r\nFROM sys.[objects] O\r\n    INNER JOIN (\r\n        SELECT [object_id], [schema_id] from sys.[tables]\r\n        UNION\r\n        SELECT [object_id], [schema_id] from sys.[views]\r\n    ) AS T on O.[object_id] = T.[object_id]\r\n    INNER JOIN sys.[schemas] S ON S.[schema_id] = T.[schema_id]\r\n    INNER JOIN sys.[all_columns] AC ON O.[object_id] = AC.[object_id]\r\n    INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]\r\nWHERE O.[name] = 'vUser' AND S.[name] = 'dbo'\r\nORDER BY column_id\r\n```\r\n\r\nI'm not sure if joining to sys.[tables] or sys.[views] is even necessary; here is SQL that just uses sys.[objects]:\r\n\r\n```\r\nSELECT DISTINCT\r\n AC.column_id AS [column_id]\r\n,AC.name AS [name]\r\n,TY.name AS [type]\r\n,AC.max_length AS [char_length]\r\n,AC.precision AS [precision]\r\n,AC.scale AS [scale]\r\n,AC.is_identity AS [autoincrement]\r\n,AC.is_nullable AS [null]\r\n,OBJECT_DEFINITION(AC.default_object_id) AS [default] \r\nFROM sys.[objects] O\r\n    INNER JOIN sys.[schemas] S ON S.[schema_id] = O.[schema_id]\r\n    INNER JOIN sys.[all_columns] AC ON O.[object_id] = AC.[object_id]\r\n    INNER JOIN sys.[types] TY ON TY.[user_type_id] = AC.[user_type_id]\r\nWHERE O.[name] = 'vUser' AND S.[name] = 'dbo'\r\nORDER BY column_id\r\n```\r\n\r\nI 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."},"comment":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/comments/99550269","html_url":"https://github.com/cakephp/cakephp/issues/6501#issuecomment-99550269","issue_url":"https://api.github.com/repos/cakephp/cakephp/issues/6501","id":99550269,"user":{"login":"markstory","id":24086,"avatar_url":"https://avatars.githubusercontent.com/u/24086?v=3","gravatar_id":"","url":"https://api.github.com/users/markstory","html_url":"https://github.com/markstory","followers_url":"https://api.github.com/users/markstory/followers","following_url":"https://api.github.com/users/markstory/following{/other_user}","gists_url":"https://api.github.com/users/markstory/gists{/gist_id}","starred_url":"https://api.github.com/users/markstory/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/markstory/subscriptions","organizations_url":"https://api.github.com/users/markstory/orgs","repos_url":"https://api.github.com/users/markstory/repos","events_url":"https://api.github.com/users/markstory/events{/privacy}","received_events_url":"https://api.github.com/users/markstory/received_events","type":"User","site_admin":false},"created_at":"2015-05-06T17:45:19Z","updated_at":"2015-05-06T17:45:19Z","body":"Does sys.views provide the same column names as sys.tables?  If it does we can easily reflect on both. I'm not very fluent in SQLServer, but am happy to update the schema reflection query if your queries will work with views + tables."}}