Three useful system catalog views for finding object information:
sys.tables provides one row for each table in a database. This does include user tables and system tables that exist in each database. There is a column within sys.tables: [is_ms_shipped] that identifies system tables. This allows you to filter your results to just user tables if you so desire without having to join to the sys.sysobject system compatibility view’s type column (where sys.sysobjects.type = ‘U’) in order to do so.
sys.all_columns offers a row for each column for every object in a database. Many of the columns are shared with sys.types, and we pull most of the metadata from this view, but there is still one column lacking from our result set that we must pull from sys.types.
sys.types is the catalog view that stores rows relating to system and user-defined data types and their properties. The only field from this view we will need is the data type’s name, as it is the only field in our desired result set we can’t return from sys.all_columns as it pertains to column metadata.
Each of the catalog views are scoped at the database level, meaning that they exist in each database (system or user) on the SQL Server instance. Even sys.types, a catalog view you may expect to be the same across all databases on an instance is scoped at the database level. Why? Quite simply put, sys.types includes user-defined datatypes that are unique to a database. Database collation also has an impact on sys.types, therefore making native data types such as text, ntext, varchar(), char(), nvarchar(), nchar(), and sysname different between databases if their collations differ.