How to run a script against all databases

I want to run a script to get the file details on each database.
Script:
Use DBname
Select * from sysfiles
I want to cycle through the databases using dbname as variable. I tried a
bunch of different ways with no luck. Any help or pointers would be great.
What I'm looking to do is "inventory" my sql servers. I need to come up
with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
I would like to come up with a script (or scripts) that will give me list of
databases and details on databases for that server. I would like to dump
into table and eventually use that to keep track of my servers. Any
thoughts '
thanks in advance
robTry this:
sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
"Rob" wrote:
> I want to run a script to get the file details on each database.
> Script:
> Use DBname
> Select * from sysfiles
> I want to cycle through the databases using dbname as variable. I tried a
> bunch of different ways with no luck. Any help or pointers would be great.
> What I'm looking to do is "inventory" my sql servers. I need to come up
> with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> I would like to come up with a script (or scripts) that will give me list of
> databases and details on databases for that server. I would like to dump
> into table and eventually use that to keep track of my servers. Any
> thoughts '
> thanks in advance
> rob|||This works great... yet another undocumented MS trick.
One question. One of my tables has a name that starts with a number, 3.
This seems to choke the script. Any ideas on how to resolve.
thanks again for great tip.
rob
"CLM" wrote:
> Try this:
> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
> "Rob" wrote:
> > I want to run a script to get the file details on each database.
> > Script:
> >
> > Use DBname
> > Select * from sysfiles
> >
> > I want to cycle through the databases using dbname as variable. I tried a
> > bunch of different ways with no luck. Any help or pointers would be great.
> > What I'm looking to do is "inventory" my sql servers. I need to come up
> > with a Disaster Recovery Plan and I have 10 + sql servers I need to document.
> > I would like to come up with a script (or scripts) that will give me list of
> > databases and details on databases for that server. I would like to dump
> > into table and eventually use that to keep track of my servers. Any
> > thoughts '
> >
> > thanks in advance
> > rob|||Surround the question mark in square brackets [?]
A number is not a valid starting character for a regular identifier.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:BC0CA6B4-0A30-4D45-89F7-EC46DCB1B15A@.microsoft.com...
> This works great... yet another undocumented MS trick.
> One question. One of my tables has a name that starts with a number, 3.
> This seems to choke the script. Any ideas on how to resolve.
> thanks again for great tip.
> rob
>
> "CLM" wrote:
>> Try this:
>> sp_MSforeachdb @.command1="use ?; Select * from sysfiles; "
>> "Rob" wrote:
>> > I want to run a script to get the file details on each database.
>> > Script:
>> >
>> > Use DBname
>> > Select * from sysfiles
>> >
>> > I want to cycle through the databases using dbname as variable. I
>> > tried a
>> > bunch of different ways with no luck. Any help or pointers would be
>> > great.
>> > What I'm looking to do is "inventory" my sql servers. I need to come
>> > up
>> > with a Disaster Recovery Plan and I have 10 + sql servers I need to
>> > document.
>> > I would like to come up with a script (or scripts) that will give me
>> > list of
>> > databases and details on databases for that server. I would like to
>> > dump
>> > into table and eventually use that to keep track of my servers. Any
>> > thoughts '
>> >
>> > thanks in advance
>> > rob