Search This Blog

2009-10-29

Insert procedure generator



ALTER PROCEDURE [dbo].[procUtils_GenerateInsertProc]                
@TableName [varchar](50)                
WITH EXECUTE AS CALLER                
AS                
BEGIN -- proc start                                                  
SET NOCOUNT ON;                                                  
BEGIN TRY        --begin try                                        
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name                       
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'                      
--SELECT column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'              --SELECT *     from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Task'                
--Declare the Table variable                           
DECLARE @ColNames TABLE                          
(                          
Number INT IDENTITY(1,1), --Auto incrementing Identity column                          
ColName VARCHAR(300) , --The string value                        ,   
DataType varchar(50) ,  --the datatype                 
IS_NULLABLE nvarchar(5) , --should we add =null in front   
CHARACTER_MAXIMUM_LENGTH INT  
)                          
--Decalre a variable to remember the position of the current delimiter                          
DECLARE @CurrentDelimiterPositionVar INT                           
DECLARE @PkColName varchar(200)
DECLARE @ProcName varchar(1000)   
set @ProcName = '[dbo].[procGen_' + @TableName + '_Insert]'  
--Decalre a variable to remember the number of rows in the table                          
DECLARE @Count INT                          
--Populate the TABLE variable using some logic                          
-- SELECT * from INFORMATION_SCHEMA.COLUMNS                       
INSERT INTO @ColNames   
SELECT column_name ,  Data_type , IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH  
from INFORMATION_SCHEMA.COLUMNS                       
where TABLE_NAME=@TableName                          
--Initialize the looper variable                          
SET @CurrentDelimiterPositionVar = 1                          
--Determine the number of rows in the Table                          
SELECT @Count=max(Number) from @ColNames                          
--A variable to hold the currently selected value from the table                          
DECLARE @ColName varchar(300);                          
DECLARE @DataType varchar(50)                
DECLARE @IS_NULLABLE VARCHAR(5)  
DECLARE @CHARACTER_MAXIMUM_LENGTH INT  
DECLARE @CODESTR VARCHAR(1000)   
--PRINT DROP PROCEDURE  
PRINT 'IF OBJECT_ID(''' + @ProcName + ''') IS NOT NULL  
BEGIN   
DROP PROC ' + @ProcName + '   
END   
GO'  
print '                
/****** Object:  StoredProcedure ' + @ProcName + '*/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
CREATE PROCEDURE ' + @ProcName + '  
@CurUserSessionId [int] ,  
@CurPageTypeId [int] ,  
@MsgOut [varchar](200) ,  
@DebugMsgOut [varchar](200) ,  
@ret [int] OUTPUT ,'  
--@COLUMN_NAME [DATA_TYPE] (MAX_LENGTH) =NULL ,   
WHILE @CurrentDelimiterPositionVar <= @Count --1st loop  
BEGIN                          
--Load current value from the Table                          
SELECT @ColName = ColName FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar  
SELECT @DataType = DataType FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar         
SELECT @IS_NULLABLE = IS_NULLABLE FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar              
SELECT @CHARACTER_MAXIMUM_LENGTH = CHARACTER_MAXIMUM_LENGTH FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar              
SET @CODESTR = '@' + @ColName + ' ['+ @DataType +  '] '  
--ADD THE (200)  
IF @CHARACTER_MAXIMUM_LENGTH IS NOT NULL   
BEGIN --IF @CHARACTER_MAXIMUM_LENGTH IS NOT NULL   
--xml  
if @DataType != 'xml' and @DataType != 'sql_variant' and @DataType != 'text' and @DataType != 'ntext' and @DataType != 'image' and @DataType != 'hierarchyid' and @DataType != 'binary' and @DataType != 'varbinary'   
begin --those with()  
if @CHARACTER_MAXIMUM_LENGTH != -1
SET @CODESTR =    
@CODESTR + '(' + CONVERT(VARCHAR , @CHARACTER_MAXIMUM_LENGTH ) + ')'     
else 
SET @CODESTR =    @CODESTR + '(max)'     
end --those with(200)  
else   
begin   
SET @CODESTR =  @CODESTR   
end     
END --IF @CHARACTER_MAXIMUM_LENGTH IS NOT NULL   
IF @IS_NULLABLE = 'YES'  
SET @CODESTR = + @CODESTR + ' = NULL '  
if @CurrentDelimiterPositionVar = 1 
SET @CODESTR = @CODESTR + ' OUTPUT ' 
if @CurrentDelimiterPositionVar != @Count   
SET @CODESTR = @CODESTR + ','  
IF @DataType != 'timestamp'  
PRINT @CODESTR   
--DEBUGGING  
--PRINT '@ColName - ' + @ColName   
--PRINT '@DataType - ' + @DataType            
--PRINT '@IS_NULLABLE - ' + @IS_NULLABLE   
--PRINT '@CHARACTER_MAXIMUM_LENGTH - ' +  CONVERT ( VARCHAR , @CHARACTER_MAXIMUM_LENGTH )  
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                          
END                         
print '  
AS  
BEGIN -- proc start                              
SET NOCOUNT ON;                              
'                
SET @CurrentDelimiterPositionVar = 1              --START LOOP AGAIN   
print 'INSERT INTO [dbo].[' + @TableName + ']( '                          
--Loop through until all row processing is done                          
WHILE @CurrentDelimiterPositionVar <= @Count     --2nd loop  
BEGIN                          
--Load current value from the Table                          
SELECT @ColName = ColName FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar  
SELECT @DataType = DataType FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar         
SELECT @IS_NULLABLE = IS_NULLABLE FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar              
if   @CurrentDelimiterPositionVar = 1
begin -- the primary key 
set @CODESTR = ''
set @PkColName = @ColName
end --the primary key 
ELSE 
set @CODESTR = '[' + @ColName + ']' --null the codestring var  
if @CurrentDelimiterPositionVar < @Count  and @CurrentDelimiterPositionVar <> 1
set @CODESTR = @CODESTR + ' , '  
set @CODESTR = @CODESTR + '--type of ' + @DataType    
if @DataType != 'timestamp'  
print @CODESTR  
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                          
END                          
print ') VALUES ( '                          
--AND START ALL OVER AGAIN                     
SET @CurrentDelimiterPositionVar = 1                
--Loop through until all row processing is done          
WHILE @CurrentDelimiterPositionVar <= @Count --1st loop  
BEGIN                          
--Load current value from the Table                          
SELECT @ColName = ColName FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar  
SELECT @DataType = DataType FROM @ColNames   
WHERE Number = @CurrentDelimiterPositionVar         
if   @CurrentDelimiterPositionVar = 1
begin --the primary key 
set @CODESTR = ''
set @PkColName = @ColName
end --the primary key 
ELSE 
set @CODESTR = '@' + @ColName  
 
--Process the current value                          
if @CurrentDelimiterPositionVar < @Count and @CurrentDelimiterPositionVar <> 1
set @CODESTR = @CODESTR + ' ,'  
if @DataType != 'timestamp'  
print @CODESTR  
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                          
END                                                 
set nocount off                       
PRINT ')  
SET @' + @pkColName + ' = @@IDENTITY
SET @Ret = 0
END  
GO   
'                
END TRY        --end try                                        
BEGIN CATCH                                              
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +                                     
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +                       
CAST(ERROR_SEVERITY() AS varchar(1000)) +                                     
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +                       
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))                                              
END CATCH                                              
END --procedure end                                                   
--USE [GenApp]                      
--GO                      
--SELECT NAME FROM SYS.tables where name like '%Msg%'                      
--EXEC [dbo].[procUtils_GenerateInsertProc] @TableName = N'Task'                

how-to find nullable stored procedure parameters in sql server 2005 2008



declare @sql varchar(MAX)
set @sql = ( select ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_NAME   = 'procToExamine'        )
 
 
if ( dbo.fn_regex('@ParamName(\s)*\[(\s)*int(\s)*\](\s)*=(\s)*null(\s)*\,' , @sql) <> 0)
print 'yes'
ELSE 
PRINT 'no'
print @sql

Utility Regex function for Sql Server 2005 2008

SOURCES
Ken Henderson's WebLog


USE GenApp
GO
 
--UNCOMMENT AND RUN THOSE FIRST 
-- --ENABLE Ole Automation Procedures                   
--EXECUTE sp_configure 'Ole Automation Procedures', '1'                   
--RECONFIGURE WITH OVERRIDE                   
-- --SHOW THE CONFIGURATION                   
--EXEC SP_CONFIGURE;  
 
CREATE FUNCTION
dbo.fn_regex(@pattern varchar(255), @matchstring varchar(8000))
RETURNS int
AS
BEGIN
declare @obj int
declare @res int
declare @match bit
set @match=0
exec @res=sp_OACreate 'VBScript.RegExp',@obj OUT
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OASetProperty @obj, 'Pattern', @pattern
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OASetProperty @obj, 'IgnoreCase', 1
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OAMethod @obj, 'Test',@match OUT, @matchstring
IF (@res <> 0) BEGIN
RETURN NULL
END
exec @res=sp_OADestroy @obj
return @match
END
GO

less shortcuts

[Arrows]/[Page Up]/[Page Down]/[Home]/[End]: Navigation.
[Space bar]: Next page.
b: Previous page.
ng: Jump to line number n. Default is the start of the file.
nG: Jump to line number n. Default is the end of the file.
/pattern: Search for pattern. Regular expressions can be used.
n: Go to next match (after a successful search).
N: Go to previous match.
mletter: Mark the current position with letter.
‘letter: Return to position letter. [' = single quote]
‘^ or g: Go to start of file.
‘$ or G: Go to end of file.
s: Save current content (got from another program like grep) in a file.
=: File information.
F: continually read information from file and follow its end. Useful for logs watching. Use Ctrl+C to exit this mode.
-option: Toggle command-line option -option.
h: Help.
q: Quit.

2009-10-28

Nix Cheat Sheet

echo PORTABLE *NIX CHEAT SHEET COPY PASTE AT YOUR COMMAND PROMPT
echo AND USE BY history | grep "a word"

echo how-to script --- script -a /export/home/yogeorgi/SCRIPTSLOGS/`date +%Y%m%d%H%M%S`_script.log

echo if the man is full of references to files --- man commandToFindHelpAbout | col -b >/var/man/mancommandToFindHelpAbout.man.txt
vim .bashrc
echo´'
alias ls="ls -a -l -X -1 --color=tty"
alias dir="ls -ba"
alias cls="clear"
alias deltree="rm -r"
alias move="mv"
ls -al | more
'

echo take the last 5 commands for faster execution to the temp execution script --- tail -n 5 /root/.bash_history >> /var/run.sh

echo I saw the command cd /to/some/suching/dir/which/was/very/long/to/type so I redid it and saved my fingers --- '!345'


echo send that file to myself --- cat history.txt | mail -s "test file sending" -c mail1@com yordan.georgiev(boom)gmail.com


echo how-to display the history withouth the line numbers ... --- history | perl -i -pe 's/^([ ]*)([0-9]*)(.*)$/$3/gi'

echo how-to deal with command outputs --- command | filtercommand > command_output.txt 2>errors_from_command.txt

echo find the files in the /bin folder having the "trace" string in their name --- find /bin | grep "trace"

echo find all xml type of files and display only the rows having wordToFindInRow --- find . -name '*.xml' -exec cat {} \;| grep wordToFindInRow | less

echo find and replace recursively --- find . -name '*.html' -print0 | xargs -0 perl -pi -e 's/foo/bar/g'


how-to putty go to the beginning of the current line --- Ctrl + A
how-to putty Go to the end of the line you are currently typing on -- Ctrl + E
how-to putty go to the beginning of line --- Ctlr + A -
how-to putty go to the end of line --- Ctrl + E
how-to putty delete a word --- Ctrl + W
how-to putty go forwared --- Alt + f - Move forward
how-to putty move backwards -- Alt + B - move backwords
how-to putty delete backwards from cursors --- Alt + BackSpace
how-to putty browse command history backwords --- down arrow
how-to putty browse command history upwards up arrow -
how-to putty recall command --- Ctrl + R
how-to putty Suspend/ Stop the commandCtrl + z -
how-to putty Clear the screen --- Ctrl + l




echo display the first 20 lines of the file --- head -n 20 tooLongFile --

echo get nice prompt
vim /etc/bashrc
PS1="\u@\h \t \w\n\\$ "

echo how to restart a process initiated at startup
/etc/rc.d/init.d/sendmail start | stop | status | restart

echo see all the rules associated with the firewall
iptables -L -v
gunzip *file.zip
tar -xvf file.tar
To access the server download winscp.exe:

echo start winscp with a stored session
winscp oracle@192.168.255.12

echo To start remote session click on the putty screen , configure putty settings to pull full screen with alt + Enter
echo right click on the title bar , settings , change the font , copy paste from and to the terminal window text

echo how-to ensure the sshd daemon is running --- ps -ef | grep sshd

echo User and group management
useradd -p winscppass -r winscp -d /home/winscp --- to add a user with specifig home directory name and pass
luserdel winscp --- delete the user
gpasswd: administer the /etc/group file
groupadd: Create a new group
groupmod: Modify a group
groupdel: Delete a new group
useradd: Create a new user or update default new user information
usermod: Modify a user account
userdel: Delete a user account and related files

echo how to kill process interactively
killall -v -i sshd

echo disk usage of users under the /home directory
du --max-depth=1 /home | sort -n -r

echo the most efficent way to search your history is to hit Ctrl R and type the start of the command. It will autocomplete as soon as there’s a match to a history entry, then you just hit enter. If you want to complete the command (add more stuff to it ) use the right arrow to escape from the quick search box ...
How to install Perl modules
gzip -dc yourmodule.tar.gz | tar -xof -
perl Makefile.PL
make test
make install

echo How to see better which file were opened , which directories were visited
echo type always the fullpath after the vi - use the $PWD env variable to open files in the current directory , thus after opening the file after:
vim /$PWD , press tab to complete the name of the current directory , type the name of the file
thus after
history | grep vim

echo the full list of opened files is viwed .
echo of course the same could be seen from the /home/username/.viminfo file /files
echo where to set the colors for the terminal (if you are lucky to have one with colors; )
/etc/DIR_COLORS
open a file containing "sh" in its name bellow the "/usr/lib" directory
:r !find /usr/lib -name *sh*
go over the file and gf
uname -a --- which version of Linux I am using
rmp -qa --- show all installed packages
passwd [username] --- change the password for the specified user (own password)

echo How to copy paste text in the putty window from client to server - click the right button of your pointing device
echo How to copy paste text from the putty window from server to client - right-click the window title and select copy all to Clipboard.
To restart a service
service sshd restart
service --status-all --- show the status of all services

chown -R root:nortel Directory

echo perform action recursively on a set of files
find . -name '*.pl' -exec perl -wc {} \;

$ for file in *
> do cp $file $file.bak
> done
$ for file in `ls -R` ; do cp $file $file.bak; done

echo make Bash append rather than overwrite the history on disk:
shopt -s histappend

echo henever displaying the prompt, write the previous line to disk:
PROMPT_COMMAND='history -a'

gpm -- general "cut and paste" server
sh ScriptWithALotOfErrorMessages.sh | tee -a whereToSaveIt.log

echo run first the following command
$ script -a The_Log_File_To_Append_as_well_as_display_diagnostig_messages.log

echo than run the script
sh ScriptWithALotOfErrorMessages.sh
tr '\t' ',' < FileWithTabs > fileWithCommas
df -k --- disk usage

echo Allow access to the box from only one ip address
IPTables=/sbin/iptables
$ IPTables -A INPUT -s -p tcp --source-port xxx -j ACCEPT
$ IPTables -A OUTPUT -d < Insert other Origin ip here > -p tcp --destination-port xxx-j ACCEPT

echo has the root logged in over an unencrypted network ?
# last | grep “^root “ | egrep –v “reboot|console” | more
echo start command in the background
command1 &
echo start another one
command2 &

echo bring the command in the foreground
fg %1
echo create a backup file based on the timestamp on bash
cp fileName.ext fileName.ext.`date +%Y%m%d%H%M%S`.bak

echo who am I on this machine
id
whoami

2009-10-27

Set a nice prompt for *nix version

source:
How to change your shell prompt.

Description csh* ksh bash tcsh* zsh
Current working directory $CWD $PWD \w %/ %/
Current working directory, with one's home directory by `~' $CWD:t $PWD##*/ \W %~ %~
Full hostname 'uname -n' 'uname -n' N/A %M %M
Hostname up to the first '.' `hostname -s` `hostname -s` \h %m %m
Start (stop) boldfacing mode %B (or %b) N/A N/A %B (or %b) %B (or %b)
Start (stop) standout mode %S (or %s) N/A N/A %S (or %s) %S (or %s)
Start (stop) underline mode %U (or %u) N/A N/A %U (or %u) %U (or %u)
User name `whoami` `logname` \u %n %n
The shell's tty that the user is logged in on %l N/A N/A %| %|
The current history number %h N/A \! %h (or %!) %h (or %!)
Name of the shell N/A N/A \s N/A N/A
Time of day in 12-hour hh:mm AM/PM %t N/A \@ %t (or %@) %t (or %@)
Time of day in 24-hour hh:mm %T N/A \A %T %T
Time of day in 12-hour with seconds hh:mm:ss AM/PM %p N/A \T %p N/A
Time of day in 24-hour with seconds hh:mm:nn %P N/A \t %P %*
The day in 'dd' format %D N/A N/A %D N/A
The month in 'Mon' format %w N/A N/A %w N/A
The month in 'mm' format %W N/A N/A %W N/A
The year in 'yy' format %y N/A N/A %y N/A
The year in 'yyyy' format %Y N/A N/A %Y N/A
The date in "Weekday Month Date" format N/A N/A \d N/A N/A
The date in day-dd format N/A N/A N/A N/A %w
The date in Mon/dd/yy format N/A N/A N/A N/A %W
The date in yy-mm-dd format N/A N/A N/A N/A %D
The weekday in 'Day' format %d N/A N/A %d N/A
Description csh* ksh bash tcsh* zsh

2009-10-26

Putty shortcuts

Ctlr + A - go to the beginning of line

Ctrl + E - go to the end of line

Ctrl + W - delete a word

Alt + f - Move forward

Alt + B - move backwords

Alt + BackSpace - Delete backward from cursor

down arrow - browser command history backwords

up arrow - browse command history upwards

Ctrl + r - Recall command

Ctrl + z - Suspend/ Stop the command

Search the history backwards

Ctrl + l - clear the screen



Here "2T" means Press TAB twice

$ 2T - All available commands(common)
$ (string)2T - All available commands starting with (string)
$ /2T - Entire directory structure including Hidden one

Linux *nix Cheat sheet

echo record the current session via script
script -a /export/home/yogeorgi/SCRIPTSLOGS/`date +%Y%m%d%H%M%S`_script.log
echo Hint , if you copy paste from this document to putty write first echo and than paste ..
echo get the help in better format
echo if the man is full of references to files
man commandToFindHelpAbout | col -b >/var/man/mancommandToFindHelpAbout.man.txt
alias ls="ls -a -X -1 --color=tty"
alias dir="ls -ba"
alias cls="clear"
alias deltree="rm -r"
alias move="mv"
ls -al | more

echo where I have been lately ?
history | grep cd

echo take the last 5 commands for faster execution to the temp execution script
tail -n 5 /root/.bash_history >> /var/run.sh

echo I saw the command cd /to/some/suching/dir/which/was/very/long/to/type so I redid it and saved my fingers
!345
history >history.txt ---- echo the last 1000 commands into a history.txt file

echo send that file to myself
cat history.txt | mail -s "test file sending" -c mail1@com yordan.georgiev(boom)gmail.com

echo remove all trealing spaces from the history file works for TextPad
^([ ]*)([0-9]*)(.*)$
\3 --- replace with the pure commands
history | gawk -F1 'BEGIN {FS=" "};{print $2 , $3}' | less

echo display the history withouth the line numbers ...
history | perl -i -pe 's/^([ ]*)([0-9]*)(.*)$/$3/gi'


vim fileName anotherFile
echo how to deal with command outputs
command | filtercommand > command_output.txt 2>errors_from_command.txt

echo Hint after the less filter pressing s will prompt you for saving the output to a file ...
echo find all files and folders containing the word linux and pipe it to the less for easier viewing
find / -name "linux" | less

echo find the files having os somewhere in their names and only those having linux
find . -name '*os*' | grep linux | less

echo find all xml type of files and display only the rows having wordToFindInRow
find . -name '*.xml' -exec cat {} \;| grep wordToFindInRow | less

echo The ultimate "find in files" in Linux
find / -name '*bash*' -exec grep -nH tty {} \;

echo or even faster , be aware of "funny file names xargs -0
find / -name '*bak' | xargs grep -nH tty

echo find and replace recursively
find . -name '*.html' -print0 | xargs -0 perl -pi -e 's/foo/bar/g'

Ctrl + A --- Go to the beginning of the line you are currently typing on
Ctrl + E --- Go to the end of the line you are currently typing on
#how-to mount an usb stick
#remember to change the path other wise you will get the device is busy errror
mkdir /mnt/usbflash
mount /dev/sdb1 -t vfat /mnt/usbflash
umount /mnt/usbflash

head -n 20 tooLongFile -- display the first 20 lines of the file

echo get nice prompt
vim /etc/bashrc
PS1="\u@\h \t \w\n\\$ "

echo how to restart a process initiated at startup
/etc/rc.d/init.d/sendmail start | stop | status | restart

echo see all the rules associated with the firewall
iptables -L -v
gunzip *file.zip
tar -xvf file.tar
To access the server download winscp.exe:

echo start winscp with a stored session
winscp oracle@192.168.255.12

echo To start remote session click on the putty screen , configure putty settings to pull full screen with alt + Enter
echo right click on the title bar , settings , change the font , copy paste from and to the terminal window text

echo how to ensure the sshd daemon is running
ps -ef | grep sshd

echo User and group management
useradd -p winscppass -r winscp -d /home/winscp --- to add a user with specifig home directory name and pass
luserdel winscp --- delete the user
gpasswd: administer the /etc/group file
groupadd: Create a new group
groupmod: Modify a group
groupdel: Delete a new group
useradd: Create a new user or update default new user information
usermod: Modify a user account
userdel: Delete a user account and related files

echo how to kill process interactively
killall -v -i sshd

echo disk usage of users under the /home directory
du --max-depth=1 /home | sort -n -r

echo the most efficent way to search your history is to hit Ctrl R and type the start of the command. It will autocomplete as soon as there’s a match to a history entry, then you just hit enter. If you want to complete the command (add more stuff to it ) use the right arrow to escape from the quick search box ...
How to install Perl modules
gzip -dc yourmodule.tar.gz | tar -xof -
perl Makefile.PL
make test
make install

echo How to see better which file were opened , which directories were visited
echo type always the fullpath after the vi - use the $PWD env variable to open files in the current directory , thus after opening the file after:
vim /$PWD , press tab to complete the name of the current directory , type the name of the file
thus after
history | grep vim

echo the full list of opened files is viwed .
echo of course the same could be seen from the /home/username/.viminfo file /files
echo where to set the colors for the terminal (if you are lucky to have one with colors; )
/etc/DIR_COLORS
open a file containing "sh" in its name bellow the "/usr/lib" directory
:r !find /usr/lib -name *sh*
go over the file and gf
uname -a --- which version of Linux I am using
rmp -qa --- show all installed packages
passwd [username] --- change the password for the specified user (own password)

echo How to copy paste text in the putty window from client to server - click the right button of your pointing device
echo How to copy paste text from the putty window from server to client - right-click the window title and select copy all to Clipboard.
To restart a service
service sshd restart
service --status-all --- show the status of all services

chown -R root:nortel Directory

echo perform action recursively on a set of files
find . -name '*.pl' -exec perl -wc {} \;

$ for file in *
> do cp $file $file.bak
> done
$ for file in `ls -R` ; do cp $file $file.bak; done

echo make Bash append rather than overwrite the history on disk:
shopt -s histappend

echo henever displaying the prompt, write the previous line to disk:
PROMPT_COMMAND='history -a'

gpm -- general "cut and paste" server
sh ScriptWithALotOfErrorMessages.sh | tee -a whereToSaveIt.log

echo run first the following command
$ script -a The_Log_File_To_Append_as_well_as_display_diagnostig_messages.log

echo than run the script
sh ScriptWithALotOfErrorMessages.sh
tr '\t' ',' < FileWithTabs > fileWithCommas
df -k --- disk usage

echo Allow access to the box from only one ip address
IPTables=/sbin/iptables
$ IPTables -A INPUT -s -p tcp --source-port xxx -j ACCEPT
$ IPTables -A OUTPUT -d < Insert other Origin ip here > -p tcp --destination-port xxx-j ACCEPT

echo has the root logged in over an unencrypted network ?
# last | grep “^root “ | egrep –v “reboot|console” | more
echo start command in the background
command1 &
echo start another one
command2 &

echo bring the command in the foreground
fg %1
echo create a backup file based on the timestamp on bash
cp fileName.ext fileName.ext.`date +%Y%m%d%H%M%S`.bak

echo who am I on this machine
id
whoami

2009-10-21

Sql 2008 stored procedure for creating history tables out of passed existing table



USE [MyDb]
GO
 
/****** Object:  StoredProcedure [dbo].[procUtils_GenerateHistoryTable]    Script Date: 10/21/2009 09:56:56 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
alter PROCEDURE [dbo].[procUtils_GenerateHistoryTable]
@TableName [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start                                                
SET NOCOUNT ON;                                                
BEGIN TRY        --begin try                                      
--CODE SNIPPET TO LIST TABLE COLUMNS                     
-- RUN IN SSMS WITH cTRL + t FIRST TO OUTPUT THE RESULT TO TEXT FOR COPY PASTE                    
--FIRST SEARCH THE TABLE WHICH HAD A "Feature" in its name                     
--SELECT NAME FROM SYS.TABLES WHERE NAME LIKE '%Feature%'                    
--select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Feature'                        
--Declare the Table variable                         
DECLARE @ColNames TABLE                        
(                        
Number INT IDENTITY(1,1), --Auto incrementing Identity column                        
ColName VARCHAR(300) , --The string value                        
DataType varchar(50) , --the datatype               
IsNullable varchar(3) -- whether or not this table col is nullable
)                        
--Decalre a variable to remember the position of the current delimiter                        
DECLARE @CurrentDelimiterPositionVar INT                         
--Decalre a variable to remember the number of rows in the table                        
DECLARE @Count INT   
DECLARE @sqlTxtToPrint varchar(max)
SET @sqlTxtToPrint = ' USE MyDb 
GO'
 
--Populate the TABLE variable using some logic                        
-- SELECT *  from INFORMATION_SCHEMA.COLUMNS         
INSERT INTO @ColNames SELECT column_name , Data_type , IS_NULLABLE from INFORMATION_SCHEMA.COLUMNS                     
where TABLE_NAME=@TableName                        
--Initialize the looper variable                        
SET @CurrentDelimiterPositionVar = 1                        
--Determine the number of rows in the Table                        
SELECT @Count=max(Number) from @ColNames                        
--A variable to hold the currently selected value from the table                        
DECLARE @ColumnName varchar(300);                        
DECLARE @DataType varchar(50)              
DECLARE @PkColName varchar(200)
DECLARE @IsNullable varchar(3)
 
set @sqlTxtToPrint = '              
SET NOCOUNT ON;              
SET XACT_ABORT ON;              
GO 
DROP TABLE [dbo].[' + @TableName + 'History]
GO
 
CREATE TABLE [dbo].[' + @TableName + 'History](' + CHAR(13)              
--Loop through until all row processing is done                        
WHILE @CurrentDelimiterPositionVar <= @Count                        
BEGIN                        
--Load current value from the Table                        
SELECT @ColumnName = ColName FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar  
SELECT @DataType = DataType FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar    
SELECT @IsNullable = IsNullable FROM @ColNames WHERE Number = @CurrentDelimiterPositionVar    
--PRINT EACH COLUMN WITH , AT THE END 
if @CurrentDelimiterPositionVar = 1 
BEGIN --IF THIS IS THE FIRST COL = PK
set @PkColName = @TableName + 'HistoryId' 
set @sqlTxtToPrint = @sqlTxtToPrint +  
'[' + @PkColName + '][int] IDENTITY(1,1) NOT NULL,' + CHAR(13)
END --IF THIS IS THE FIRST COL = PK
IF @IsNullable = 'YES'
BEGIN --IF @IsNullable = 'YES'
set @sqlTxtToPrint = @sqlTxtToPrint +  
'[' + @ColumnName + ']['  + @DataType  +     
'] NULL ,'+ CHAR(13)
end --IF @IsNullable = 'YES'
else 
begin --IF @IsNullable = 'NO'
set @sqlTxtToPrint = @sqlTxtToPrint +  
'[' + @ColumnName + ']['  + @DataType  +     
'] NOT NULL,'+ CHAR(13)
 
end --IF @IsNullable = 'NO'
-- print 'obj1.' + @ColumnName+ ' = obj2.'+ @ColumnName                    
-- print @ColumnName    --SIMPLE PRINT                    
--Increment loop counter                        
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1
END                        
 
set @sqlTxtToPrint = @sqlTxtToPrint +  '[SqlLogin] [varchar](50) NOT NULL,
[Action] [varchar](50) NOT NULL,
[UpdatedWhen] [datetime] NOT NULL 
CONSTRAINT [PK_' + @TableName + 'History] PRIMARY KEY CLUSTERED 
(
[' + @PkColName + '] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
 
GO
SET ANSI_PADDING ON
 
'
--NOW ADD SOME USEFULL STATEMENTS 
set @sqlTxtToPrint = @sqlTxtToPrint +  
+ 'SELECT NAME FROM SYS.PROCEDURES WHERE NAME LIKE ''%PROC%''' + CHAR(13)       
+ 'SELECT NAME FROM SYS.TABLES WHERE NAME LIKE ''%TABLE%'''    + CHAR(13)    
+ 'exec sp_HelpText procUtils_GenerateMetaInsert'        + CHAR(13)
+ 'exec procUtils_GenerateMetaInsert ''TABLE'''        + CHAR(13)
 
--AT THE END PRINT THE RESULT 
PRINT @sqlTxtToPrint
 
END TRY        --end try                                      
BEGIN CATCH                                            
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +                                   
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +                     
CAST(ERROR_SEVERITY() AS varchar(10)) +                                   
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +                     
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))                                            
END CATCH                                            
END --procedure end                                                 
/*                     
USE [MyDb]                    
GO                    
SELECT NAME FROM SYS.tables where name like '%Msg%'                    
EXEC [dbo].[procUtils_GenerateHistoryTable] @TableName = N'Feat'              
 
*/ 
GO

Labels

perl (41) Cheat Sheet (25) how-to (24) windows (14) sql server 2008 (13) linux (12) oracle (12) sql (12) Unix (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) bash (6) code generation (6) Informatica (5) cheatsheet (5) energy (5) tsql (5) utilities (5) excel (4) future (4) generic (4) git cheat sheet (4) html (4) perl modules (4) programs (4) settings (4) sh (4) shortcuts (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) logging (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) python (3) sftp (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) architecture (2) ascii (2) awk (2) batch (2) cas (2) chrome extensions (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) excel shortcuts (2) export (2) file (2) free programs (2) informatica sql repository (2) linux cheat sheet (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nz netezza cheat sheet (2) nzsql (2) ora (2) prediction (2) publish (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) .virmrc vim settings configs (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) IDEA (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) JavaScript anchor html jquery (1) Khan Academy (1) LINUX UNIX BASH AND CYGWIN TIPS AND TRICKS (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Scala (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture input output (1) archive (1) arguments (1) avatar (1) aws cheat sheet cli (1) aws cli (1) aws cli amazon cheat sheet (1) aws elb (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash perl search and replace (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) chrome (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy paste (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) db2 cheat sheet (1) db2 starter ibm bash Linux (1) debt (1) diagram (1) dictionaries (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) freaky (1) functions (1) fusion research (1) german (1) git gitlab issues handling system (1) google cli (1) google code (1) google command line interface (1) gpg (1) ha (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ibm db2 cognos installation example db deployment provisioning (1) ideas (1) image (1) informatica oracle sql (1) informatica repo sql workflows sessions file source dir (1) informatica source files etl (1) install (1) isg-pub issue-tracker architecture (1) it management best practices (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash shell grep xargs (1) linux bash tips and t ricks (1) linux bash unix cygwin cheatsheet (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql cheat sheet (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz table count rows (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) openssl (1) oracle PL/SQL (1) oracle Perl perl (1) oracle installation usability (1) oracle number formatting format-model ora-sql oracle (1) oracle templates create table (1) oracle trigger generic autoincrement (1) oracle vbox virtual box cheat sheet (1) oracle virtual box cheat sheet (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) postgreSQL how-to (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scala ScalaFmt (1) scp (1) scripts (1) scsi (1) search and replace (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) sidebar (1) silicon (1) smells (1) smtp (1) software development (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) sshd cygwin (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) system design (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tmux .tmux.conf configuration (1) tmux efficiency bash (1) tool (1) ui code prototyping tips and tricks (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vbox virtual box cheat sheet (1) vim perl regex bash search for string (1) vim recursively hacks (1) vim starter (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) visual stuio (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows 8 (1) windows programs (1) windows reinstall (1) windows utility batch perl space Windows::Clipboard (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List