SQL Server 7.0 Undocumented Stored Procedures

In this article, I want to tell you about some useful undocumented stored procedures shipped with SQL Server 7.0.

sp_columns_rowset


This stored procedure returns the complete column description, including the length, type, name, and so on.

Syntax

sp_columns_rowset table_name [, table_schema ] [, column_name]

where

table_name - is the table name, table_name is sysname.

table_schema – is the table schema, table_schema is sysname, with a default of NULL.

column_name - is the column name, column_name is sysname, with a default of NULL.

This is the example:

USE pubs GO EXEC sp_columns_rowset ‘authors’ GO

sp_fixindex

This stored procedure can be used to fix corruption in a system table by recreating the index.

Syntax

sp_fixindex dbname, tabname, indid

where

dbname - is the database name, dbname is sysname

tabname – is the system table name, tabname is sysname

indid - is the index id value, indid is int

Note. Before using this stored procedure the database has to be in single user mode.

See this link for more information:

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14051

This is the example:

USE pubs GO EXEC sp_fixindex pubs, sysindexes, 2 GO

sp_MSforeachdb

Sometimes, you need to perform the same actions for all tables in the database. You can create cursor for this purpose, or you can also use sp_MSforeachdb stored procedure to accomplish the same goal with less work.

For example, you can use this stored procedure to run a CHECKDB for all the databases on your server.

EXEC sp_MSforeachdb @command1=”print ‘?’ DBCC CHECKDB (‘?’)”

sp_MSforeachtable

Sometimes, you need to perform the same actions for all tables in the database. You can create cursor for this purpose, or you can also use sp_MSforeachtable stored procedure to accomplish the same goal with less work.

For example, you can use this stored procedure to rebuild all the indexes in a database.

EXEC sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”