SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


List Installed R Packages on SQL Server

As data scientiest if you want to get the list of all installed R packages on SQL Server instance, there a a few methods you can choose. In this SQL database R script tutorial, I want to share a few methods you can choose to display installed R package list on your SQL Server database instance. Checking previously installed R packages before you install a new R package on your SQL Server Machine Learning Services may save you from double work.


List R Packages using SQL Server Management Studio

For SQL developers the easiest method to display all R packages as a list is running installed.packages() R command by executing following SQL sp_execute_external_script statement.

EXEC sp_execute_external_script
 @language = N'R',
 @script = N'OutputDataSet <- as.data.frame( installed.packages() );';
Code

When you execute above SQL sp_execute_external_script external script, all of the R packages installed on that SQL Server Machine Learning Services will be listed as seen in below screenshot.

installed R packages list on SQL Server

If you need to get the column names, following SQL script can be executed:

EXEC sp_execute_external_script
 @language = N'R',
 @script = N'OutputDataSet <- as.data.frame( installed.packages() );'
WITH RESULT SETS (
 (
  Package nvarchar(max),
  LibPath nvarchar(max),
  "Version" nvarchar(max),
  "Priority" nvarchar(max),
  Depends nvarchar(max),
  Imports nvarchar(max),
  LinkingTo nvarchar(max),
  Suggests nvarchar(max),
  Enhances nvarchar(max),
  License nvarchar(max),
  License_is_FOSS nvarchar(max),
  License_restricts_use nvarchar(max),
  OS_type nvarchar(max),
  MD5sum nvarchar(max),
  NeedsCompilation nvarchar(max),
  Built nvarchar(max)
 )
)
Code

As SQL developers can see the R packages installed on current SQL Server list has now column names at the top.

SQL Server R packages list including column names

If you want to store the output R packages list in a database table, one of the following methods can be used.
The first way requires a loop back linked server definition.
I mean, you have to create a linked server which is pointing to itself and using the current user's security context.
This method is known as Select from Stored Procedure using OpenQuery

Then following SELECT command can be executed to select data from stored procedure (sp_execute_external_script) and create temp table to store result set in the current database.

SELECT *
INTO ##InstalledRPackagesList
FROM OPENQUERY([.\SQL2019CTP21], '
 EXEC sp_execute_external_script
  @language = N''R'',
  @script = N''OutputDataSet <- as.data.frame( installed.packages() );''
 WITH RESULT SETS (
  (
   Package nvarchar(max),
   LibPath nvarchar(max),
   "Version" nvarchar(max),
   "Priority" nvarchar(max),
   Depends nvarchar(max),
   Imports nvarchar(max),
   LinkingTo nvarchar(max),
   Suggests nvarchar(max),
   Enhances nvarchar(max),
   License nvarchar(max),
   License_is_FOSS nvarchar(max),
   License_restricts_use nvarchar(max),
   OS_type nvarchar(max),
   MD5sum nvarchar(max),
   NeedsCompilation nvarchar(max),
   Built nvarchar(max)
  )
 )
') as [OpenQuery]
select * from ##InstalledRPackagesList
drop table ##InstalledRPackagesList
Code

Other method to store output of sp_execute_external_script external script procedure is more easy compared with above solution.
Database developers must first create the table on the database to store the installed R packages list data.
This table's field list must match the columns in the WITH RESULTS SET clause.
Here is the SQL code that can be used.

Create Table RPackagesList (
 Package nvarchar(max),
 LibPath nvarchar(max),
 "Version" nvarchar(max),
 "Priority" nvarchar(max),
 Depends nvarchar(max),
 Imports nvarchar(max),
 LinkingTo nvarchar(max),
 Suggests nvarchar(max),
 Enhances nvarchar(max),
 License nvarchar(max),
 License_is_FOSS nvarchar(max),
 License_restricts_use nvarchar(max),
 OS_type nvarchar(max),
 MD5sum nvarchar(max),
 NeedsCompilation nvarchar(max),
 Built nvarchar(max)
)

Insert Into RPackagesList
EXEC sp_execute_external_script
 @language = N'R',
 @script = N'OutputDataSet <- as.data.frame( installed.packages() );'
select * from RPackagesList
Code

If as a data scientist, you are interested only for certain columns then below SQL code and R script can be combined to display desired output

For example, assume that the data scientist is only concerns with package name, version of the R package and the built number. These are the columns in 1st, 3rd and 16th order.
R script code should be modified to return only those columns by using [,c(1,3,16)] column selection option.
Please note, if you are interested all columns between 5th and 10th columns then you can use [,c(5:10)]

Database developers will realize, I have a table with all columns are mapped to the output of the sp_execute_external_script stored procedure.
INSERT INTO command from stored procedure will store output of the R-script into database table

Create Table RPackagesList
(
 Package nvarchar(max),
 "Version" nvarchar(max),
 Built nvarchar(max)
);

Insert Into RPackagesList
EXEC sp_execute_external_script
 @language = N'R',
 @script = N'
  installedRPackages <- data.frame( installed.packages() )
  OutputDataSet <- installedRPackages[,c(1,3,16)];';

select * from RPackagesList;
Code

SQL Server database table for installed R packages list


Installed R Packages List on RTerm

Another option for data engineers to get all of R packages installed on a SQL Server instance is using the RTerm, R script terminal tool

Rterm or R for Windows front-end tool is accessible using R.exe executable file in related SQL Server instance's R_SERVICES bin folder

SQL Server R for Windows Rterm tool

Launch Rterm software by running the R.exe file. When the application is launched, type installed.packages() command on the command line and press Enter.

Rterm to list installed R packags on SQL Server


List R Packages using Library() Command

While using Rterm another option to list all installed R packages on SQL Server is executing the library() R command as seen in below screenshot.

library();
Code

list all R packages in SQL Server library

If you test the library() command on your SQL Server 2019 instance you will see that a popup screen will be displayed and the R packages installed on that instance will be listed with the default library path.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.