NativeSoft

    SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = ‘FOREIGN KEY’ UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS […]

# cd /etc/cron.daily/ # touch /etc/cron.daily/dbbackup-daily.sh # chmod 755 /etc/cron.daily/dbbackup-daily.sh # vi /etc/cron.daily/dbbackup-daily.sh Then copy following lines into file with Shift+Ins #!/bin/sh now=”$(date +’%d_%m_%Y_%H_%M_%S’)” filename=”db_backup_$now”.gz backupfolder=”/var/www/vhosts/example.com/httpdocs/backups” fullpathbackupfile=”$backupfolder/$filename” logfile=”$backupfolder/”backup_log_”$(date +’%Y_%m’)”.txt echo “mysqldump started at $(date +’%d-%m-%Y %H:%M:%S’)” >> “$logfile” mysqldump –user=mydbuser –password=mypass –default-character-set=utf8 mydatabase | gzip > “$fullpathbackupfile” echo “mysqldump finished at $(date +’%d-%m-%Y %H:%M:%S’)” >> […]

To get the InnoDB recomended pool size SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf10243,0,PowerOf1024)))+0.49999), SUBSTR(‘ KMG’,IF(PowerOf10243,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables WHERE engine=’InnoDB’) A, (SELECT 2 PowerOf1024) B;

DECLARE @tcp_port nvarchar(5) EXEC xp_regread @rootkey = ‘HKEY_LOCAL_MACHINE’, @key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’, @value_name = ‘TcpPort’, @value = @tcp_port OUTPUT select @tcp_port

The below SQL will provide the backup database instructions needed. It is working on MSSQL 2000 – > 2008 Modify the @PATH variable as needed. declare @PATH VARCHAR(1000) SELECT @PATH=’\\server_name\database_backup\’ DECLARE @SRVNAME VARCHAR(100) SELECT @SRVNAME=COALESCe(@@SERVERNAME, ‘server_not_defined’) select ‘backup database ‘ + name + ‘ to disk=”’ + @path + @SRVNAME + ‘_’ + name + […]

/********************************************************************************************************************** Purpose: Returns a single result set similar to sp_Space used for all user tables at once. Notes: 1. May be used as a view, stored procedure, or table-valued funtion. 2. Must comment out 1 “Schema” in the SELECT list below prior to use. See the adjacent comments for more info. Revision History: Rev 00 […]

We struggled with DB2 error codes so we think this page will be useful for all the people having the same struggle: find what a DB2 error code means. IBM offered a pdf document with all the codes, we put that document into a database that can be easily queried. DB2 Error Code Details

Change the registry key located in: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer See attached image:

1 REMAP VALUES INSIDE A QUERY USING CASE…WHEN SYNTAX   SELECT id,title,    (CASE date WHEN ‘0000-00-00’ THEN ” ELSE date END) AS date    FROM your_table SELECT id,title,    (CASE status WHEN 0 THEN ‘open’ WHEN 1 THEN ‘close’ ELSE ‘standby’ END) AS status     FROM your_table 2 FIND DUPLICATE RECORDS WITH EMAIL FIELD SELECT email, COUNT(email) AS q    FROM emails_table GROUP BY email HAVING q > 1     ORDER BY q DESC 3 EXTRACT RECORDS WITH A RANDOM ORDER   SELECT * FROM your_table ORDER BY RAND() 4 RESET THE AUTOINCREMENT COUNTER IN A TABLE   ALTER TABLE your_table AUTO_INCREMENT = 100 […]