Problem in ORDER BY clause

• 
  Hi Adam,
  
  
  Unfortunately for SQL Server 2005 we are unable to retrieve detailed information about system views, such as the columns. This is due to changes MS have made between 2000 and 2005 that prevent us getting at that information directly. It may be that in the future we are able to work around this, however for now that isn't going to be possible unfortunately. We use SQL Compare as our meta-data store, and it just can't get the information it needs out of SQL Server.
  
  Now, having said that SSMS displays the information which means one of the following is probably true:
  
  (1) Somehow it's possible to get the system view definitions out of SQL Server 2005 and thereby deduce the columns in the views based on their text definition (we already do a similar kind of thing to support derived tables). If this is possible we can make the necessary changes in SQL Compare to do this and then have to do no further work, but this could impact other products using the SQL Compare back-end.
  
  (2) SSMS uses SMO (I'm not sure whether it does or not) and this exposes information about system views that isn't directly available through the system views themselves. If this is true we can provide this support in a future version of SQL Prompt, but not in this version because we'd need to cache this information separately from the main meta-data. We'd also have to do some other bits of plumbing, and create the appropriate wrappers for the SMO types.
  
  (3) SSMS includes hardcoded information about system objects in SQL Server 2005. Obviously we can replicate this but it'll be quite time-consuming.
  
  None of the above is by any means a quick fix unfortunately so I'm afraid this extra information won't be appearing for the final release of SQL Prompt 3. You will notice however that such information is available when connected to SQL Server 2000 (e.g. try your query against the sysdatabases table in 2000 and it works perfectly).
  
  Hope that goes some way towards explaining the issues.
  
  
  Thanks,
  Bart

  Bart Read December 14, 2006 09:31

  - 0 +
• 
  Hi Bart,
  
  I'm not sure what the issue is. Can't you just grab the system view columns from the sys.all_columns view? What's keeping you from doing so?

  amachanic December 18, 2006 14:34

  - 0 +
• 
  Hi Adam,
  
  
  That's true, but I'm assuming it would still have to be treated as a special case within the SQL Compare engine since clearly for whatever reason it isn't pulling this information out in the first place (note that I haven't confirmed this by looking at the SQL Compare source code because there is rather a lot of it).
  
  What I have done is spent half an hour looking at SQL Server 2005 system views, which leaves me feeling unsure as to why this information is not available since text definitions are available for system objects. I strongly suspect we use the text definitions for views these rather than information stored in sys.all_columns to figure out the columns because we generally distrust the system tables for other things such as dependencies (again, I've not confirmed this by looking at the source code). Anyway, this is exactly the same kind of thing I do to support derived tables so I suspect we also do this for user views. My investigations showed me that system object text definitions are all in exactly the same place (sys.all_sql_modules) as user object text definitions so I'm at a loss as to why we don't display column information for them.
  
  The only thing I can speculate is that perhaps because we can't chase the columns back to an underlying table, we can't (except by referring to sys.all_columns) determine their data types. Having spoken to Andras about a related issue two or three weeks back I'd say this would almost certainly cause problems in the SQL Compare engine, which is why we'd need to deal with this as a special case and pull the information from sys.all_columns for system views. Now this raises an interesting question, which is what would SQL Compare do if somebody had defined a user view that selected columns from one or more system views, or perhaps a mixture of system views and user tables/views?
  
  Unfortunately I can't answer any of this at the moment since Andras is away on holiday until the New Year, however I'll certainly be following these issues up with him once he returns.
  
  
  Thanks,
  Bart

  Bart Read December 19, 2006 07:09

  - 0 +

Add comment

Please sign in to leave a comment.