Avatar of bibi92
bibi92
Flag for France asked on

dbcc on one or several databases

Hello,

How can I modify this script for executing dbcc on one or several databases?

Open in new window

function main()
{
      if ($verbose) {$VerbosePreference = "Continue"}
      if ($debug) {$DebugPreference = "Continue"}
      dbcc_checkdb $I $D
}

function dbcc_checkdb ($I, $D)
{
      $cn = new-object system.data.SqlClient.SqlConnection( `
            "Data Source=$I;Integrated Security=SSPI;Initial Catalog=$D");
      $ds = new-object System.Data.DataSet "dsCheckDB"
      $query = "DBCC CHECKDB($D) WITH TABLERESULTS"
      $da = new-object "System.Data.SqlClient.SqlDataAdapter" ($query, $cn)
      $da.Fill($ds)

      $dtCheckDB = new-object "System.Data.DataTable" "dsCheckDB"
      $dtCheckDB = $ds.Tables[0]
      $dtCheckDB | Format-Table -autosize `
      -property Error, Level, State, MessageText, `
                                    RepairLevel, Status, DbId, ObjectId, `
                                    IndexId, PartitionId, AllocUnitId, File, `
                                    Page, Slot, RefFile, RefPage, RefSlot, Allocation
}

Thanks

bibi
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
bibi92

8/22/2022 - Mon
sachitjain

If you run following logic on master database, it would run dbcc checkdb for all the databases

declare @table table (SNO int identity(1,1), [name] sysname)
declare @i int, @cnt int, @dbname sysname, @query nvarchar(100)

insert into @table ([name])
select [name] from sys.sysdatabases

set @i = 1
while @i <= @cnt
begin
      select @dbname = [name] from @table where SNO = @i
      set @query = ';
                              DBCC CHECKDB;'
      set @query = 'use ' + CAST(@dbname as varchar(30)) + ' ' + @query
      print @query
      exec sp_executesql @query
      set @i = @i + 1
end
Mark Wills

Well, the script appears to be OK with a single database - right ?

So, the question is how to make it loop around for multiple $D given the instance of $I

Ideally, create a procedure on your SQL Server and simply pass the $D as a parameter to the stored procedure and the parameter can have multiple database names seperated by a delimiter (like a comma).

Or, create an arrary of database names and then use that to loop around and build up your query. Or, if you need to process each database seperately (because of results) then you will also need to include keeping the results / processing before moving onto next.

Or, you could build up a query to go through each one and collect the results (and I have cheated a little by simply referring to column names as col1 col2 etc). Note that the #checkdb temp table MUST match the correct number of columns, so if doing a different checkdb then that table will be different....

if object_id('tempdb..#checkdb','U') is not null drop table #checkdb
create table #checkdb (id int identity,[error] varchar(100), [level] varchar(100), [state] varchar(100), [MessageText] varchar(555), [repairlevel] varchar(100), [status] varchar(100), col1 varchar(100), col2 varchar(100), col3 varchar(100), col4 varchar(100), col5 varchar(100), col6 varchar(100), col7 varchar(100), col8 varchar(100), col9 varchar(100), col10 varchar(100),col11 varchar(100), col12 varchar(100), col13 varchar(100), col14 varchar(100), col15 varchar(100), col16 varchar(100), col17 varchar(100))

insert #checkdb (MessageText) values ('dbcc checkdb(my_db) with tableresults')
insert #checkdb
exec ('dbcc checkdb(my_db) with tableresults')

insert #checkdb (MessageText) values ('dbcc checkdb(adventureworks2012) with tableresults')
insert #checkdb
exec ('dbcc checkdb(adventureworks2012) with tableresults')

select * from #checkdb

Open in new window


And the above could be made into a SP on the server and pass a delimited list of DB names
bibi92

ASKER
Thanks but I have to use powershell.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bibi92

ASKER
Thanks bibi