Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
1 day, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
dbcc on one or several databases
Posted on 2012-12-27
Hello,
How can I modify this script for executing dbcc on one or several databases?
{
if ($verbose) {$VerbosePreference = "Continue"}
if ($debug) {$DebugPreference = "Continue"}
dbcc_checkdb $I $D
}
function dbcc_checkdb ($I, $D)
{
$cn = new-object system.data.SqlClient.SqlC
"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.Sql
$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
How can I modify this script for executing dbcc on one or several databases?
{
if ($verbose) {$VerbosePreference = "Continue"}
if ($debug) {$DebugPreference = "Continue"}
dbcc_checkdb $I $D
}
function dbcc_checkdb ($I, $D)
{
$cn = new-object system.data.SqlClient.SqlC
"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.Sql
$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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
5 Comments
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
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
Active 3 days ago
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....
And the above could be made into a SP on the server and pass a delimited list of DB names
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
And the above could be made into a SP on the server and pass a delimited list of DB names
Active 3 days ago
OK...
Then have a look at : http://cornasdf.blogspot.com.au/2012/06/powershell-to-run-dbcc-checkdb-loop-for.html
Thats doing a checkdb, with no info messages, and you want with tableresults, so, the output will be slightly different.
But the important part is it shows how to loop around (I wouldnt kill the connection each time either).
Then have a look at : http://cornasdf.blogspot.com.au/2012/06/powershell-to-run-dbcc-checkdb-loop-for.html
Thats doing a checkdb, with no info messages, and you want with tableresults, so, the output will be slightly different.
But the important part is it shows how to loop around (I wouldnt kill the connection each time either).
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month1 day, 13 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.