some important system tables and procedures

some system tables

select * from sys.database_recovery_status

select * from DBROLES

--gives info about the database roles in each database

select * from sys.identity_columns

-- lists down all the identity columns in your database

select * from information_schema.KEY_COLUMN_USAGE

-- lists down all the primary keys in that database

select * from information_schema.ROUTINES
-- lists down all the SPs and functions accessed by the current user in that database

select * from information_schema.SCHEMATA
-- lists all the schemas in the database

 Name: sp_add_data_file_recover_suspect_db 
 Purpose: Adds a data file to a suspect database and runs  recovery on the database.  This SP should only be used   on databases that have been marked suspect due to  insufficient data (error 1105) or log (error 9002) space. 

Name: sp_add_log_file_recover_suspect_db 


--sp_attach_single_file_db
used to attach databases when there is no log file and only data file exists.

sp_changegroup
-- used to move a user from one role to another

sp_clean_db_file_free_space
this internally executes dbcc cleanpage(dbid,fileid,pageno) for each page.
This procedure frees up each page in the specificed file and in the specified database.

sp_clean_db_free_space


sp_databases
lists all the databases, this is similar to sp_helpdb

sp_depends <objectname>
this is used to get dependencies of a particular object in that database.
you can also get this information from the below table
select * from sysdepends


sp_helpsort
this will give the collation setting

sp_helpstats
stats for the particular object

sp_server_info