Hi Friends,
Just wanted to write a short note on what is the difference between sys.Databases and sys.sysDatabases. Well, both of them return some metadata related to databases hosted on the instance. However, sys.Databases returns 56 columns and sys.sysDatabases returns 12 columns. But why? whats the difference?
SQL Server 2005 introduced System Catalog Views, the preferred way of working with SQL Server meta data. sys.Databases is a system catalog view and there are a number of such views under the sys schema. sys.sysDatabases, howeever, are like the old meta data tables that existed in SQL Server 2000 which is still there for backword compatibility reasons. These are again views but return much less information compared to catalog views (12 columns vs 56 columns). These are called as compatibility views.
Catalog views exist in every user database under the Views -> System Views node and there a dozens of them allowing you to work with every type of metadata. They all exist under sys schema and need to be addressed with the schema. SO if you run select * from Databases (without prefixing sys schema), you will get an error. On the other hand, sys.sysDatabases can be referred as sysDatabases (without prefixing any schema) or dbo.sysDatabases. Simply becuase you might have a number of scripts referring to them in that way written for SQL Server 2000 – so nothing breaks.
It is preferred that you work with new system catalog views (not really new though) – you can observe that there is almost one column in sys.Databases for every database option 🙂
Hi Amit, This is the very big confussion for me long time, here I am clear about the difference between sys.databases and sys.sysdatabases. this is very good post.
Same comment as of Jayaram- “Hi Amit, This is the very big confussion for me long time, here I am clear about the difference between sys.databases and sys.sysdatabases. this is very good post.”.
Thank you.