R - Prime Numbers

This R code will generate tables containing the first 2 million numbers, whether that number is prime (divisible only itself and 1) or not, its lowest divisor (or 0 for primes).  The final two statements result in displaying the percent of non-prime numbers (92.55%) and prime numbers (7.45%).


num <- 1:2000000
prime <- rep("Prime", 2000000)
divisor <- rep(0, 2000000)
for (x in seq(from=2, to=2000000, by=1)) {
  z <- sqrt(x);
  for (y in 2:z) {
     if (x%%y == 0) {
       prime[x] <- "Non-Prime";
       divisor[x] <- y;
       break;
       }    
    }
}
ptable <- table(prime)
prop.table(ptable)


PowerShell - Summary Of Files By Extension

Knowing in advance all the possible file types is a more efficient way to produce this type of report, but letting the code figure that out is more practical.  This report will start at the given $Target directory and summarize the number of files and total bytes by extension, in alphabetical order, to the whatever $ReportFile you select.

# Note: This report was written and tested using PowerShell V3
#
# Change the starting directory and report name to suit your needs
#
$Target = "C:\YourChoiceOfDirectory"
$ReportFile = "C:\YourChoiceOfReportName.txt"
$AllTypes = @()
#
# Write Report Header Lines
#
$Message = "All File Summary Report"
($Message) | Out-File $ReportFile
$Message = "                       "
($Message) | Out-File $ReportFile -Append
$Message = "FileType        Files                    Bytes"
($Message) | Out-File $ReportFile -Append
$Message = "---------  ----------  -----------------------"
($Message) | Out-File $ReportFile -Append
#
# Get every filename and produce an array of unique extensions
#

$List = Get-ChildItem $Target -File -Recurse
Foreach ($Item in $List) {
   $FileName= $Item.FullName
   $FileSplit = $FileName.split(“.”)
   $FilePieces = $FileSplit.Count
   $FileType = $FileSplit[$FilePieces-1]
   $AllTypes += $FileType
   }
$extensions = $AllTypes | Sort-Object -Unique
#
# Get a list of files for a specific extension, count the number of
# files and their total size and write that info to the report file.

# Repeat for each extension found.
#
Foreach ($ext in $extensions)  {
   $Filename = Get-ChildItem $Target -File *.$ext -Recurse
   $Files = 0; $Size = 0;
   Foreach ($Item in $Filename) {
     $Files++
     $Size = $Size + $Item.Length
      }
   $ext = $ext.PadRight(10,' ')
   $Files = $Files.ToString('N0').PadLeft(11,' ')
   $Size = $Size.ToString('N0').PadLeft(25,' ')
   $Message = "$ext$Files$Size"
   ($Message) | Out-File $ReportFile -Append

SQL Statements

At some point in the past I've used each of the following SQL statements to accomplish some required task and not wanting to Google Search for the same things twice, staring collecting them in a list for faster reference.  Some of these may not work with all versions of SQL Server and as new features are introduced may not be the best way to perform a specific task.  


Row Counts for Every Table in a Database
SELECT o.name, rows
FROM sysindexes i join sysobjects o on o.id=i.id
WHERE indid < 2 and type='U'
ORDER BY rows DESC


Get Information on All Columns for All Tables in a Database
SELECT table_schema, table_name, column_name, ordinal_position,
      column_default, data_type, character_maximum_length
FROM information_schema.columns


The Last Time a Table was Updated For All Tables in a Database
SELECT DISTINCT OBJECT_NAME(object_id,database_id) as TableName, last_user_update
FROM database name.sys.dm_db_index_usage_stats
WHERE database_id = DB_ID('database name')
GROUP BY OBJECT_NAME(object_id,database_id), last_user_update
ORDER BY TableName


Find String in Any Object in a Database
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%string%'


Backup a Database
BACKUP DATABASE databasename
TO DISK = 'path\name.bak';


Restore Database to Another Location
RESTORE DATABASE databasename
FROM DISK = 'path\name.bak'
WITH RECOVERY,
MOVE 'databasename_Data' TO 'newpath\databasename_Data.MDF'
MOVE 'databasename_Log'  TO 'newpath\databasename_Data.LDF'


Copy Results from a Query to a Pipe-Delimited File
BCP "SELECT * FROM [database].[owner].[table_name]" queryout filename -Uuserid -Ppassword -t"|" -c -S server_name\instance


Change User Password
EXEC sp_password 'old password', 'new password', 'userid’


Select Rows Between Two Datetimes
SELECT *
FROM tablename
WHERE field BETWEEN '10/15/2015 00:00:00.00'
               AND '10/15/2015 23:59:59.999'


Select Yesterday's Data
SELECT *
FROM table
WHERE date_field >= dateadd(day,datediff(day,1,GETDATE()),0)
  AND date_field < dateadd(day,datediff(day,0,GETDATE()),0)


Count the Number of Occurances of Each Value
SELECT DISTINCT column_name, count(column_name) as CountOf
FROM tablename
GROUP BY column_name


Count Rows By Year
SELECT DISTINCT YEAR(datetime_field) as Year, COUNT(*) as Rows
FROM tablename
GROUP BY YEAR(datetime_field)
ORDER BY YEAR(datetime_field) DESC


Update Rows Based on a Time Difference in Minutes
UPDATE tablename
SET field = field
WHERE DATEDIFF(MINUTE,datetime,CURRENT_TIMESTAMP) < minutes


Replace String in a Field
UPDATE tablename
SET field = REPLACE(field, 'text', 'newtext')
Replace Substring in a Column
UPDATE tablename
SET field = CAST(REPLACE(CAST(field as NVarchar(4000)),'string1','string2') AS NText)
WHERE field LIKE '%string1%'


Delete Rows from a Table Between Two Datetimes
DELETE FROM tablename
WHERE date_field BETWEEN 'mm/dd/yyyy 00:00:00.00' AND 'mm/dd/yyyy 23:59:59.999'


Left Join
SELECT A.field1, A.field2, B.field3
FROM tablename1 A
LEFT JOIN tablename2 B
ON A.field1=B.field1

AWK - Directory to Rows

This AWK script parses the output of the Windows "DIR *.* /s" command and puts in columnar format with column titles. This makes it easy to open in your favorite spreadsheet program using a fixed column format.



#
BEGIN {
   record="  ";                               # Start output record at 2 spaces
   for (i = 1; i <= 7; ++i) {record=record record;}   # Expand output record to 256 spaces
   record=repl(record,1,14,"Date");                   # Date goes starts at column 1
   record=repl(record,12,26,"Size");                  # File size starts at column 12
   record=repl(record,28,87,"Filename");              # File name starts in column 28
   record=repl(record,89,148,"Fullpath");             # Full path starts in column 89
   print record;                                      # Print the header line
}
function repl(s,f,t,v)                                 # Custom function to add data to the output record
{ return substr(s,1,f-1) sprintf("%-*s", t-f+1, v) substr(s,t+1) }
{
if ( substr($0,2,9) == "Directory" ) {                 # If this line contains the directory name,
   dir=substr($0,15,60);                              # save the name
   }
else if (substr($0,3,1) == "/" &&                   
        substr($0,6,1) == "/" &&
        substr($0,25,1) != "<")   { # If this line contains a file ...
   date=substr($0,1,10);                              # Date
   size=substr($0,24,15);                             # File size
   file=substr($0,40,60);                             # File name
   name=dir "\\" file;                                # Full path
   record="  ";                               # Start output record at 2 spaces
   for (i = 1; i <= 7; ++i) {record=record record;}   # Expand output record to 256 spaces
   record=repl(record,1,10,date);                     # Date goes starts at column 1
   record=repl(record,12,26,size);                    # File size starts at column 12
   record=repl(record,28,87,file);                    # File name starts in column 28
   record=repl(record,89,148,dir);                    # Full path starts in column 89
   print record;                                      # Print the output line
   }
}

AWK - Hex to Binary

This AWK code is used to translate data that has been exported from Wireshark using
File ... Export Packet Dissections ... As Plain Text ..., checking only the Packet Bytes box
in the Packet Format section (unchecking all others). These print lines are hex characters,
which this code will translate to binary. This can be useful to create a file (e.g. PDF, JPG)
from a packet capture.



BEGIN {BINMODE = 2;}                       # BINMODE of 2 sets writing output to binary mode
{ if ($1 > "0020") {                       # Ignore the lines starting with 0000, 0010 and 0020
   x=0;                                   # There are 16 bytes on each line; Begin at offset 0
   if ($1 == "0030") {x=6;}               # Ignore the first 6 bytes of the 0030 line
   while (x < 16) {                       # We start after bypassing the network header bytes
     n=0;                               # Initialize the binary number to output
     pos = 7+(x*3);                       # Calculate the position of the first hex byte
     hex = substr($0,pos,1);              # Grab the first hex byte
     if (hex == " ") {next;}              # If a blank is found, there are no more bytes
     if (hex <= "9") {n=hex*16;}          # Use multiplication for hex digits 0-9
     if (hex == "a") {n=160;}             # Assign the other hex digits to their binary numbers
     if (hex == "b") {n=176;}
     if (hex == "c") {n=192;}
     if (hex == "d") {n=208;}
     if (hex == "e") {n=224;}
     if (hex == "f") {n=240;}
     hex = substr($0,pos+1,1);            # Grab the second hex byte
     if (hex <= "9") {n=n+hex;}           # Use addition for hex digits 0-9
     if (hex == "a") {n=n+10;}            # Add the other hex digits to their binary numbers
     if (hex == "b") {n=n+11;}
     if (hex == "c") {n=n+12;}
     if (hex == "d") {n=n+13;}
     if (hex == "e") {n=n+14;}
     if (hex == "f") {n=n+15;}
                                          # It is required to print directly to a file using the
     printf "%c", n > "output.bin";       # “>” syntax to avoid AWK inserting a CR before each LF
     x++;                               # Go to the next hex byte on this line                   
     }
   }
}

R - Prime Numbers

This R code will generate tables containing the first 2 million numbers, whether that number is prime (divisible only itself and 1) or not, ...