Cool Maintenance T-SQL Scripts

How to run an integrity check on all databases

DECLARE @SQL VARCHAR(1000) 
DECLARE @DB sysname
DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR 

 SELECT [name] 
 FROM master..sysdatabases 
 WHERE [name] NOT IN ('tempdb') 
 and DATABASEPROPERTYEX(name, 'Status') != 'OFFLINE'
 ORDER BY [name] 
 
OPEN curDB 
FETCH NEXT FROM curDB INTO @DB 
WHILE @@FETCH_STATUS = 0 
 BEGIN 
 SELECT @SQL = 'USE [' + @DB +']' + CHAR(13) + 'DBCC CHECKDB WITH NO_INFOMSGS' 
+ CHAR(13) 
 --EXEC(@SQL) -- Uncomment to execute the command
 PRINT @SQL -- Prints just the command, comment if you do not need the command 
 FETCH NEXT FROM curDB INTO @DB 
 END 
CLOSE curDB 
DEALLOCATE curDB

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: