The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,053 other followers

small script to list SQL Server databases and their data/log files

Posted by jpluimers on 2010/06/29

For my own memory: a small script to list SQL Server databases and their data/log files.

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S .\SQLEXPRESS -E -Q "select name from sys.databases"

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S .\SQLEXPRESS -E -Q "select name,filename from model.sys.sysfiles"

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S .\SQLEXPRESS -E -Q "select name,filename from master.sys.sysfiles"

"%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\OSQL.EXE" -S .\SQLEXPRESS -E -Q "exec sp_msforeachdb 'select ''?'',name,filename from ?.dbo.sysfiles'"

SQL Server maintains a list of databases in the system table sys.databases.

It also has an unsupported procedure sp_msforeachdb that you can use to investigate for instance performance issues.

In the above example, sp_msforeachdb just builds a statement (filling the ? with the database name) to execute.

–jeroen

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: