SQL Server 2000 Undocumented Stored Procedures


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




sp_MShelpcolumns

This stored procedure returns the complete schema for a table, including the length, type, name, and whether a column is computed.

Syntax

sp_MShelpcolumns tablename [, flags] [, orderby] [, flags2]

where

tablename – is the table name. tablename is nvarchar(517).

flags - flags is int, with a default of 0.

orderby - orderby is nvarchar(10), with a default of NULL.

flags - flags2 is int, with a default of 0.

To get the full columns description for the authors table in the pubs database, run:

USE pubs

GO

EXEC sp_MShelpcolumns ‘authors’

GO





sp_MShelpindex

This stored procedure returns information about name, status, fill factor, index columns names, and file groups for a given table.

Syntax

sp_MShelpindex tablename [, indexname] [, flags]

where

tablename – is the table name. tablename is nvarchar(517).

indexname – is the index name. indexname is nvarchar(258), with a default of NULL.

flags - flags is int, with a default of NULL.

To get the indexes description for the authors table in the pubs database, run: 

USE pubs

GO

EXEC sp_MShelpindex ‘authors’

GO



Continues…

Pages: 1 2 3 4 5




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 |