Help for package MSSQL (original) (raw)
| Version: | 1.0.1 |
|---|---|
| Date: | 2024-10-22 |
| Title: | Tools to Work with Microsoft SQL Server Databases via 'RODBC' |
| Imports: | 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. |
| License: | GPL-3 |
| URL: | https://github.com/gfcm/MSSQL |
| Encoding: | UTF-8 |
| RoxygenNote: | 7.3.2 |
| NeedsCompilation: | no |
| Packaged: | 2024-10-22 02:05:14 UTC; arnim |
| Author: | Arni Magnusson [aut, cre] |
| Maintainer: | Arni Magnusson thisisarni@gmail.com |
| Repository: | CRAN |
| Date/Publication: | 2024-10-22 04:10:05 UTC |
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](#topic+dbOverview) and[tableOverview](#topic+tableOverview) more convenient than the underlying[sqlTables](../../RODBC/refman/RODBC.html#topic+sqlTables) and [sqlColumns](../../RODBC/refman/RODBC.html#topic+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 theRows and Cols columns will only contain NA values. Similarly, the peek = FALSE results in faster computation, but theFirst 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](../../RODBC/refman/RODBC.html#topic+sqlTables) is the underlying function used to get the list of tables/views, [tableDim](#topic+tableDim) is used to count rows and columns, and [sqlColumns](../../RODBC/refman/RODBC.html#topic+sqlColumns) is used to peek at the first column names.
[dbStorage](#topic+dbStorage) shows the storage size of tables and[dbTime](#topic+dbTime) shows the time when tables/views were created and last modified.
[MSSQL-package](#topic+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 oftotal, 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](../../RODBC/refman/RODBC.html#topic+sqlQuery) is the underlying function used to querysys.tables, sys.indexes, sys.partitions,sys.allocation_units, and sys.schemas.
[dbOverview](#topic+dbOverview) shows the dimensions of tables/views and the first column names, and [dbTime](#topic+dbTime) shows the time when tables/views were created and last modified.
[object.size](../../../../doc/manuals/r-patched/packages/utils/refman/utils.html#topic+object.size) is the base function to return the storage size of objects inside the R workspace.
[MSSQL-package](#topic+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](../../RODBC/refman/RODBC.html#topic+sqlQuery) is the underlying function used to querysys.tables and sys.views.
[dbOverview](#topic+dbOverview) shows the dimensions of tables/views and the first column names, and [dbStorage](#topic+dbStorage) shows the storage size of tables.
[Sys.time](../../../../doc/manuals/r-patched/packages/base/refman/base.html#topic+Sys.time) is the base function to show the current time.
[MSSQL-package](#topic+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](#topic+tableNrow) and [tableNcol](#topic+tableNcol) are the underlying functions to get the number of rows and columns in a database table.
[dim](../../../../doc/manuals/r-patched/packages/base/refman/base.html#topic+dim) is the base function to return the dimensions for data frames inside the R workspace.
[MSSQL-package](#topic+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](../../RODBC/refman/RODBC.html#topic+sqlQuery) with [tableQuote](#topic+tableQuote) are the underlying functions used to query the table/view.
[head](../../../../doc/manuals/r-patched/packages/utils/refman/utils.html#topic+head) is the base function to return the first parts of an object inside the R workspace.
[tableOverview](#topic+tableOverview) shows the data types and dimensions of a database table.
[MSSQL-package](#topic+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](#topic+tableDim) and [tableNrow](#topic+tableNrow) also return the dimensions of a database table.
[sqlColumns](../../RODBC/refman/RODBC.html#topic+sqlColumns) is the underlying function used to examine the table columns.
[ncol](../../../../doc/manuals/r-patched/packages/base/refman/base.html#topic+ncol) is the base function to return the number of columns for data frames inside the R workspace.
[MSSQL-package](#topic+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](#topic+tableDim) and [tableNcol](#topic+tableNcol) also return the dimensions of a database table.
[sqlQuery](../../RODBC/refman/RODBC.html#topic+sqlQuery) is the underlying function used to examine the table rows.
[nrow](../../../../doc/manuals/r-patched/packages/base/refman/base.html#topic+nrow) is the base function to return the number of rows for data frames inside the R workspace.
[MSSQL-package](#topic+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](../../RODBC/refman/RODBC.html#topic+sqlColumns), [sqlQuery](../../RODBC/refman/RODBC.html#topic+sqlQuery), and[tableNrow](#topic+tableNrow) are the underlying functions used to examine the table/view.
[class](../../../../doc/manuals/r-patched/packages/base/refman/base.html#topic+class) is the base function to show the class of an object inside the R workspace.
[tableHead](#topic+tableHead) returns the first rows of a database table.
[MSSQL-package](#topic+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](../../RODBC/refman/RODBC.html#topic+sqlQuery) requires special quotes if the table name has spaces.
[Quotes](../../../../doc/manuals/r-patched/packages/base/refman/base.html#topic+Quotes) in base R.
[MSSQL-package](#topic+MSSQL-package) gives an overview of the package.
Examples
tableQuote("table")
tableQuote("table name")
tableQuote("schema.table")
tableQuote("schema.table name")