Home
> Query, SQL > BISQL # 73 – How To find list of SQL Server Instances Installed on any machine By Query
BISQL # 73 – How To find list of SQL Server Instances Installed on any machine By Query
Hi folks
Today suddenly in I need to find out which SQL Server instance is installed on my machine , while researching this I found out query and one undocumented sp
We can use the xp_regread undocumented extended stored procedure to read from the registry via Transact-SQL.
Syntax for xp_regread
EXECUTE xp_regread [@rootkey=]'rootkey', [@key=]'key' [, [@value_name=]'value_name'] [, [@value=]@value OUTPUT]
Following query I have used to find instance
you can use this query as it is n paste in your query plan n see result :
DECLARE @GetInstances TABLE ( Value nvarchar(100), InstanceNames nvarchar(100), Data nvarchar(100) ) Insert into @GetInstances EXECUTE xp_regread @rootkey = 'HKEY_LOCAL_MACHINE', @key = 'SOFTWARE\Microsoft\Microsoft SQL Server', @value_name = 'InstalledInstances' Select InstanceNames from @GetInstances
Following is output for above query
After some more digging I just found following link which have solve all my problems but result was quite surprising
MSDN Blog:How to list all the installed SQL Server on the Server using TSQL
Result after above links
And if you want this Details via .net go to Following link
MSDN Blog :Getting SQL Server registry settings via SQLCLR table-valued user-defined function
Hope this Helps !!!!
Thanks for visiting my blog !!
Hope you will like this script on How To find list of SQL Server Instances Installed on a machine By Query
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
Advertisement
Leave a Reply Cancel reply
Search Here !!
Join 2,993 other subscribers
Mentalist Blogs
- Blog on Beyond Relational's
- Business Intelligence Mentalist BI World
- Link Resource Blog Daily Intersting links
- Microsoft Mentalist MVC,WCF
My Recent Post
- My session in SQL Saturday #859 Tampa – Learn Power BI Report Server Enterprise Architecture, Tools to publish reports and Best Practice
- BI SQL # 281 : All Power BI Dashboard Query consolidated & Information
- SQLSaturday #699 Tampa – Power BI-Create a Dashboard from Zero to Hero
- Product Review – Stellar Phoenix SQL Database Repair
- BI SQL # 280: Get all SQL Server instance information for at a glance
- BI SQL # 279 : Get all last back up date and time with physical bak file path
- Image Post–SQL 2016, Azure and Everything around it !
- BI SQL # 278 : SQL Server DBA Scripts : How to Quick Search Anything in Database in SQL Server
- 2015 in review–BI,SQL Blog and Power BI (About me) , Happy New Year 2016
- Merry Christmas & Gifts of Slides on SQL Coding Conventions, Best Practices, Tips and Programming Guidelines for TSQL
- SQL Server 2016–Getting Started, Download, Installation and First Look
- Back to origin, SQL Saturday Atlanta event day updates – SQLSaturday #392
- 2014 in review ! Wish you all Happy New Year ! Happy SQL Scripting !
- BI SQL # 277 : SQL Server DBA Scripts : Find TOP IO Stored Procedure
- BI SQL # 276 : SQL Server DBA Scripts : Find all DB IO with Rank
- BI SQL # 275 : SQL Server DBA Scripts : Find Session details
- BI SQL # 274 : SQL Server DBA Scripts : Space used by version store
- BI SQL # 269 : SQL Server DBA Scripts : Find all server running in n/w
- BI SQL # 273 : SQL Server DBA Scripts : Core in server
- BI SQL # 272 : SQL Server DBA Scripts : Find Connectivity information
- Microsoft Power Business Intelligence (Power BI) Workshop in Microsoft Mumbai Office
- BI SQL # 271 : SQL Server DBA Scripts : Display all Server Information
- BI SQL # 270 : SQL Server DBA Scripts : Find the SQL Server Protocols Status
- BI SQL # 268 : SQL Server DBA Scripts : Find Statistics Info for all tables
- BISQL – Laymen to SQL Developer # 45 – Security & Integrity #2 – Authorization, Granting of Privileges, Security Specification in SQL
- BI SQL # 267 : SQL Server DBA Scripts : Identity Columns: Are You Nearing The Limits?
- BI SQL # 266 : SQL Server DBA Scripts : Find Cumulative IO Stats
- BI SQL # 265 : SQL Server DBA Scripts : Compare Data Table
- BISQL – Laymen to SQL Developer # 44 – Security & Integrity #1 – Introduction, Security & Integrity Violations
- BI SQL # 264 : SQL Server DBA Scripts : Monitoring Database Backups
- BI SQL # 263 : SQL Server DBA Scripts : All Database Information
- BI SQL # 262 : SQL Server DBA Scripts : Find IP Address of machine
- BI SQL # 261 : SQL Server DBA Scripts : Find If any database is Offline
- BISQL – Laymen to SQL Developer # 43 – Relational Databases #2 – Object Oriented Data Model, Object Oriented Databases, Object Relational Database Management Systems
- BI SQL # 260 : SQL Server DBA Scripts : Find last backup details of all databases in a server
- BI SQL # 259 : SQL Server DBA Scripts : Database current size, auto growth setting and file location
- BI SQL # 258 : SQL Server DBA Scripts : Index Definition Audit Script
- BISQL – Laymen to SQL Developer # 42 – Relational Databases #1 – Introduction, Basics of Object Oriented Design (OOD)
- BI SQL # 257 : SQL Server DBA Scripts : SQL Agent Job Script
- BI SQL # 256 : SQL Server DBA Scripts : Check VLF Counts in Database
- BI SQL # 255 : SQL Server DBA Scripts : Find Recently Executed Stored Procedures
- BI SQL # 254 : SQL Server DBA Scripts : Estimating Rows per Page
- BISQL – Laymen to SQL Developer # 38 – Recovery Techniques #1 – Introduction, Techniques for Recovering
- BI SQL # 253 : SQL Server DBA Scripts : Permissions Script
- BI SQL # 252 : SQL Server DBA Scripts : Find SQL Server Start time and End time
- BI SQL # 251 : SQL Server DBA Scripts : List SQL Script to find the Roles assigned to the SQL Logins including Create_data and modify_date
- BISQL – Laymen to SQL Developer # 41 – Distributed Databases #2 – Data Fragmentation, Replication, and Allocation Techniques for Distributed Database Design
- BI SQL # 250 : SQL Server DBA Scripts : List all roles of the current database with their members
- BI SQL # 249 : SQL Server DBA Scripts : Finding tempdb Size and Growth Parameters
- BI SQL # 248 : SQL Server DBA Scripts : Find Tables with Foreign Key Constraint in Database
- BI SQL # 247 : SQL Server DBA Scripts : List all the Databases on Server
- BISQL – Laymen to SQL Developer # 40 – Distributed Databases #1 – Introduction, Client-Server Model
- BI SQL # 246 : SQL Server DBA Scripts : SQL DMV to find the details of current processes running like percent completed!!
- BI SQL # 245 : SQL Server DBA Scripts : Show Running Queries on SSMS
- BI SQL # 244 : SQL Server DBA Scripts : Restore Database Through script
- BISQL – Laymen to SQL Developer # 39 – Recovery Techniques #2 – Deferred Update, Immediate Update
- BI SQL # 243 : SQL Server DBA Scripts : Finding all the objects that have been modified in the last N days in database
- BI SQL # 242 : SQL Server DBA Scripts : Shrink Log file through Script
- BI SQL # 241 : SQL Server DBA Scripts : Find SQL Server Service Account
- BI SQL # 240 : SQL Server DBA Scripts : Foreign Key & Primary Key Relationship All
- BI SQL # 239 : SQL Server DBA Scripts : C. Compare Database
- BI SQL # 238 : SQL Server DBA Scripts : B. Compare Database
- BI SQL # 237 : SQL Server DBA Scripts : A. Compare Database
- BISQL – Laymen to SQL Developer # 37 – Concurrency Control Techniques #2 – The Two Phase Locking Protocol
- BI SQL # 236 : SQL Server DBA Scripts : Find list of stored procedure with content info
- BI SQL # 235 : SQL Server DBA Scripts : Identify Structure of a Table
- BI SQL # 234 : SQL Server DBA Scripts : Get Distinct Value for each column from Table
- BI SQL # 233 : SQL Server DBA Scripts : Trim all column of given table
- BISQL – Laymen to SQL Developer # 36 – Concurrency Control Techniques #1 – Introduction, Types of Locks: Locking Technique for Concurrency Control
- BI SQL # 232 : SQL Server DBA Scripts : Backup Old And Restore new Database through Script
- BI SQL # 231 : SQL Server DBA Scripts : Create Primary Key for all Tables
- BI SQL # 230 : SQL Server DBA Scripts : Disable Data Compression
- BISQL – Laymen to SQL Developer # 35 – Transaction Processing #2 – Desirable Properties of Transactions, Concepts of Schedule History, recoverable cascading rollback and script schedules
- BI SQL # 229 : SQL Server DBA Scripts : Enable Data Compression
- BI SQL # 228 : SQL Server DBA Scripts : Create Alter Script Dynamically
- BI SQL # 227 : SQL Server DBA Scripts : Display Table info with Index and Rows
- BI SQL # 226 : SQL Server DBA Scripts : Display Name And Size of Drive on Machine
- BISQL – Laymen to SQL Developer # 34 – Transaction Processing #1 – Introduction, Transaction Processing Concepts, Transactions in a Multiuser System
- BI SQL # 225 : SQL Server DBA Scripts : Display Space Details for Given database
- BI SQL # 224 : SQL Server DBA Scripts : Display Memory Configuration
- BI SQL # 223 : SQL Server DBA Scripts : Get All table Size Information
- BI SQL # 222 : SQL Server DBA Scripts : Find Tables without Primary Key
- BISQL – Laymen to SQL Developer # 33 – Functional Dependencies and Normalization For Relational Databases #4 – Fourth Normal Form (4NF), Normalization using Join Dependencies
- BI SQL # 221 : SQL Server DBA Scripts : Find Specific text in database
- BI SQL # 220 : SQL Server DBA Scripts : Find Any Column with Specific DataType
- BI SQL # 219 : SQL Server DBA Scripts : Add mdf file as new DB
- BISQL – Laymen to SQL Developer # 32 – Functional Dependencies and Normalization For Relational Databases #3 – Boyce Codd Normal Form (BCNF)
- BI SQL # 218 : SQL Server DBA Scripts : Identify size of all non clustered indexes
- BI SQL # 217 : SQL Server DBA Scripts : Display Configuration Settings
- BI SQL # 216 : SQL Server DBA Scripts : Searching For Specific column for all Tables in database
- BI SQL # 215 : SQL Server DBA Scripts : Making IDENTITY column to NON-IDENTITY & vice versa
- BISQL – Laymen to SQL Developer # 31 – Functional Dependencies and Normalization For Relational Databases #2 – Levels of Relation Schema & Functional Dependency, Normal Forms Based on Primary Keys
- BI SQL # 214 : SQL Server DBA Scripts : Identify Number of Lines of code for each database Object
- BI SQL # 213 : SQL Server DBA Scripts : Reads and Writes in Database
- BI SQL # 212 : SQL Server DBA Scripts : Searching String in Steps of JOB
- BISQL – Laymen to SQL Developer # 30 – Functional Dependencies and Normalization For Relational Databases #1 – Introduction, Information Design Guide Lines for Relational Database
- BI SQL # 211 : SQL Server DBA Scripts : How to Drop All Table from database through Script
- BI SQL # 210 : SQL Server DBA Scripts : Reset Identity Column
- BI SQL # 209 : SQL Server DBA Scripts : Displaying Table Name Having Primary Key with Column Name
- BI SQL # 208 : SQL Server DBA Scripts : Identifying Procedures Taking More Execution time
- BI SQL # 207 : SQL Server DBA Scripts : Identify Free space in Temp DB
- BI SQL # 206 : SQL Server DBA Scripts : Identify IO Stall For Each DB
- BI SQL # 205 : SQL Server DBA Scripts : Identify High Latch Waits
- BISQL – Laymen to SQL Developer # 29 – SQL #3 – The Create Table Statement, Specifying of Constraints & Default Values of SQL
- BI SQL # 204 : SQL Server DBA Scripts : Identify Top Nth waits With I / O Specification
- BI SQL # 203 : SQL Server DBA Scripts : Identify Top Nth heavy I / O Operation
- BI SQL # 202 : SQL Server DBA Scripts : How to Delete Database through Script
- BI SQL # 201 : SQL Server DBA Scripts : Job Failure Notification
- BISQL – Laymen to SQL Developer # 28 – SQL #2 – Multi Table Queries, Data Manipulation Language
- BI SQL # 200 : SQL Server DBA Scripts : Various SQL Server IO Statistics
- BI SQL # 199 : SQL Server DBA Scripts : Temporary Tables and Their Size
- BI SQL # 198 : SQL Server DBA Scripts : Stored procedure Execution statistics
- BISQL – Laymen to SQL Developer # 27 – SQL#1 – Introduction, Tuple Relational Calculus
- BI SQL # 197 : SQL Server DBA Scripts : Overview of all objects created in SSRS ordered by the folder hierarchy
- BI SQL # 197 : SQL Server DBA Scripts : Overview of all objects created in SSRS ordered by the folder hierarchy
- BI SQL # 196 : SQL Server DBA Scripts : Script to VLF count for all databases
- BI SQL # 195 : SQL Server DBA Scripts : SQL Server replication publication information
- BISQL – Laymen to SQL Developer # 26 – Relational Algebra #2 – Update Operation on Relations, The Relational Algebra
- BI SQL # 192 : SQL Server DBA Scripts : Search string or pattern in SQL module definition
- BI SQL # 191 : SQL Server DBA Scripts : Search for a string in all tables of SQL Server Database
- BISQL – Laymen to SQL Developer # 25 – Relational Algebra #1 – Introduction, Relational Model Constraints
- BI SQL # 190 : SQL Server DBA Scripts : Script to find the partition key of a table
- BI SQL # 189 : SQL Server DBA Scripts : Run DBCC CHECKDB on all databases
- BI SQL # 187 : SQL Server DBA Scripts : Query Master Data with Details as Comma Separated Value
- BISQL – Laymen to SQL Developer # 24 – Entity-Relationship Model #3 – Components of an ER Model, Constraints on Relationships Types
- BI SQL # 186 : SQL Server DBA Scripts : Find the details of the given report name by querying Report Server Database
- BI SQL # 185 : SQL Server DBA Scripts : Programmability Objects and their Parameters
- BI SQL # 184 : SQL Server DBA Scripts : Pending IO Requests
- BISQL – Laymen to SQL Developer # 23 – Entity-Relationship Model #2 – ER Model Concept with an example
- BI SQL # 183 : SQL Server DBA Scripts : Pages per object in buffer pool
- BI SQL # 182 : SQL Server DBA Scripts : Page Life Expectancy
- BI SQL # 181 : SQL Server DBA Scripts : Overview of Locks per Database and Type
- BI SQL # 180 : SQL Server DBA Scripts : List log file information for all the databases on the server
- BISQL – Laymen to SQL Developer # 22 – Entity-Relationship Model #1 – Introduction, Conceptual Data model for Database Design
- BI SQL # 179 : SQL Server DBA Scripts : Log Growth Rate
- BI SQL # 178 : SQL Server DBA Scripts : List expensive queries MAIN
- BI SQL # 177 : SQL Server DBA Scripts : List expensive queries CHILD
- BISQL – Laymen to SQL Developer # 21 – Index Structures of Files #2 – Multilevel Indexes, B+Tree Index Files, B-Tree’
- BI SQL # 176 : SQL Server DBA Scripts : List for all SSRS reports that use data sources and datasets with the command text
- BI SQL # 175 : SQL Server DBA Scripts : List Database Roles and Members with Server Login
- BI SQL # 174 : SQL Server DBA Scripts : List connection strings of all SSRS Shared Data sources
- BI SQL # 173 : SQL Server DBA Scripts : List all trigger with SQL definition
- BISQL – Laymen to SQL Developer # 20 – Index Structures of Files #1 – Introduction, Primary Index, Clustering Index
- BI SQL # 172 : SQL Server DBA Scripts : List all SSRS subscriptions
- BI SQL # 171 : SQL Server DBA Scripts : List all SSRS report parameters with definition
- BI SQL # 170 : SQL Server DBA Scripts : List all SSIS packages stored in msdb database
- BISQL – Laymen to SQL Developer # 19 – Record Storage and Primary File Organization #4 – Hashing Techniques
- BI SQL # 169 : SQL Server DBA Scripts : List all Server Permissions
- BI SQL # 168 : SQL Server DBA Scripts : List all Objects and Indexes per Filegroup or Partition
- BI SQL # 167 : SQL Server DBA Scripts : List all Locks of the Current Database
Categories
- A to Z SQL Command (9)
- Free Tool (8)
- Link (248)
- Microsoft SQL Server (204)
- MSBI (205)
- New blog (5)
- NMUG (3)
- Optimization (204)
- PASS (3)
- Query (270)
- Query, (4)
- Script (200)
- SQL (148)
- SQL Mentalist (209)
- SQL PraRup (193)
- SQL Query (204)
- SQL Server (199)
- SQL Server 2014 (6)
- SQL Server 2016 (7)
- SQL Tips (15)
- SQL Tips and Tricks (17)
- SQL Tricks (41)
- Technology (6)
- Technology, (203)
- TSQL (176)
- Vishal Pawar (214)
- ZeroToBasic (15)
SQL Server Mentalist 
I am getting on to each topics : really good and interesting – thanks for sharing …
Hey thanx a Lot Praveen !I am glad u liked it n reall thanx for reply !