Archive | Database RSS feed for this section

MS SQL: Search keyword in the whole database

16 Dec

How to search for a keyword in the whole database. You need to do the following:

1: Create a procedure

CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN
	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
	SET NOCOUNT ON
	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)
		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END
	SELECT ColumnName, ColumnValue FROM #Results
END

2: Run the procedure
exec SearchAllTables 'Searchstring'
GO

Read more:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Delete old files in Windows and Oracle

24 Jul

Oracle produces a lot of trace and log files. Take a backup of the files before renaming/removing them. For the trace files – it’s wise to keep 1-2 week of alert/trace files in the same directory. Here’s an example to create a bat-file which you can schedule to delete old files (older than 14 days:

forfiles /p D:\App\Oracle\diag\rdbms\ora01\ora01\alert\ /s /m *.* /D -14 /C “cmd /c del /F /Q @path”

Oracle will automatically create a new alert log files when you rename/delete existing file.

Read more

https://community.oracle.com/thread/3544775?start=0&tstart=0

http://dba.stackexchange.com/questions/15715/how-to-free-up-disk-space-which-logs-directories-to-clean

https://community.oracle.com/thread/2297940?tstart=0

http://ss64.com/nt/forfiles.html

http://ss64.com/nt/del.html

http://stackoverflow.com/questions/51054/batch-file-to-delete-files-older-than-n-days

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:402360068979

http://dba-masters.blogspot.no/2012/08/11g-r2-trace-files-and-alert-logs.html

http://stackoverflow.com/questions/7160342/how-to-skip-are-you-sure-y-n-when-deleting-files-in-batch-files

http://www.databasejournal.com/features/oracle/article.php/3875896/Purging-Oracle-Databases-Alert-Log-with-ADRCI—Usage-and-Warning.htm

How do I list or search all the column names in my database – ms sql

3 Jun

Here is an example of a query that does the job:

 

USE dbname
GO

SELECT t.name AS table_name,
SCHEMA_NAME (schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%something%’
ORDER BY schema_name, table_name;

 

 

MS SQL – method to backup all instances/databases through script

10 Feb

Sometimes you need to backup all of the instances and databases in a particular server. One way is to script everything and create a schedule task in Windows to run it.

 

1: Create a sql-file with:

DECLARE @name VARCHAR (100) — database name
DECLARE @path VARCHAR (256) — path for backup files
DECLARE @fileName VARCHAR (256) — filename for backup
DECLARE @fileDate VARCHAR (40) — used for file name

— specify database backup directory
SET @path = ‘E:\MSSQL\Backup\’

— specify filename format
–SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
SELECT @fileDate =
CONVERT (VARCHAR (20), GETDATE (), 112)
+ REPLACE (CONVERT (VARCHAR (20), GETDATE (), 108), ‘:’, ”)

DECLARE
db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,
‘model’,
‘msdb’,
‘tempdb’) — exclude these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
–PRINT @name
FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

2: Create a cmd-file with (backup instances default, instanceaa, instancebb):

sqlcmd -S servernameXX -i”c:\temp\backup_all_databases.sql”
sqlcmd -S servernameXX\INSTANCEAA -i”c:\temp\backup_all_databases.sql”
sqlcmd -S servernameXX\INSTANCEBB-i”c:\temp\backup_all_databases.sql”

3: Create a schedule task to rund cmd-file e.g everyday

 

Read more

http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

 

MS SQL – disable constraint to update/delete records

30 Jan

In the relational db you have many relations, and in order to delete a certain record you have to delete all of the relations that record have in all the referenced tables. One way is to temporary disable the constraints – update the records – enable the constraints. You can use the command below:

* Disable constraints for all tables:
EXEC sp_msforeachtable "ALTER TABLE XX NOCHECK CONSTRAINT all"

* Re-enable constraints for all tables:
EXEC sp_msforeachtable "ALTER TABLE XX WITH CHECK CHECK CONSTRAINT all"


* Disable the constraints on a table called tableName:
ALTER TABLE tableXX NOCHECK CONSTRAINT ALL

* Re-enable the constraints on a table called tableName:
ALTER TABLE tableXX WITH CHECK CHECK CONSTRAINT ALL

Read more:
http://stackoverflow.com/questions/737115/turn-off-constraints-temporarily-ms-sql
http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql/161410#161410


Mysql – Copy database to another server

30 Jan

I used the sqlyog to copy database – which you can download from:

https://code.google.com/p/sqlyog/

https://code.google.com/p/sqlyog/wiki/Downloads

 

Or you can use sqldump command:

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver 

Can read more:

http://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another

http://stackoverflow.com/questions/25794/mysql-copy-duplicate-database

http://serverfault.com/questions/194462/any-way-to-copy-the-schema-from-one-mysql-database-to-another

http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html

http://dev.mysql.com/doc/refman/5.6/en/copying-databases.html

 

 

 

 

 

 

Log Backup Failed to Complete

12 Nov

When you get this alert in the MS SQL 2012 server, then you probably haven’t managed to perform a full backup and transaction log backup of the database after creating the database the first time. This is when you have “Recovery model” set to full. Run this command in the editor for that database:

BACKUP DATABASE [DATABASENN] TO DISK = N’e:\mssql\backup\DATABASENN_1.bak’
GO
BACKUP LOG [DATABASENN] TO DISK = N’e:\mssql\backup\DATABASENN_1log.bak’
GO

Read more:

http://support2.microsoft.com/kb/2646329

http://stackoverflow.com/questions/3131258/backup-failed-to-complete-the-command-backup-database

http://blog.sqlauthority.com/2010/03/27/sql-server-fix-error-4214-backup-log-cannot-be-performed-because-there-is-no-current-database-backup/