ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Search string value in all Columns and Tables in SQL Server<p>I will get back soon.</p>https://www.aspforums.net:443/Threads/265415/Search-string-value-in-all-Columns-and-Tables-in-SQL-Server/https://www.aspforums.net:443/Threads/265415/Search-string-value-in-all-Columns-and-Tables-in-SQL-Server/Tue, 06 Aug 2019 00:40:48 GMTSearch string value in all Columns and Tables in SQL Server<p>Try below.</p> <pre class="brush: sql">USE DATABASE_NAME DECLARE @SearchStr nvarchar(100) = &#39;SEARCH_TEXT&#39; DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = &#39;&#39; SET @SearchStr2 = QUOTENAME(&#39;%&#39; + @SearchStr + &#39;%&#39;,&#39;&#39;&#39;&#39;) WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = &#39;&#39; SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + &#39;.&#39; + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = &#39;BASE TABLE&#39; AND QUOTENAME(TABLE_SCHEMA) + &#39;.&#39; + QUOTENAME(TABLE_NAME) &gt; @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + &#39;.&#39; + QUOTENAME(TABLE_NAME) ), &#39;IsMSShipped&#39; ) = 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 (&#39;char&#39;, &#39;varchar&#39;, &#39;nchar&#39;, &#39;nvarchar&#39;, &#39;int&#39;, &#39;decimal&#39;) AND QUOTENAME(COLUMN_NAME) &gt; @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO @Results EXEC ( &#39;SELECT &#39;&#39;&#39; + @TableName + &#39;.&#39; + @ColumnName + &#39;&#39;&#39;, LEFT(&#39; + @ColumnName + &#39;, 3630) FROM &#39; + @TableName + &#39; (NOLOCK) &#39; + &#39; WHERE &#39; + @ColumnName + &#39; LIKE &#39; + @SearchStr2 ) END END END SELECT ColumnName, ColumnValue FROM @Results</pre> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/265415/Search-string-value-in-all-Columns-and-Tables-in-SQL-Server/https://www.aspforums.net:443/Threads/265415/Search-string-value-in-all-Columns-and-Tables-in-SQL-Server/Tue, 06 Aug 2019 02:23:21 GMTSearch string value in all Columns and Tables in SQL Server<p>Hi <a class="username" rel="anand.mand"> anand.mand</a>,</p> <p>Refer below links -</p> <p><a href="https://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server">https://stackoverflow.com/questions/1796506/search-all-tables-all-columns-for-a-specific-value-sql-server</a></p> <p><a href="https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/">https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/</a></p>https://www.aspforums.net:443/Threads/265415/Search-string-value-in-all-Columns-and-Tables-in-SQL-Server/https://www.aspforums.net:443/Threads/265415/Search-string-value-in-all-Columns-and-Tables-in-SQL-Server/Tue, 06 Aug 2019 04:49:34 GMT