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 (‘?’)”

Continues…

Pages: 1 2 3 4




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |