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