I have a PHP script which behaves like PhpMyAdmin, in that it automatically lists a database structure and the fields to modify similar to an excel spreadsheet. Now without using PHP, I need standalone SQL scripts that can simply be used to search an entire database for a particular string of word(s).
I've posted my own as well as some others I've lifted from elsewhere, as they worked with my environment, to put them in one place on a website I've bookmarked (my personal site :c)
My own with Typos
Note that the below only prints out the SQL query to copy and paste into your SQL administration software. You can put a distinct on it (not forgetting the collate). This example is expanded in a further article called "Search a database with soundex":
/* USING A CURSOR */ DECLARE @SqlToExecute nvarchar(max); DECLARE @mySearchString varchar(50); SET @mySearchString = 'dnya'; DECLARE MyCursor CURSOR FOR SELECT 'SELECT ' + COLUMN_NAME + ' COLLATE Latin1_General_CS_AS AS column1 FROM ' + (TABLE_SCHEMA + '.' + TABLE_NAME) + ' WHERE SOUNDEX(' + COLUMN_NAME + ')=SOUNDEX('''+ @mySearchString +''')' + CHAR(10) FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext') AND TABLE_SCHEMA='dbo' ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME; OPEN MyCursor FETCH NEXT FROM MyCursor INTO @SqlToExecute WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' union all ' + @SqlToExecute; FETCH NEXT FROM MyCursor INTO @SqlToExecute END CLOSE MyCursor DEALLOCATE MyCursor
- /* USING A CURSOR */
- DECLARE @SqlToExecute nvarchar(max);
- DECLARE @mySearchString varchar(50);
- SET @mySearchString = 'dnya';
- DECLARE MyCursor CURSOR FOR
- SELECT
- 'SELECT ' + COLUMN_NAME + ' COLLATE Latin1_General_CS_AS AS column1 FROM ' + (TABLE_SCHEMA + '.' + TABLE_NAME) + ' WHERE SOUNDEX(' + COLUMN_NAME + ')=SOUNDEX('''+ @mySearchString +''')' + CHAR(10)
- FROM
- INFORMATION_SCHEMA.COLUMNS
- WHERE
- DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')
- AND TABLE_SCHEMA='dbo'
- ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
- OPEN MyCursor
- FETCH NEXT FROM MyCursor INTO @SqlToExecute
- WHILE @@FETCH_STATUS = 0
- BEGIN
- PRINT ' union all ' + @SqlToExecute;
- FETCH NEXT FROM MyCursor INTO @SqlToExecute
- END
- CLOSE MyCursor
- DEALLOCATE MyCursor
T-SQL
Source: http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58 - Sorna Kumar
IF OBJECT_ID('usp_SearchDB','P') IS NOT NULL DROP PROCEDURE usp_SearchDB GO CREATE PROCEDURE usp_SearchDB @Tablenames VARCHAR(500) ,@SearchStr NVARCHAR(60) ,@GenerateSQLOnly Bit = 0 AS /* Parameters and usage @Tablenames -- Provide a single table name or multiple table name with comma seperated. If left blank , it will check for all the tables in the database @SearchStr -- Provide the search string. Use the '%' to coin the search. EX : X%--- will give data staring with X %X--- will give data ending with X %X%--- will give data containig X @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database. By default it is 0 and it will search. Samples : 1. To search data in a table EXEC SearchTables @Tablenames = 'T1' ,@SearchStr = '%TEST%' The above sample searches in table T1 with string containing TEST. 2. To search in a multiple table EXEC SearchTables @Tablenames = 'T2' ,@SearchStr = '%TEST%' The above sample searches in tables T1 & T2 with string containing TEST. 3. To search in a all table EXEC SearchTables @Tablenames = '%' ,@SearchStr = '%TEST%' The above sample searches in all table with string containing TEST. 4. Generate the SQL for the Select statements EXEC SearchTables @Tablenames = 'T1' ,@SearchStr = '%TEST%' ,@GenerateSQLOnly = 1 */ SET NOCOUNT ON DECLARE @CheckTableNames Table ( Tablename sysname ) DECLARE @SQLTbl TABLE ( Tablename SYSNAME ,WHEREClause VARCHAR(MAX) ,SQLStatement VARCHAR(MAX) ,Execstatus BIT ) DECLARE @sql VARCHAR(MAX) DECLARE @tmpTblname sysname IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') BEGIN INSERT INTO @CheckTableNames SELECT Name FROM sys.tables END ELSE BEGIN SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' INSERT INTO @CheckTableNames EXEC(@sql) END INSERT INTO @SQLTbl ( Tablename,WHEREClause) SELECT SCh.name + '.' + ST.NAME, ( SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) FROM SYS.columns SC JOIN SYS.types STy ON STy.system_type_id = SC.system_type_id AND STy.user_type_id =SC.user_type_id WHERE STY.name in ('varchar','char','nvarchar','nchar') AND SC.object_id = ST.object_id ORDER BY SC.name FOR XML PATH('') ) FROM SYS.tables ST JOIN @CheckTableNames chktbls ON chktbls.Tablename = ST.name JOIN SYS.schemas SCh ON ST.schema_id = SCh.schema_id WHERE ST.name 'SearchTMP' GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ; UPDATE @SQLTbl SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) DELETE FROM @SQLTbl WHERE WHEREClause IS NULL WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) BEGIN SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0 IF @GenerateSQLOnly = 0 BEGIN IF OBJECT_ID('SearchTMP','U') IS NOT NULL DROP TABLE SearchTMP EXEC (@SQL) IF EXISTS(SELECT 1 FROM SearchTMP) BEGIN SELECT Tablename=@tmpTblname,* FROM SearchTMP END END ELSE BEGIN PRINT REPLICATE('-',100) PRINT @tmpTblname PRINT REPLICATE('-',100) PRINT replace(@sql,'INTO SearchTMP','') END UPDATE @SQLTbl SET Execstatus = 1 WHERE Tablename = @tmpTblname END SET NOCOUNT OFF GO
- IF OBJECT_ID('usp_SearchDB','P') IS NOT NULL
- DROP PROCEDURE usp_SearchDB
- GO
- CREATE PROCEDURE usp_SearchDB
- @Tablenames VARCHAR(500)
- ,@SearchStr NVARCHAR(60)
- ,@GenerateSQLOnly Bit = 0
- AS
- /*
- Parameters and usage
- @Tablenames -- Provide a single table name or multiple table name with comma seperated.
- If left blank , it will check for all the tables in the database
- @SearchStr -- Provide the search string. Use the '%' to coin the search.
- EX : X%--- will give data staring with X
- %X--- will give data ending with X
- %X%--- will give data containig X
- @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.
- By default it is 0 and it will search.
- Samples :
- 1. To search data in a table
- EXEC SearchTables @Tablenames = 'T1'
- ,@SearchStr = '%TEST%'
- The above sample searches in table T1 with string containing TEST.
- 2. To search in a multiple table
- EXEC SearchTables @Tablenames = 'T2'
- ,@SearchStr = '%TEST%'
- The above sample searches in tables T1 & T2 with string containing TEST.
- 3. To search in a all table
- EXEC SearchTables @Tablenames = '%'
- ,@SearchStr = '%TEST%'
- The above sample searches in all table with string containing TEST.
- 4. Generate the SQL for the Select statements
- EXEC SearchTables @Tablenames = 'T1'
- ,@SearchStr = '%TEST%'
- ,@GenerateSQLOnly = 1
- */
- SET NOCOUNT ON
- DECLARE @CheckTableNames Table
- (
- Tablename sysname
- )
- DECLARE @SQLTbl TABLE
- (
- Tablename SYSNAME
- ,WHEREClause VARCHAR(MAX)
- ,SQLStatement VARCHAR(MAX)
- ,Execstatus BIT
- )
- DECLARE @sql VARCHAR(MAX)
- DECLARE @tmpTblname sysname
- IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
- BEGIN
- INSERT INTO @CheckTableNames
- SELECT Name
- FROM sys.tables
- END
- ELSE
- BEGIN
- SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + ''''
- INSERT INTO @CheckTableNames
- EXEC(@sql)
- END
- INSERT INTO @SQLTbl
- ( Tablename,WHEREClause)
- SELECT SCh.name + '.' + ST.NAME,
- (
- SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10)
- FROM SYS.columns SC
- JOIN SYS.types STy
- ON STy.system_type_id = SC.system_type_id
- AND STy.user_type_id =SC.user_type_id
- WHERE STY.name in ('varchar','char','nvarchar','nchar')
- AND SC.object_id = ST.object_id
- ORDER BY SC.name
- FOR XML PATH('')
- )
- FROM SYS.tables ST
- JOIN @CheckTableNames chktbls
- ON chktbls.Tablename = ST.name
- JOIN SYS.schemas SCh
- ON ST.schema_id = SCh.schema_id
- WHERE ST.name 'SearchTMP'
- GROUP BY ST.object_id, SCh.name + '.' + ST.NAME ;
- UPDATE @SQLTbl
- SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5)
- DELETE FROM @SQLTbl
- WHERE WHEREClause IS NULL
- WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
- BEGIN
- SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement
- FROM @SQLTbl
- WHERE ISNULL(Execstatus ,0) = 0
- IF @GenerateSQLOnly = 0
- BEGIN
- IF OBJECT_ID('SearchTMP','U') IS NOT NULL
- DROP TABLE SearchTMP
- EXEC (@SQL)
- IF EXISTS(SELECT 1 FROM SearchTMP)
- BEGIN
- SELECT Tablename=@tmpTblname,* FROM SearchTMP
- END
- END
- ELSE
- BEGIN
- PRINT REPLICATE('-',100)
- PRINT @tmpTblname
- PRINT REPLICATE('-',100)
- PRINT replace(@sql,'INTO SearchTMP','')
- END
- UPDATE @SQLTbl
- SET Execstatus = 1
- WHERE Tablename = @tmpTblname
- END
- SET NOCOUNT OFF
- GO
MySQL
Source: http://kedar.nitty-witty.com/blog/search-through-all-databases-tables-columns-in-mysql - Kedar at Nitty-Witty.com
## Procedure for search in all fields of all databases DELIMITER $$ #Script to loop through all tables using Information_Schema DROP PROCEDURE IF EXISTS get_table $$ CREATE PROCEDURE get_table(in_search varchar(50)) READS SQL DATA BEGIN DECLARE trunc_cmd VARCHAR(50); DECLARE search_string VARCHAR(250); DECLARE db,tbl,clmn CHAR(50); DECLARE done INT DEFAULT 0; DECLARE COUNTER INT; DECLARE table_cur CURSOR FOR SELECT concat('SELECT COUNT(*) INTO @CNT_VALUE FROM `',table_schema,'`.`',table_name,'` WHERE `', column_name,'` REGEXP "',in_search,'"') ,table_schema,table_name,column_name FROM information_schema.COLUMNS WHERE TABLE_SCHEMA IN ('network_detail'); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; #Truncating table for refill the data for new search. PREPARE trunc_cmd FROM "TRUNCATE TABLE temp_details;"; EXECUTE trunc_cmd ; OPEN table_cur; table_loop:LOOP FETCH table_cur INTO search_string,db,tbl,clmn; #Executing the search SET @search_string = search_string; #SELECT search_string; PREPARE search_string FROM @search_string; EXECUTE search_string; SET COUNTER = @CNT_VALUE; #SELECT COUNTER; IF COUNTER>0 THEN # Inserting required results from search to table INSERT INTO temp_details VALUES(db,tbl,clmn); END IF; IF done=1 THEN LEAVE table_loop; END IF; END LOOP; CLOSE table_cur; #Finally Show Results SELECT concat("SELECT * FROM ",t_table, " WHERE ", t_field, " REGEXP '", in_search, "';") FROM temp_details; END $$ DELIMITER ;