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 |