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)' + ')').