Monday, March 26, 2012

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 

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

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

this will give the collation setting

stats for the particular object


No comments:

Post a Comment