Cheat Sheet

At some point I'll turn this into something more useful but for now it's just so I don't lose it

Docker

• VOLUMES USED BY CONTAINER: docker inspect -f '{{ .Mounts }}' containerid

BASH

• REDIRECTION: | Passes STDOUT to STDIN of parameters, > writes STDOUT to file
• XARGS: echo -l /bin | xargs ls -l (will used STDOUT as parameter)
  • ls -1|grep -v "XMLFindAndReplace"|xargs -I {} mv {} ./.git/
  • List files in folder one per line, grep -v does an inverted match, -I {} takes the list and passes it to mv via {}
• SHOW PERMISSIONS: ls -l (first - flags directory then following 3x3 are RWE for owner, group, other)
• CHOWN: chown root:root file1 (Sets OWNER:GROUP of file1)
• CHMOD: chmod g+rw file1 (add group RW to file1, can use octal i.e. 777 will be RWE for all, 700 will be RWE for user and none for other)
• FIND: find ./ -iname "*.txt" (-type d = search for folders)
• GREP: grep -rli --include="*.js" "var SIM" ./* (r = recursive , l = return file names, i = case insensitive)
• GREP REGEX: grep -ioP '<[lm].*?".*?"' ./IDs.txt
  • (o = show match only, P = use PERL regex (to allow non greedy ?))
  • <[lm] match <l or <m, .*?" match until next "
  • '(?<=")#[A-Fa-f0-9]{6}(?=")' --- #[A-Fa-f0-9]{6} matches # followed by 6 hex characters, (?<=) and (?=) are positive look ahead/behinds that will match text if these are true
• SED TO REPLACE: sed -e 's/ //g' -e 's/id="/:/g' -e 's/<//g' -e 's/\"//g' | sort -u
  • -e is used to pass multiple patters, s/valuetofind/valuetosubstitute/g sort -u sorts unique outputs
• RENAME FILES: find ./ -iname "*pendemo*" | xargs -I {} rename 's/pendemo/xyz/g' {}
• GREP and SED:
  • grep -rli "pendemo" ./* | xargs -I {} sed -i 's/pendemo/mellon/g' {}
  • grep -rli "Pen Demo Pension Scheme" ./* | xargs -I {} sed -i 's/Pen Demo Pension Scheme/XYZ Retirement Benefits Plan/g' {}

GPG KEYGEN

• gpg --full-generate-key
• gpg -a --export-secret-key 2435FA363760E5872045685E4326EE37E6CF5B7A > ~/privatekey.asc
• gpg -a --export 2435FA363760E5872045685E4326EE37E6CF5B7A > ~/publickey.asc

GIT

• PUSH TO REMOTE WITH DIFFERENT NAME: git push -u origin localname:remotename
• E.G.: git push -u origin xyz:pensions/24663
• SET UPSTREAM FOR LOCAL BRANCH: git branch --set-upstream-to=origin/branchname

Command

• CHECK WHOS LOGGED ONTO REMOTE: qwinsta /server:server DISCONNECT WITH: rwinsta /server:server sessionid
• ROBOCOPY "C:\temp\images\" "\myserver\xyz" /E /XO /R:5
• GACUTIL: /c/Program\ Files\ (x86)/Microsoft\ SDKs/Windows/v10.0A/bin/NETFX\ 4.6\ Tools/gacutil.exe
• CHECK IF DLL INSTALLED: reg query HKLM\SOFTWARE\Classes /s /f whatever.dll
• CHECK DLL DEPENDANTS: /c/Program\ Files\ (x86)/Microsoft\ Visual\ Studio\ 14.0/VC/bin/amd64/dumpbin.exe /DEPENDENTS DLL
• FIND DLL PUBLIC KEY: /c/Program\ Files\ (x86)/Microsoft\ SDKs/Windows/v10.0A/bin/NETFX\ 4.6\ Tools/sn.exe -T DLL
• LIST ALL INSTALLED SERVICES: sc queryex type= service state= all
• netsh http add urlacl url=http://+:8890/ user=Everyone

SQL Server

• TRUNCATE DATE: dateadd(dd, datediff(dd,0, getDate()), 0)
• DATEDIFF: DATEDIFF(Interval, Date1, Date2) Interval: year, quarter, month, dayofyear, day, week, weekday, hour, minute, second, millisecond, microsecond
• DATEADD: DATEADD(Interval, amount, Date)
• FIRST DAY OF THE MONTH: dateadd("day",-day(date)+1,date)
• LAST DAY OF THE MONTH: dateadd("day",-day(date),dateadd("month",1,date))
• CHARINDEX: CHARIDEX('String to Find','String to Search')
• ADD ROWNMBER: row_number() over(order by memberid) as persno
• CREATE PSUEDO TABLE: SELECT mytable.rag FROM (VALUES('Green'),('Amber'),('Red')) as MyTable(rag)
• CASE: CASE
• WHEN MIN(value) <= 0 THEN 0 WHEN MAX(1/value) >= 100 THEN 1
• ELSE 'Else'
• END
• FIND TABLES COLUMN IS ON:
  • SELECT COLUMN_NAME AS 'ColumnName'
, TABLE_NAME AS 'TableName'
, 'SELECT ' + table_name + ', ' + column_name + ', COUNT(*) FROM [' + TABLE_NAME + '] where [' + COLUMN_NAME + '] = 22377;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%UNO%'
ORDER BY TableName
, ColumnName;
• FIND REFERENCING OBJECTS:
  • select distinct [Table Name] = o.Name, [Found In] = sp.Name, sp.type_desc
from sys.objects o inner join sys.sql_expression_dependencies sd on o.object_id = sd.referenced_id
inner join sys.objects sp on sd.referencing_id = sp.object_id
and sp.type in ('P', 'FN', 'V')
where o.name in ('tab_CMSOPENclient','tabTabsClient', 'vewtabTabsClient', 'vewHKRptClientListsForHAWKandCMSOpen', 'sp_RefreshTaBsClient')
order by sp.Name
• FIND REFERENCING COLUMNS:
  • SELECT sys.objects.object_id, sys.schemas.name AS [Schema], sys.objects.name AS Object_Name, sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK)
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
sys.sql_modules.definition COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%{Column Name}%' ESCAPE '\'
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name
• Full Text Search:

  • DECLARE @TableList TABLE(TABLE_NAME varchar(128))
    DECLARE @Table varchar(128)
    DECLARE @ColumnList TABLE(COLUMN_NAME varchar(128))
    DECLARE @Column varchar(128)
    DECLARE @Count int, @TableCount int, @i int
    DECLARE @Search varchar(128)
    DECLARE @SQL nvarchar(max)
    SET NOCOUNT ON;
    
    set @Search = 'Bespoke Team 1'
    
    INSERT @TableList
    select table_name from information_schema.tables where table_name like '%rpt%' and table_type = 'VIEW'
    
    SELECT @TableCount = COUNT(*) From @TableList
    SET @i = 0
    
    WHILE(1 = 1)
    BEGIN
                
        SET @Table = NULL
        SELECT TOP(1) @Table = table_name
        FROM @TableList
        
    	SET @i = @i + 1
    	PRINT CONCAT('Table ', @i, '/', @TableCount)
    
        IF @Table IS NULL
            BREAK
                
        /* Column Loop */
    
    		INSERT @ColumnList
    		select COLUMN_NAME from information_schema.COLUMNS where table_name = @Table and data_type in ('varchar', 'nvarchar') and column_name not in ('Scheme', 'Action', 'Insurer')
    
    		WHILE(1 = 1)
    		BEGIN
                
    			SET @Column = NULL
    			SET @Count = 0
    			SELECT TOP(1) @Column = Column_name
    			FROM @ColumnList
        
    			IF @Column IS NULL
    				BREAK
    
    			
    			--SET @SQL = CONCAT('SELECT @COUNT = COUNT(*) FROM ', @Table, ' WHERE ', @Column , ' like ''%', @Search, '%'' ')
    			SET @SQL = CONCAT('SELECT @COUNT = COUNT(*) FROM [', @Table, '] WHERE [', @Column , '] = ''', @Search,''' ')
    			EXEC sp_EXECUTESQL @SQL, N'@Count INT OUTPUT', @count OUTPUT
    			
    			IF @Count > 0
    				PRINT CONCAT('*****', @Table, ' - ', @Column , ' - ', @Count)
    
    			DELETE TOP(1) FROM @ColumnList
        
    		END
    
    	/* End Column Loop */
        
        DELETE TOP(1) FROM @TableList
        
    END