August 21, 2011 at 10:04 pm
Hi,
I'm using the below cursor DBCC CHECKDB on a production instance.
Just wanted to know that we can improve this script..
Thanks
Create procedure IntegrityCheck
as
begin
declare database_cursor CURSOR for select name from sys.sysdatabases
declare @database_name sysname
open database_cursor
fetch next from database_cursor into @database_name
while @@FETCH_STATUS=0
begin
print @database_name
dbcc checkdb(@database_name) with no_infomsgs
fetch next from database_cursor into @database_name
end
close database_cursor
deallocate database_cursor
end
August 21, 2011 at 10:21 pm
You can perform a cursor or a loop or you can do the following:
exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH ALL_ERRORMSGS, EXTENDED_LOGICAL_CHECKS, DATA_PURITY'
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 21, 2011 at 11:05 pm
Thanks,
which one is recommended for Production instance?
because I believe sp_MSforeachDB is an undocumented procedure.
August 21, 2011 at 11:09 pm
gmamata7 (8/21/2011)
Thanks,which one is recommended for Production instance?
because I believe sp_MSforeachDB is an undocumented procedure.
Just because it is undocumented does not mean that you should not use it.
If you feel more comfortable with a Cursor or WHILE LOOP than go with it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply