Search the Database

Searching for an object in a SQL 2000 Database is easier by using this undocumented stored procedure sp_MSobjsearch. This can be used to search any SQL objects such as User Table, System Table, View, SP, triggers, columns, etc...


EXEC sp_MSobjsearch
=============================================
--PARAMETERS
=============================================
@searchkey default NULL
@dbname default current db = db_name(), valid DB name or * (ALL)
@objecttype default 1 (user table), can be valid objtype or 4096 (ALL), see remarks @hitlimit default 100 rows, 0 is all results
@casesensitive default 0, only valid when server is case sensitive
@status default 0 = no status, 1 = send percentage progress status back based
database/step
@extpropname default NULL
@extpropvalue default NULL

=============================================
-- REMARKS
=============================================
@objecttype
user table = 1 from @dbname..sysobjects
system table = 2 from @dbname..sysobjects
view = 4 from @dbname..sysobjects
sp = 8 from @dbname..sysobjects
rf(repl sp) = 16 from @dbname..sysobjects
xp = 32 from @dbname..sysobjects
trigger = 64 from @dbname..sysobjects
UDF = 128 from @dbname..sysobjects
DRI Constraints = 256 from @dbname..sysobjects
log = 512 from @dbname..sysobjects
column = 1024 from @dbname..syscolumns
index = 2048 from @dbname..sysindexes
all = 4096
=============================================

Delete from Registry using SQL

xp_regdeletekey and xp_regdeletevalue are the two undocumented stored procedures that helps in deleting values and keys from registry. These stored procedures should be used very vary carefully as there are chances of harming the system and system may crash.

xp_regdeletekey
This is an extended stored procedure that will delete an entire key from the registry.
EXEC xp_regdeletekey @rootkey,@key
Example:-
EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Test'

xp_regdeletevalue

This is an extended stored procedure that will delete a particular value for a key in the registry.

EXEC xp_regdeletevalue @rootkey,@key,@value_name

Example:-

EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue'

Registry writing and regisrty reading through SQL

In SQL server we have 2 undocumented stored procedures for reading from registry and for writing into registry. For reading from registry we use the xp_regread and for writing into registry we use xp_regwrite undocumneted extended stored procedures. These two SP`s can be found in master database of a particular server.

Usage :-
EXEC xp_regread @rootkey, @key,[@value_name],[@Value]
Example:-
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= 'SOFTWARE\Microsoft\Microsoft SQLServer\80\Replication\Subscriptions\',
@value_name= 'SubscriberEncryptedPasswordBinary'

EXEC xp_regwrite @rootkey,@key,@value_name,@type,@value
Example:-
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test',
@value_name='TestValue', @type='REG_SZ', @value='Test'

Undocumented stored procedure for retrieving SQL Agent properties

sp_get_sqlagent_properties is and undocumented stored procedure to retrive the SQL Agent properties of a particular server. This stored procedure can be found in msdb database.

Usage:
EXEC msdb..sp_get_sqlagent_properties

Undocumented stored procedure:: sp_MS_upd_sysobj_category

sp_MS_upd_sysobj_category is an undocumneted stored procedure which Enables or disables a special system mode where all newly created objects are automatically shown as system objects in enterprise manager. sp_MS_upd_sysobj_category accepts a parameter @Mode. @Mode can either be 1 or 2. Setting the value of @Mode to 1 enables this special system mode and setting it to 2, disables it. Among other things, sp_MS_upd_sysobj_category allows the creation of user-defined INFORMATION_SCHEMA views.

Usage:
EXEC master.dbo.sp_MS_upd_sysobj_category @Mode
@Mode = 1 or 2
1=Enable
2=Disable

Enable special system mode:
EXEC master.dbo.sp_MS_upd_sysobj_category 1

Disable special system mode:
EXEC master.dbo.sp_MS_upd_sysobj_category 2