Tuesday, May 5, 2015

MSSQL_Refresh-ListTableColumnsInfo

SELECT
SysObj.name AS TableName
,SysCol.column_id AS ColumnId
,SysCol.name AS ColumnName
,CASE
WHEN SysCol.is_identity=1
THEN 'Yes'
ELSE 'No'
END AS IsIdentity
,CASE
WHEN SysCol.is_nullable=1
THEN 'Yes'
ELSE 'No'
END AS IsNullable
,CASE
WHEN SysTyp.name IN ('char','varchar')
THEN SysTyp.name+'('+ CASE
WHEN SysCol.max_length<0
THEN 'MAX'
ELSE CONVERT(VARCHAR(10), SysCol.max_length)
END+')'
WHEN SysTyp.name IN ('nvarchar','nchar')
THEN SysTyp.name+'('+ CASE
WHEN SysCol.max_length<0
THEN 'MAX'
ELSE CONVERT(VARCHAR(10),SysCol.max_length/2)
END+')'
WHEN SysTyp.name IN ('numeric')
THEN SysTyp.name+'('+CONVERT(VARCHAR(10),SysCol.precision)+','+CONVERT(VARCHAR(10),SysCol.scale)+')'
ELSE SysTyp.name
END AS ColumnType
,SysCol.max_length
,SysCol.scale AS Scale
,SysCol.precision AS PrecisionFormat
,SysCol.collation_name AS Collation
,SysInf.CHARACTER_SET_NAME
FROM
B2BEMPowerdb_Refactor.sys.columns AS SysCol
INNER JOIN
B2BEMPowerdb_Refactor.sys.types AS SysTyp
ON SysCol.system_type_id=SysTyp.user_type_id AND SysTyp.is_user_defined=0
INNER JOIN
B2BEMPowerdb_Refactor.sys.objects AS SysObj
ON SysCol.object_id=SysObj.object_id
INNER JOIN
B2BEMPowerdb_Refactor.sys.schemas AS SysSch
ON SysObj.schema_id=SysSch.schema_id
LEFT OUTER JOIN
B2BEMPowerdb_Refactor.sys.identity_columns AS SysIdc
ON SysCol.object_id=SysIdc.object_id AND SysCol.column_id=SysIdc.column_id
LEFT OUTER JOIN
B2BEMPowerdb_Refactor.sys.computed_columns AS SysCpc
ON SysCol.object_id=SysCpc.object_id AND SysCol.column_id=SysCpc.column_id
LEFT OUTER JOIN
B2BEMPowerdb_Refactor.sys.check_constraints AS SysCkc
ON SysCol.object_id=SysCkc.parent_object_id AND SysCol.column_id=SysCkc.parent_column_id
LEFT OUTER JOIN
B2BEMPowerdb_Refactor.INFORMATION_SCHEMA.COLUMNS AS SysInf
ON SysCol.name=SysInf.COLUMN_NAME
ORDER BY
SysSch.name+'.'+SysObj.name,SysCol.column_id

Wednesday, April 22, 2015

Xamarin_Refreash-I

Xamarin-A multi-platform evolved tool


Suited for enterprise integration.
Suited for code validation.
Suited for iBeacons.
Suited for MS Band, using its SDK, working code and component API.

With Platform Preview and tools.
With Standard Forms.
With good social media like LinkedInFacebook, Twitter, InstaGramGitHub, Google+ and YouTube.

Tuesday, April 14, 2015

MSSQL_Refresh-ListTablePKFKColumn

SELECT
PKeyTab.TableName, PKeyTab.PKName, FKeyTab.FKName, PKeyTab.ColumnName
FROM
(
SELECT
TableName, PKName, ColumnName
FROM
(
SELECT
OBJECT_NAME(parent_object_id) AS PKTableName,
OBJECT_NAME(OBJECT_ID) AS PKName
FROM
sys.objects
WHERE
type_desc
IN ('PRIMARY_KEY_CONSTRAINT')
) AS PKTab
LEFT OUTER JOIN
(
SELECT
Table_Name AS TableName,
Constraint_Name AS ConstraintName,
Column_Name AS ColumnName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
) As AllTab
ON ( PKTab.PKTableName = AllTab.TableName
AND
PKTab.PKName = AllTab.ConstraintName)
) AS PKeyTab
LEFT OUTER JOIN
(
SELECT
TableName, FKName, ColumnName
FROM
(
SELECT
OBJECT_NAME(parent_object_id) AS FKTableName,
OBJECT_NAME(OBJECT_ID) AS FKName
FROM
sys.objects
WHERE
type_desc
IN ('FOREIGN_KEY_CONSTRAINT')
) AS FKTab
LEFT OUTER JOIN
(
SELECT
Table_Name AS TableName,
Constraint_Name AS ConstraintName,
Column_Name AS ColumnName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
) As AllTab
ON ( FKTab.FKTableName = AllTab.TableName
AND
FKTab.FKName = AllTab.ConstraintName)
) AS FKeyTab
ON (PKeyTab.TableName = FKeyTab.TableName AND
PKeyTab.ColumnName = PKeyTab.ColumnName )
ORDER BY PKeyTab.TableName, PkeyTab.PKName

Monday, January 19, 2015

CRAN-R_RefreshPart4

Hi,

This is the next refresh on R, previously we saw many packages but now I give one single script.
On Windows it is better to run under RStudio:
#devtools from RStudio
install.packages("devtools") ;
library("devtools") ;
#Rserve interface
install.packages("Rserve") ;
#Rcmdr
install.packages("Rcmdr", dependencies=TRUE) ;
install.packages(c("car","MASS","nnet","XLConnect")) ;
#plyr Get the released version from CRAN
install.packages("plyr") ;
#plyr Get the development version from github
devtools::install_github("hadley/plyr") ;
#dplyr Get the released version from CRAN
install.packages("dplyr") ;
devtools::install_github("hadley/lazyeval") ;
devtools::install_github("hadley/dplyr") ;
install.packages(c("nycflights13", "Lahman")) ;
#reshape
install.packages("reshape") ;
install.packages("reshape2") ;
#iplots
install.packages("iplots") ;
#iplots eXtreme: Acynomix
install.packages("Acynomix") ;
#ggobi
source("http://www.ggobi.org/downloads/install.r") ;
#rggobi
install.packages("rggobi") ;
#google CausalImpact
devtools::install_github("google/CausalImpact") ;
#packrat
install.packages("packrat") ;
devtools::install_github("rstudio/packrat") ;
#knitr
install.packages("knitr", dependencies = TRUE) ;
update.packages(ask = FALSE, repos = 'http://cran.rstudio.org') ;
install.packages("knitr",
                 repos = c("http://rforge.net", "http://cran.rstudio.org"),
                 type = "source") ;
devtools::install_github('yihui/knitr', build_vignettes = TRUE) ;
#rmarkdown
install.packages("rmarkdown") ;
devtools::install_github("rstudio/rmarkdown") ;
#shiny
install.packages("shiny") ;
#ggplot & ggplot2
install.packages("ggplot") ;
install.packages("ggplot2") ;
#ggvis
install.packages("ggvis") ;
#rCharts
devtools::install_github("ramnathv/rCharts") ;
#plotly
install_github("ropensci/plotly") ;
devtools::install_github("ropensci/plotly") ;
#googlevis
install.packages("googleVis") ;
#clickme
devtools::install_github("nachocab/clickme") ;
#gg2v
install.packages("gg2v") ;
devtools::install_github("hadley/gg2v") ;
#vega, rVega
install.packages("Vega","rVega") ;
devtools::install_github("metagraf/rVega") ;
devtools::install_github("rVega", "metagraf") ;
#r2d3
devtools::install_github("jamesthomson/R2D3") ;
#ggmap
install.packages("ggmap") ;
install.packages("pandoc") ;
#biocLite
source("http://bioconductor.org/biocLite.R") ; biocLite() ;
#bioc
source("http://bioconductor.org/getBioC.R") ; getBioC() ;

Sunday, December 14, 2014

CRAN-R_RefreshPart3

This post follows the previous part2.

About SyncFusion PredictiveAnalytics

Here is the link.

It uses a Predictive Model Markup Labguage with files outputted using R.

Here is a brief description.

Thursday, November 27, 2014

CRAN-R_RefreshPart2

This post follows the previous part1.

About ggplot

You have a ggplot book, and book website.

About R

You have a RGraphics book.

You have an AdvancedR book.

You have a RetEspace book, web site, book part 2, and web site part 2.

You have a Mathematical Statistics With Applications in R book.


Wednesday, November 26, 2014

CRAN-R_RefreshPart1

Previously

You read already my first R post, this is a refresher.

Setup

You can find R in CRAN.

Setup Related

You can find related projects in CRAN.

Setup Packages

You can find ReShape2 in GitHub or
install.packages("reshape2")
Reshape2 makes it easy to transform data between wide and long formats. reshape2 is based around two key functions: melt and cast: melt takes wide-format data and melts it into long-format data and cast takes long-format data and casts it into wide-format data.

You can find Dplyr in GitHub or
install.packages("dplyr")
Dplyr is the next iteration of plyr, focussing on only data frames. dplyr is faster and has a more consistent API.

You can find DevTools or
install.packages("devtools")
Devtools makes package development a breeze: it works with R’s existing conventions for code structure, adding efficient tools to support the cycle of package development.

You can find PackRat or
install.packages("packrat") 
A dependency management tool for R to make your R projects more isolated, portable, and reproducible.

You can  find KnitR. Elegant, flexible and fast dynamic report generation that combines R with TeX, Markdown, or HTML.

You can find RMarkdown Website or in GitHub or
install.packages("rmarkdown")
R Markdown lets you insert R code into a markdown document. R then generates a final document that replaces the R code with its results.

You can find Shiny or
install.packages("shiny")
Shiny makes it incredibly easy to build interactive web applications with R.
 
You can find Ggplot2 or
install.packages("ggplot2")
An enhanced data visualization package for R.

You can find GgVis or
install.packages("ggvis")
Is the next iteration of the popular ggplot2 graphics package. ggvis creates dynamic, interactive data visualizations.

You can find RforGoogleAdWord post.

Setup R-(D)COM

You can find R-(D)COM in CRAN. Also the R-(D)COM installer.

Setup RStudio

You can find it in it RStudio Website.

Setup PowerShell

You can find PowerShell R Interop in Codeplex.