SQL Server Instances on a server

Question

I was wondering if I can have a script to find out

1. Number of instances installed on a server
2. Available databases in each instance
3. Memory that has been used by each instance.

Answer 1

I guess my answer can add a value and would be exact as to what you are looking for.

1.Number of instances installed on a server

You can query registry for SQL server instance information but MS provides you tool also which would produce result in report format for all instances installed on machine. You can run SQL Server discovery report IF you have any instance of SQL server 2008 and above installed. Its very easy to run and would list out all SQL server 2000,2005..2014 instances installed on your system

You can go to start..all programs..Microsoft SQL Server..Configuration tool..Then click on SQL Server Installation Center. A windows will pop up on left side of that window is option called as tools select it and you would see below screen.

Now click on Installed SQL Server discovery report this would generate report

3.Memory that has been used by each instance.

Below query would give memory utilized by SQL server instance and would work if SQL Server is 2008 and above

select

(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,     
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,     
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,     
process_physical_memory_low,     
process_virtual_memory_low     
from sys. dm_os_process_memory

For SQL server 2005 This Blogs.msdn article has script

Answer 2

to get number of instances installed you can get it using command line with following command

SQLCMD -L

for getting databases in a particular instance you can get it using

sqlcmd -E -S SERVER\INSTANCE -Q "sp_databases"

OR

SELECT * FROM sys.databases

to get memory uses this may help you

DECLARE @total_buffer INT;

SELECT @total_buffer = cntr_value
   FROM sys.dm_os_performance_counters 
   WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
   AND counter_name = 'Total Pages';

;WITH src AS
(
   SELECT 
       database_id, db_buffer_pages = COUNT_BIG(*)
       FROM sys.dm_os_buffer_descriptors
       --WHERE database_id BETWEEN 5 AND 32766
       GROUP BY database_id
)
SELECT
   [db_name] = CASE [database_id] WHEN 32767 
       THEN 'Resource DB' 
       ELSE DB_NAME([database_id]) END,
   db_buffer_pages,
   db_buffer_MB = db_buffer_pages / 128,
   db_buffer_percent = CONVERT(DECIMAL(6,3), 
       db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

for getting information regarding memory you can go through the following link SQL-Server Memory Usage

Reference from one of the Stackoverflow pages

Answer 3

Instance names can be obtained by using something like the following script:

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL'

Basically you'll have to check the registry. (At least that's how it used to be a few years ago when I actually wrote the script this snippet is coming from).

For databases, try checking sys.databases.

I'm not sure memory usage counters would be available through a script, though Powershell might be able to offer a solution for that part.