SQL Instances and Databases

Question

SQL Instances and Databases

dj_e31337

Valued Member

Points: 68
More actions
June 9, 2011 at 2:27 am

#252466

Is it possible to write a SQL script that returns the SQL instances running on a server and also the databases that are attached to the instances.
This is a starting point:
SELECT name, physical_name AS current_file_location
FROM sys.master_files

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You can't do it with a SQL query, because each instance lives on its own and is not aware of othere instances on the same machine.

You could query the registry with xp_regread (UGLY!) or use a powershell script:

function get-SqlServerList {[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()}

get-SqlServerList

To enumerate the databases you can use a query:

SELECT name

FROM sys.databases

Hope this helps

Gianluca

-- Gianluca Sartori

crazy4sql SSCoach Points: 19590 More actions · Answer 2

you need to use a batch file for this. Logic will be something like :-

1) run this on cmd :- sqlcmd -l > c:\serverlist.txt

you will get server list.

2) create this sql query in a txt file:-

print @@servername

select name from sys.databases(2005 or above)/sysdatbases(sql 2000)

3) Have another batch file which will run the sql query in a loop on the name mentioned in serverlist.txt

----------
Ashish

Rhox Hall of Fame Points: 3462 More actions · Answer 3

Isn't it possible with the registered servers window?

- Create group and place all connections (per instances) in it

- Right click on the group and select 'New query'

- Execute your query...

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Rhox (6/9/2011)
Isn't it possible with the registered servers window?
- Create group and place all connections (per instances) in it
- Right click on the group and select 'New query'
- Execute your query...

That's the best option when you already know all the instances. When you don't, you can discover them with the SQLBrowser service, using the powersehll script or sqlcmd -L as already mentioned.

-- Gianluca Sartori