Posts

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…
Post a Comment
Read more

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 …
Post a Comment
Read more

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);                     …
Post a Comment
Read more