-- Revoke get the qualified name of the given object use pubsgodeclare @ object_id int, @ qualified_name nvarchar (512) Select @ object_id = object_id ('author') exec sp_msget_qualified_name @ object_id, @ qualified_name outputselect @ qualified_name -- sp_msdrop_object is used to delete the specified object ID (it can be a table, view, or stored procedure ), take titleauthor as an example use pubsgodeclare @ object_id intselect @ object_id = object_id ('titleauthor') exec sp_msdrop_object @ object_idgo -- sp_msgettools_path to return SQL Server 2000 tools and utilities Program The path to use mastergodeclare @ install_path nvarchar (260) exec sp_msgettools_path @ install_path outputselect @ install_pathgo -- sp_mscheck_uid_owns_anything returns the list of objects owned by the specified user. Use pubsgoexec into uidgo -- sp_columns_rowset returns the complete column description, including the length and type. Use pubsgoexec sp_columns_rowset 'author' go -- sp_msforeachdb sometimes, you need all databases to perform the same action. The cursor can do this, or you can use the stored procedure to complete sp_msforeachdb with a small number Code To achieve the same purpose. For all databases, checkdbexec sp_msforeachdb @ command1 = "print '? 'Dbcc checkdb ('? ') "-- Sp_msforeachtable stored procedure to recreate all the cursors in the database: exec sp_msforeachtable @ command1 =" print '? 'Dbcc dbreindex ('? ') "-- Sp_mshelpcolumns the stored procedure returns the name table of the complete schema, including the length, type, and whether to calculate the column. Use pubsgoexec sp_mshelpcolumns 'authors '-- the specified name information returned by sp_mshelpindex, status, fill factor, index column name and file table group use pubsgoexec sp_mshelpindex 'authors' -- sp_mshelptype returns all data types of the database, including the User-Defined use pubsgoexec sp_mshelptype -- sp_msindexspace returned table index space occupied by use pubsgoexec sp_msindexspace 'authors '-- sp_mskilldb Delete corrupt databases, DBCC dbrepairuse mastergoexec sp_mskilldb 'pubs' -- sp_mstablespace returns the number of table rows, use pubsgoexec sp_mstablespace 'author' -- sp_tempdbspace to return the size of the temporary database and the size of the used space exec sp_tempdbspace -- sp_who2 to return the process information of the specified user, exec sp_who2 'sa 'is more detailed than sp_who'
Original article: http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm