SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC on a detached MDF file


DBCC on a detached MDF file

Author
Message
Paul Randal
Paul Randal
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3745 Visits: 1717
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
mike.hamilton721
mike.hamilton721
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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!!!
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40626 Visits: 38567
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)' + ')').

Cool
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)
mike.hamilton721
mike.hamilton721
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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!!
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40626 Visits: 38567
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.

Cool
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)
mike.hamilton721
mike.hamilton721
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40626 Visits: 38567
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.

Cool
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)
mike.hamilton721
mike.hamilton721
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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!
Ed Wagner
Ed Wagner
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17019 Visits: 10089
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
mike.hamilton721
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search