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
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.
used to attach databases when there is no log file and only data file exists.
-- used to move a user from one role to another
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.
lists all the databases, this is similar to sp_helpdb
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
this will give the collation setting
stats for the particular object