Package 'MSSQL'

Tools to Work with Microsoft SQL Server Databases via RODBC

Description

Tools that extend the functionality of the RODBC package to work with Microsoft SQL Server databases. Makes it easier to browse the database and examine individual tables and views.

Details

Browse database:

dbOverview	Dimensions and column names
dbStorage	Storage size
dbTime	Time created and modified

Browse table:

tableDim	Dimensions
tableHead	First rows
tableNcol	Number of columns
tableNrow	Number of rows
tableOverview	Data types and dimensions

Helper functions:

tableQuote

Quote table name

Note

browseVignettes() shows a vignette with implementation notes.

Author(s)

Arni Magnusson.

See Also

This package complements the RODBC package and does not replace the standard query methods.

For example, the user may find dbOverview and tableOverview more convenient than the underlying sqlTables and sqlColumns, but to query the database sqlQuery or sqlFetch are still used in the normal way.

---

Overview of Tables and Views

Description

Get dimensions and first few column names of tables and views in a database.

Usage

dbOverview(channel, schema = "dbo", dim = TRUE, peek = 2, ...)

Arguments

channel	an RODBC connection.
schema	database schema.
dim	whether to calculate the number of rows and columns for each table/view.
peek	how many column names to show. The value FALSE has the same effect as zero.
...	passed to sqlTables.

Details

The dim = FALSE option results in faster computation, but the ⁠Rows⁠ and ⁠Cols⁠ columns will only contain NA values. Similarly, the peek = FALSE results in faster computation, but the ⁠First⁠ column will only contain NA values. These options can be useful to get a quick overview of a large database.

Value

Data frame containing six columns:

Name	name of table/view.
Schema	database schema.
Type	type of table/view.
Rows	number of rows.
Cols	number of columns.
First	first column names.

See Also

sqlTables is the underlying function used to get the list of tables/views, tableDim is used to count rows and columns, and sqlColumns is used to peek at the first column names.

dbStorage shows the storage size of tables and dbTime shows the time when tables/views were created and last modified.

MSSQL-package gives an overview of the package.

Examples

## Not run: 
con <- odbcConnect("myDatabase")

dbOverview(con)

dbOverview(con, dim=FALSE, peek=FALSE)

## End(Not run)

---

Storage Size

Description

Get storage size of tables in a database.

Usage

dbStorage(channel, total = TRUE, used = FALSE, unused = FALSE)

Arguments

channel	an RODBC connection.
total	whether to calculate total storage size.
used	whether to calculate used storage size.
unused	whether to calculate unused storage size.

Value

Data frame containing the following columns:

Name	name of table/view.
Schema	database schema.
Type	type of table/view.
Rows	number of rows.
Cols	number of columns.

In addition, any of the following columns, depending on which of total, used, and unused are TRUE:

TotalKB	total storage size.
UsedKB	used storage size.
UnusedKB	unused storage size.

Note

Based on https://stackoverflow.com/questions/7892334.

See Also

sqlQuery is the underlying function used to query ⁠sys.tables⁠, ⁠sys.indexes⁠, ⁠sys.partitions⁠, ⁠sys.allocation_units⁠, and ⁠sys.schemas⁠.

dbOverview shows the dimensions of tables/views and the first column names, and dbTime shows the time when tables/views were created and last modified.

object.size is the base function to return the storage size of objects inside the R workspace.

MSSQL-package gives an overview of the package.

Examples

## Not run: 
con <- odbcConnect("myDatabase")

dbOverview(con)

dbOverview(con, dim=FALSE, peek=FALSE)

## End(Not run)

---

Time Created and Modified

Description

Get time information about tables and views: when they were created and when they were last modified.

Usage

dbTime(channel)

Arguments

channel

an RODBC connection.

Value

Data frame containing five columns:

Name	name of table/view.
Schema	database schema.
Type	type of table/view.
Created	time created.
Modified	time last modified.

See Also

sqlQuery is the underlying function used to query ⁠sys.tables⁠ and ⁠sys.views⁠.

dbOverview shows the dimensions of tables/views and the first column names, and dbStorage shows the storage size of tables.

Sys.time is the base function to show the current time.

MSSQL-package gives an overview of the package.

Examples

## Not run: 
con <- odbcConnect("myDatabase")

dbTime(con)

## End(Not run)

---

Table Dimensions

Description

Return the number of rows and columns in a database table.

Usage

tableDim(channel, sqtable)

Arguments

channel	an RODBC connection.
sqtable	a database table or view.

Value

Vector of length two, containing the number of rows and columns.

See Also

tableNrow and tableNcol are the underlying functions to get the number of rows and columns in a database table.

dim is the base function to return the dimensions for data frames inside the R workspace.

MSSQL-package gives an overview of the package.

Examples

## Not run: 
con <- odbcConnect("myDatabase")

tableDim(con, "sysusers")

## End(Not run)

---

First Rows

Description

Return the first rows of a database table.

Usage

tableHead(channel, sqtable, n = 3)

Arguments

channel	an RODBC connection.
sqtable	a database table or view.
n	number of rows to get.

Value

Data frame with the first n rows of the database table or view.

Note

This function can be used to examine the structure of a table or view, along with some example data values.

See Also

sqlQuery with tableQuote are the underlying functions used to query the table/view.

head is the base function to return the first parts of an object inside the R workspace.

tableOverview shows the data types and dimensions of a database table.

MSSQL-package gives an overview of the package.

Examples

## Not run: 
con <- odbcConnect("myDatabase")

tableHead(con, "sysusers")

t(tableHead(con, "sysusers", 1))

## End(Not run)

---

Number of Columns

Description

Return the number of columns in a database table.

Usage

tableNcol(channel, sqtable)

Arguments

channel	an RODBC connection.
sqtable	a database table or view.

Value

Number of columns as integer.

See Also

tableDim and tableNrow also return the dimensions of a database table.

sqlColumns is the underlying function used to examine the table columns.

ncol is the base function to return the number of columns for data frames inside the R workspace.

MSSQL-package gives an overview of the package.

Examples

## Not run: 
con <- odbcConnect("myDatabase")

tableNcol(con, "sysusers")

## End(Not run)

---

Number of Rows

Description

Return the number of rows in a database table.

Usage

tableNrow(channel, sqtable)

Arguments

channel	an RODBC connection.
sqtable	a database table or view.

Value

Number of rows as integer.

See Also

tableDim and tableNcol also return the dimensions of a database table.

sqlQuery is the underlying function used to examine the table rows.

nrow is the base function to return the number of rows for data frames inside the R workspace.

MSSQL-package gives an overview of the package.

Examples

## Not run: 
con <- odbcConnect("myDatabase")

tableNrow(con, "sysusers")

## End(Not run)

---

Data Types and Dimensions

Description

Show data types and dimensions of a database table.

Usage

tableOverview(channel, sqtable, max = 1000)

Arguments

channel	an RODBC connection.
sqtable	a database table or view.
max	number of rows to analyze the resulting data frame columns in R. Pass max = 0 to analyze the entire database table.

Value

List containing Cols and Rows, describing column data types and the number of rows.

See Also

sqlColumns, sqlQuery, and tableNrow are the underlying functions used to examine the table/view.

class is the base function to show the class of an object inside the R workspace.

tableHead returns the first rows of a database table.

MSSQL-package gives an overview of the package.

Examples

## Not run: 
con <- odbcConnect("myDatabase")

tableOverview(con, "sysusers")

tableOverview(con, "sysusers")$Cols

## End(Not run)

---

Quote Table Name

Description

Add special quotes around table name.

Usage

tableQuote(sqtable)

Arguments

sqtable

table name, with or without schema name.

Value

String with special quotes.

Note

The sqlQuery function requires special quotes if the table name has spaces. Furthermore, the schema name must not be inside the special quotes.

See Also

sqlQuery requires special quotes if the table name has spaces.

Quotes in base R.

MSSQL-package gives an overview of the package.

Examples

tableQuote("table")
tableQuote("table name")
tableQuote("schema.table")
tableQuote("schema.table name")

---