Sunday, March 25, 2012

Dbcc Showcontig

I am looking for coding that runs dbcc showcontig on all user databases in one command on a SQL Server. The coding I have listed below will grab the names of user databases, but still runs the showcontig on the master db. I have tried using sp_MSforeachdb, but that performs the showcontig on both system and user databases. Thx!
P.S. If possible this command can be used on SQL 7.0 and 2K.
USE master
DECLARE dnames_cursor CURSOR
FOR
SELECT NAME FROM sysdatabases
where name not in ('master', 'model', 'tempdb', 'pubs', 'Northwind', 'msdb')
OPEN dnames_cursor
DECLARE @.dbname sysname
FETCH NEXT FROM dnames_cursor INTO @.dbname
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.dbname = RTRIM(@.dbname)
set nocount on
select 'ZZ_Date; ', getdate()
set nocount on
Declare @.My_dbname char(30)
exec ('use ' + @.dbname)
select @.My_dbname = @.dbname
print 'ZZ_Name; ' + @.My_dbname
dbcc showcontig
END
FETCH NEXT FROM dnames_cursor INTO @.dbname
END
CLOSE dnames_cursor
DEALLOCATE dnames_cursorMay check this DB Journal (http://www.databasejournal.com/features/mssql/article.php/1442901) link for more information.sql

No comments:

Post a Comment