SQL Studio Data Compare and SQL Server Meta Data Access Methods

Question:
How does SQL Studio Data Compare access SQL Server meta data?

Answer:
Microsoft SQL Server provides two methods for obtaining meta data: information schema views or system tables. Information schema views provide an internal, system table-independent view of the SQL Server meta data and they are the recommended way to access it. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the SQL-92 standard definition for the INFORMATION_SCHEMA.

Information schema views are based on catalog view definitions in the SQL-92 standard. They present the catalog information in a format that is independent of any catalog table implementation and therefore are not affected by changes in the underlying catalog tables. Applications that use these views are portable between heterogeneous SQL-92 compliant database systems.

In earlier versions of SQL Server, meta data for all objects in a database is visible to members of the public role. This means that any user that is logged on to an instance of SQL Server can view meta data for every object in the server, even those objects on which they had no rights. In SQL Server 2005, the visibility of meta data is limited to objects that a user either owns or on which the user has been granted some permission.

Limited meta data accessibility means the following:
- Applications that assume public meta data access will break.
- Queries on system views might only return a subset of rows, or sometimes an empty result set.

To allow viewing of meta data, you have to grant the callers VIEW DEFINITION permission (SELECT permission for the information schema views) at an appropriate scope: object level, database level or server level.

Note: By default SQL Studio Data Compare is configured to use information schema views to obtain meta data. Because of the limitations to access information schema views sometimes it might not be possible retrieving the meta data needed to compare objects. Since meta data visibility configuration does not apply to the system tables we have provided an additional option to switch and use system tables directly instead of information schema views. The system base tables are used only within the SQL Server Database Engine and are not for general customer use. They are subject to change and compatibility is not guaranteed.

Note: Using system tables has shown better performance compared to using information schema views.