Get all instances - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Get all instances

Author

Topic

sqldev05
Starting Member

9 Posts

Posted - 2008-04-23 : 14:54:47

Hi,

Can anybody tell me..which command should i use to get the list of all the server names, all the sql server instances in each server and all the databases in each sql server instance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-23 : 15:02:15

sp_databases for databases with their sizes

sqldev05
Starting Member

9 Posts

Posted - 2008-04-23 : 15:07:52

Thank you for your reply.

But can we use a single command where it gives a list of everything.ie..servernames, their instances, and their databases..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 15:13:36

No such single command exists. To do it from T-SQL, you'd have to read the registry via xp_regread. I'd instead recommend using one of the Microsoft tools that finds the servers and instances on the network. Google for these tools.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqldev05
Starting Member

9 Posts

Posted - 2008-04-23 : 15:40:03

if not a single command..
how will i get a list of all the instance names of a single server.
for ex..
using select serverproperty('machinename') and serverproperty('instancename') i will get machiname and instance name..

but how will i get the list of all the instances of each machinename..and all the user databases in that particular instance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 15:41:46

The information is stored in the registry, which can queried in T-SQL via xp_regread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqldev05
Starting Member

9 Posts

Posted - 2008-04-23 : 18:50:33

when i executed this command i am getting a error..ie.parameter to be supplied. I am new to sql server. please help me with this..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 18:52:09

It's extremely hard to help with the very little information that you provided. Please post the exact command and the exact error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqldev05
Starting Member

9 Posts

Posted - 2008-04-23 : 18:57:17

xp_regread

Msg 22001, Level 15, State 0, Line 0
Error executing Read extended stored procedure: Invalid Parameter

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-23 : 18:59:25

You didn't pass it any parameters. Google xp_regread to see how to use it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources