Unregistered SQL Server Stored Procedures and Functions

Question

Hi guys, I discovered that there are many advanced extended stored procedures in SQL Server that are not documented in online books. I found them from googling. Here are some of them, if you have others, please share them with me.

Undocumented Stored Procedures in SQL Server

sp_checknames sp_columns_rowset sp_enumoledbdatasources sp_fixindex sp_gettypestring sp_ms_marksystemobject sp_msaddguidcolumn sp_msaddguidindex sp_msaddlogin_implicit_ntlogin sp_msadduser_implicit_ntlogin sp_mscheck_uid_owns_anything sp_msdbuseraccess sp_msdbuserpriv sp_msdependencies sp_msdrop_object sp_msforeachdb sp_msforeachtable sp_msget_qualified_name sp_msgettools_path sp_msgetversion sp_msguidtostr sp_mshelpcolumns sp_mshelpindex sp_mshelptype sp_msindexspace sp_msis_pk_col sp_mskilldb sp_msloginmappings sp_mstablekeys sp_mstablerefs sp_mstablespace sp_msunc_to_drive sp_msuniquecolname sp_msuniquename sp_msuniqueobjectname sp_msuniquetempname sp_tempdbspace sp_who2 xp_delete_file xp_dirtree xp_enum_oledb_providers xp_enumcodepages xp_enumdsn xp_enumerrorlogs xp_enumgroups xp_fileexist xp_fixeddrives xp_get_mapi_default_profile xp_get_mapi_profiles xp_getnetname xp_qv xp_readerrorlog xp_regaddmultistring xp_regdeletekey xp_regdeletevalue xp_regenumvalues xp_regread xp_regremovemultistring xp_regwrite xp_subdirs xp_varbintohexstr 

sp_MSforeachtable can be used to cycle through all tables in your databases. Here are some common ways to use this useful stored procedure.

 Display the size of all tables in a database EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'" Display Number of Rows in all Tables in a database EXEC sp_MSforeachtable 'SELECT ''?'', Count(*) as NumberOfRows FROM ?' Rebuild all indexes of all tables in a database EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO 

Note. DBCC DBREINDEX is deprecated in SQL 2005. Microsoft says: “This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new developments and plan to change applications that currently use this feature Instead, use ALTER INDEX.

 Disable all constraints of all tables in a database USE YOURDBNAME EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL" Disable all Triggers of all tables in a database USE YOURDBNAME EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL' Delete all data from all tables in your database -- disable referential integrity EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' GO EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1 DELETE FROM ? else TRUNCATE TABLE ? ' GO -- enable referential integrity again EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' GO To RESEED all table to 0, use this script EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1 DBCC CHECKIDENT (''?'', RESEED, 0) GO 

guys share their knowledge in one place, so many developers get baths from this.