مدونة طلال

</p>
<p>
USE master<br />
GO 
</p>
<p>
CREATE PROCEDURE sp_FindStringInTable @stringToFind VARCHAR(100), @SCHEMA sysname, @TABLE sysname<br />
AS 
</p>
<p>
DECLARE @sqlCommand VARCHAR(8000)<br />
DECLARE @WHERE VARCHAR(8000)<br />
DECLARE @columnName sysname<br />
DECLARE @CURSOR VARCHAR(8000) 
</p>
<p>
BEGIN TRY<br />
 SET @sqlCommand = &#39;SELECT * FROM &#39; + @SCHEMA + &#39;.&#39; + @TABLE + &#39; WHERE&#39;<br />
 SET @WHERE = &#39;&#39; 
</p>
<p>
 SET @CURSOR = &#39;DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME<br />
 FROM &#39; + DB_NAME() + &#39;.INFORMATION_SCHEMA.COLUMNS<br />
 WHERE TABLE_SCHEMA = &#39;&#39;&#39; + @SCHEMA + &#39;&#39;&#39;<br />
 AND TABLE_NAME = &#39;&#39;&#39; + @TABLE + &#39;&#39;&#39;<br />
 AND DATA_TYPE IN (&#39;&#39;char&#39;&#39;,&#39;&#39;nchar&#39;&#39;,&#39;&#39;ntext&#39;&#39;,&#39;&#39;nvarchar&#39;&#39;,&#39;&#39;text&#39;&#39;,&#39;&#39;varchar&#39;&#39;)&#39; 
</p>
<p>
 EXEC (@CURSOR) 
</p>
<p>
 OPEN col_cursor <br />
 FETCH NEXT FROM col_cursor INTO @columnName 
</p>
<p>
 WHILE @@FETCH_STATUS = 0 <br />
 BEGIN <br />
 IF @WHERE <> &#39;&#39;<br />
 SET @WHERE = @WHERE + &#39; OR&#39; 
</p>
<p>
 SET @WHERE = @WHERE + &#39; &#39; + @columnName + &#39; LIKE &#39;&#39;&#39; + @stringToFind + &#39;&#39;&#39;&#39;<br />
 FETCH NEXT FROM col_cursor INTO @columnName <br />
 END 
</p>
<p>
 CLOSE col_cursor <br />
 DEALLOCATE col_cursor 
</p>
<p>
 SET @sqlCommand = @sqlCommand + @where<br />
 --PRINT @sqlCommand<br />
 EXEC (@sqlCommand) <br />
END TRY<br />
BEGIN CATCH<br />
 PRINT &#39;There was an error&#39;<br />
 IF CURSOR_STATUS(&#39;variable&#39;, &#39;col_cursor&#39;) <> -3<br />
 BEGIN<br />
 CLOSE col_cursor <br />
 DEALLOCATE col_cursor <br />
 END<br />
END CATCH 
</p>
<p>

</p>
<p>
USE AdventureWorks<br />
GO <br />
EXEC sp_FindStringInTable &#39;%land%&#39;, &#39;Person&#39;, &#39;Contact&#39; 
</p>
<p>