Tag Archives: database

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

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

 

 

 

 

 

 

Find current location of data and log files of all the databases

27 Aug

In MS SQL 2012 – you can run the following select to check where the files are for databases:

SELECT name, physical_name AS current_file_location
FROM sys.master_files;