Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

DBCC on a detached MDF file Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 2:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 27, 2014 11:15 AM
Points: 2,041, Visits: 1,671
I haven't tried it, but it should do. My guess is the number of ' are tripping you up.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #1441482
Posted Wednesday, April 17, 2013 3:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:38 AM
Points: 12, Visits: 27
Alright. Played around (and Googled), and came up with this:

DBCC traceon (3604);

drop table #DBID
Create table #DBID (
Name nvarCHAR(256)NULL,
LogicalName nvarCHAR(256)NULL,
BindingID nvarCHAR(256)NULL)

declare @db varchar(256),
@dbid int,
@hidb int

select @hidb = MAX(dbid),
@dbid = 5
from sysdatabases
while @dbid <= @hidb

begin
set @db = null
select @db = name
from sysdatabases where dbid = @dbid
if @db is not null
Declare @Cmd varchar(8000)
insert into #DBID (LogicalName,BindingID)
EXEC ('DBCC fileheader(@dbid)')
insert into #DBID (Name) Values (@db)
set @dbid = @dbid + 1
end

dbcc traceoff(3604)

select Name,LogicalName,BindingID from #DBID

-----------------------------------

Now the problem is that it doesn't like the @dbid in the EXEC command. So it pulls all the names as it scrolls through the DB's, but I get the "Must declare the scalar variable @dbid" message. Any ideas on how to work around that? Thank you for the help!!!
Post #1443520
Posted Wednesday, April 17, 2013 4:02 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 20,798, Visits: 32,713
mike.hamilton721 (4/17/2013)
Alright. Played around (and Googled), and came up with this:

DBCC traceon (3604);

drop table #DBID
Create table #DBID (
Name nvarCHAR(256)NULL,
LogicalName nvarCHAR(256)NULL,
BindingID nvarCHAR(256)NULL)

declare @db varchar(256),
@dbid int,
@hidb int

select @hidb = MAX(dbid),
@dbid = 5
from sysdatabases
while @dbid <= @hidb

begin
set @db = null
select @db = name
from sysdatabases where dbid = @dbid
if @db is not null
Declare @Cmd varchar(8000)
insert into #DBID (LogicalName,BindingID)
EXEC ('DBCC fileheader(@dbid)')
insert into #DBID (Name) Values (@db)
set @dbid = @dbid + 1
end

dbcc traceoff(3604)

select Name,LogicalName,BindingID from #DBID

-----------------------------------

Now the problem is that it doesn't like the @dbid in the EXEC command. So it pulls all the names as it scrolls through the DB's, but I get the "Must declare the scalar variable @dbid" message. Any ideas on how to work around that? Thank you for the help!!!


Two problems I see. First, you declare @dbid in the outer script but never populate it. Second, if it was populated, your not adding it to you dynamic sql correctly.


This, EXEC ('DBCC fileheader(@dbid)'), is where you are getting you error since @dbid is not declared in the context of the dynamic sql. You probably want this: EXEC ('DBCC fileheader(' + cast(@dbid as varchar)' + ')').




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443525
Posted Wednesday, April 17, 2013 4:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:38 AM
Points: 12, Visits: 27
Thank you Lynn. I tried that new EXEC you listed (missing a ' ?), and I thought I added it in the right spot. But I'm not sure what you mean about populating? I did
@dbid = 5

above the BEGIN string. Is that not how I should do this?

The exec line now looks as follows:

EXEC ('DBCC fileheader(''+cast(@dbid as varchar)'+')')

This is over my head to be honest Thank you for the helP!!
Post #1443535
Posted Wednesday, April 17, 2013 4:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 20,798, Visits: 32,713
mike.hamilton721 (4/17/2013)
Thank you Lynn. I tried that new EXEC you listed (missing a ' ?), and I thought I added it in the right spot. But I'm not sure what you mean about populating? I did
@dbid = 5

above the BEGIN string. Is that not how I should do this?

The exec line now looks as follows:

EXEC ('DBCC fileheader(''+cast(@dbid as varchar)'+')')

This is over my head to be honest Thank you for the helP!!


Sorry, missed it in all the unformatted code.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443541
Posted Wednesday, April 17, 2013 5:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:38 AM
Points: 12, Visits: 27
Lynn Pettis (4/17/2013)
mike.hamilton721 (4/17/2013)
Thank you Lynn. I tried that new EXEC you listed (missing a ' ?), and I thought I added it in the right spot. But I'm not sure what you mean about populating? I did
@dbid = 5

above the BEGIN string. Is that not how I should do this?

The exec line now looks as follows:

EXEC ('DBCC fileheader(''+cast(@dbid as varchar)'+')')

This is over my head to be honest Thank you for the helP!!


Sorry, missed it in all the unformatted code.


Hmm. Still getting incorrect syntax... Do I need to change anything besides the exec? Its saying cast syntax
Post #1443550
Posted Wednesday, April 17, 2013 5:45 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 20,798, Visits: 32,713
I can see the problem, you have an extra single before the cast and before the final + sign.

Look at what I had posted earlier.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1443553
Posted Thursday, April 18, 2013 7:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:38 AM
Points: 12, Visits: 27
Lynn Pettis (4/17/2013)
I can see the problem, you have an extra single before the cast and before the final + sign.

Look at what I had posted earlier.


EXEC ('DBCC fileheader(' + cast(@dbid as varchar)' + ')')

That's the one from earlier that you posted, which is missing a ' (or has 1 too many?). I've tried putting it everywhere I think it should go, and get 1 error or another haha. So now I have:

EXEC ('DBCC fileheader(' + cast(@dbid as varchar) + ')')

And that is giving me "incorrect syntax near cast". I removed the quote before the last +. I've also tried putting ' around the '@dbid as varchar' and still got syntax. Sorry to be a bother! :-\ Thank you for all the help though!
Post #1443853
Posted Thursday, April 18, 2013 7:49 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:14 AM
Points: 4,452, Visits: 3,904
First create a temp table to receive the data from your DBCC command. I used #table_name below.
Then fire something similar to the following to populate it.

EXECUTE sp_msforeachdb N'USE ?;
INSERT INTO #table_name
EXECUTE sp_executeSQL N''DBCC fileheader(?)'';';

Then you can select from #table_name to get the fields you want.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1443874
Posted Thursday, April 18, 2013 8:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 23, 2013 7:38 AM
Points: 12, Visits: 27
Ed Wagner (4/18/2013)
First create a temp table to receive the data from your DBCC command. I used #table_name below.
Then fire something similar to the following to populate it.

EXECUTE sp_msforeachdb N'USE ?;
INSERT INTO #table_name
EXECUTE sp_executeSQL N''DBCC fileheader(?)'';';

Then you can select from #table_name to get the fields you want.


Thanks Ed. I tried something like that originally and it doesn't like databases with hyphens in the name, and there are a LOT of columns in the fileheader table, so it would be a pain to create all of them. Unless there's an easier way to do that? I couldn't get a "create table #DBID as (dbcc fileheader)" command to work. I made this loop method to skip system databases and make sure I hit every DB.
Post #1443913
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse