April 23, 2008 at 4:27 pm
Hi,
I would like to know .the command to get the list of sql server instances each server is running. and in each instances..the list of databases
Thanks
February 22, 2011 at 3:36 am
Set NoCount On
Declare @CurrID int,@ExistValue int, @MaxID int, @sql nvarchar(1000)
Declare @TCPPorts Table (PortType nvarchar(180), Port int)
Declare @SQLInstances Table (InstanceID int identity(1, 1) not null primary key,
InstName nvarchar(180),
Folder nvarchar(50),
StaticPort int null,
DynamicPort int null,
Platform int null);
Declare @Plat Table (Id int,Name varchar(180),InternalValue varchar(50), Charactervalue varchar (50))
Declare @Platform varchar(100)
Insert into @Plat exec xp_msver platform
select @Platform = (select 1 from @plat where charactervalue like '%86%')
If @Platform is NULL
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances set Platform=64
End
else
Begin
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances Set Platform=32
End
Declare @Keyexist Table (Keyexist int)
Insert into @Keyexist
Exec xp_regread'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
select @ExistValue= Keyexist from @Keyexist
If @ExistValue=1
Insert Into @SQLInstances (InstName, Folder)
Exec xp_regenumvalues N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL';
Update @SQLInstances Set Platform =32 where Platform is NULL
Select @MaxID = MAX(InstanceID), @CurrID = 1
From @SQLInstances
While @CurrID <= @MaxID
Begin
Delete From @TCPPorts
Select @sql = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPDynamicPorts'''
From @SQLInstances
Where InstanceID = @CurrID
Insert Into @TCPPorts
Exec sp_executesql @sql
Select @sql = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPPort'''
From @SQLInstances
Where InstanceID = @CurrID
Insert Into @TCPPorts
Exec sp_executesql @sql
Select @sql = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPDynamicPorts'''
From @SQLInstances
Where InstanceID = @CurrID
Insert Into @TCPPorts
Exec sp_executesql @sql
Select @sql = 'Exec xp_instance_regread N''HKEY_LOCAL_MACHINE'',
N''SOFTWARE\Wow6432Node\Microsoft\\Microsoft SQL Server\' + Folder + '\MSSQLServer\SuperSocketNetLib\Tcp\IPAll'',
N''TCPPort'''
From @SQLInstances
Where InstanceID = @CurrID
Insert Into @TCPPorts
Exec sp_executesql @sql
Update SI
Set StaticPort = P.Port,
DynamicPort = DP.Port
From @SQLInstances SI
Inner Join @TCPPorts DP On DP.PortType = 'TCPDynamicPorts'
Inner Join @TCPPorts P On P.PortType = 'TCPPort'
Where InstanceID = @CurrID;
Set @CurrID = @CurrID + 1
End
Select serverproperty('ComputerNamePhysicalNetBIOS') as ServerName, InstName, StaticPort, DynamicPort,Platform
From @SQLInstances
Set NoCount Off
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply