DBCC on a detached MDF file

Question

DBCC on a detached MDF file

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

mike.hamilton721 Mr or Mrs. 500 Points: 500 More actions · Answer 1

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

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.

mike.hamilton721 Mr or Mrs. 500 Points: 500 More actions · Answer 3

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!

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

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

mike.hamilton721 Mr or Mrs. 500 Points: 500 More actions · Answer 5

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.

mike.hamilton721 Mr or Mrs. 500 Points: 500 More actions · Answer 6

Tried one more thing.. Not sure if I'm closer or farther away:

insert into #DBID (LogicalName,BindingID)

EXEC ('DBCC fileheader('+@dbid+')')

But that gives me "column name or number of supplied values does not match table definition", but it does pull in all the names (from the first part of my query), but LogicalName and BindingID are NULL all the way down. Does this not just insert those 2 columns from the EXEC?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

mike.hamilton721 (4/18/2013)
Tried one more thing.. Not sure if I'm closer or farther away:
insert into #DBID (LogicalName,BindingID)
EXEC ('DBCC fileheader('+@dbid+')')
But that gives me "column name or number of supplied values does not match table definition", but it does pull in all the names (from the first part of my query), but LogicalName and BindingID are NULL all the way down. Does this not just insert those 2 columns from the EXEC?

Your table that you are inserting the data into has to account for ALL the columns returned by the DBCC command.

mike.hamilton721 Mr or Mrs. 500 Points: 500 More actions · Answer 8

Lynn Pettis (4/18/2013)
mike.hamilton721 (4/18/2013)
Tried one more thing.. Not sure if I'm closer or farther away:
insert into #DBID (LogicalName,BindingID)
EXEC ('DBCC fileheader('+@dbid+')')
But that gives me "column name or number of supplied values does not match table definition", but it does pull in all the names (from the first part of my query), but LogicalName and BindingID are NULL all the way down. Does this not just insert those 2 columns from the EXEC?
Your table that you are inserting the data into has to account for ALL the columns returned by the DBCC command.

Thank you! Created a table with all columns now (I'll select what I need later). But now I get "error converting data type into to nvarchar". As far as I can determine, it's not a column I created, it's the EXEC ('DBCC fileheader('+@dbid+')') part. Since it's a number, and I add 1 to it to cycle through all DBs (since msforeach db can skip DBs or have extras). I guess my question is: How do I use a db_id variable there that cycles through each? Is that possible? Thank you!