There are several catalog views that can be used to query database related info. We also have system stored procedures to get same basic information.
Some of the commonly used one’s are:
1.sp_helpdb
2.sp_help
3.sp_helpfile
4.sp_databases
5.sp_tables: list the table names
sp_tables @table_owner='HumanResources',@table_type="'Table','view'"
6.sp_columns: lists the column names and types and other columns details
sp_columns @table_owner='Person',@table_name='Person'
7.sp_pkeys: lists the primary key of the table
sp_pkeys @table_owner='Person',@table_name='Person'
8.sp_fkeys: lists all the tables where the key is referred.
sp_fkeys @pktable_name='Employee',@pktable_owner='HumanResources'
SQL Server Repository 