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
No comments:
Post a Comment