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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




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



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() );';

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)
 )
)

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

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

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;

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();

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.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems