User:Rajubaladaniya/sandbox

CREATE PROCEDURE UGLYSEARCH -- EXEC UGLYSEARCH 'TEST'

@SEARCHSTRING VARCHAR(50) AS SET NOCOUNT ON DECLARE @SQL VARCHAR(500), @TABLENAME VARCHAR(60), @COLUMNNAME VARCHAR(60)

CREATE TABLE #RESULTS(TBLNAME VARCHAR(60),COLNAME VARCHAR(60),SQL VARCHAR(600)) SELECT SYSOBJECTS.NAME AS TBLNAME, SYSCOLUMNS.NAME AS COLNAME, TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE INTO #FKFINDER FROM SYSOBJECTS INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID WHERE SYSOBJECTS.XTYPE='U' AND TYPE_NAME(SYSCOLUMNS.XTYPE) IN ('VARCHAR','NVARCHAR','CHAR','NCHAR') ORDER BY TBLNAME,COLNAME

DECLARE C1 CURSOR FOR SELECT TBLNAME,COLNAME FROM #FKFINDER ORDER BY TBLNAME,COLNAME OPEN C1 FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME WHILE @@FETCH_STATUS <> -1 BEGIN --SET @SQL = 'SELECT  + @TABLENAME +  AS TABLENAME, + @COLUMNNAME +  AS COLUMNNAME,* FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE %' + @SEARCHSTRING + '%' SET @SQL = 'IF EXISTS(SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE %' + @SEARCHSTRING + '%) INSERT INTO #RESULTS(TBLNAME,COLNAME,SQL) VALUES( + @TABLENAME + , + @COLUMNNAME + , SELECT * FROM ' + @TABLENAME + ' WHERE ' + @COLUMNNAME + ' LIKE  + @SEARCHSTRING +  ) ;' PRINT @SQL EXEC (@SQL) FETCH NEXT FROM C1 INTO @TABLENAME,@COLUMNNAME END CLOSE C1 DEALLOCATE C1

SELECT * FROM #RESULTS

=
=======================================================

DECLARE @db_name TABLE(R_DB int, dbname VARCHAR(1000)) DECLARE @tbl_name TABLE(R_T int, dbname VARCHAR(1000), tblname VARCHAR(1000), colname VARCHAR(1000), datatype VARCHAR(1000), queries VARCHAR(4000), wherecre VARCHAR(100), ccount INT) DECLARE @vtp TABLE(I INT)

DELETE FROM @db_name INSERT INTO @db_name SELECT TOP 1 ROW_NUMBER OVER(ORDER BY name) AS R_DB, name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')

DECLARE @db_count int = (SELECT COUNT(*) FROM @db_name) WHILE (@db_count> 0) BEGIN

DECLARE @vdbname varchar(100) = (SELECT dbname FROM @db_name WHERE R_DB = @db_count) DELETE FROM @tbl_name INSERT INTO @tbl_name EXEC('USE ['+@vdbname+'] SELECT ROW_NUMBER OVER(ORDER BY TABLE_CATALOG) AS R_Num, TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE, SELECT COUNT(*) FROM [+ TABLE_CATALOG +]..[+TABLE_NAME+] WHERE [+ COLUMN_NAME +] LIKE ,'%India%',0 FROM INFORMATION_SCHEMA.COLUMNS as a WHERE DATA_TYPE IN (varchar, nvarchar, text) AND exists(select * from sys.tables as t where t.name = a.TABLE_NAME)') DECLARE @tbl_count int = (SELECT COUNT(*) FROM @tbl_name) WHILE (@tbl_count> 0) BEGIN DECLARE @vtblname varchar(2500) = (SELECT queries+ wherecre FROM @tbl_name WHERE R_T = @tbl_count) DELETE FROM @vtp INSERT INTO @vtp EXEC(@vtblname)

UPDATE @tbl_name SET ccount = (SELECT I FROM @vtp) WHERE R_T = @tbl_count

SET @tbl_count = @tbl_count -1 END

SET @db_count = @db_count -1 END

SELECt * FROM @tbl_name WHERE ccount =1