{"id":5828,"date":"2016-11-26T05:29:19","date_gmt":"2016-11-26T05:29:19","guid":{"rendered":"http:\/\/www.jitendrazaa.com\/blog\/?p=5828"},"modified":"2016-11-26T05:41:05","modified_gmt":"2016-11-26T05:41:05","slug":"sql-server-search-complete-database-for-value","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-search-complete-database-for-value\/","title":{"rendered":"SQL Server \u2013 Search complete database for value"},"content":{"rendered":"<p>Recently, I was in need to search complete Database for some value and found below very useful script (T-SQL)<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nDECLARE @SearchStr nvarchar(100) = 'SEARCHSTRING'\r\nDECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))\r\n\r\nSET NOCOUNT ON\r\n\r\nDECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)\r\nSET  @TableName = ''\r\nSET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')\r\n\r\n\r\nWHILE @TableName IS NOT NULL\r\n\r\nBEGIN\r\n    SET @ColumnName = ''\r\n\tBEGIN TRY\r\n    SET @TableName = \r\n    (\r\n        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))\r\n        FROM     INFORMATION_SCHEMA.TABLES\r\n        WHERE         TABLE_TYPE = 'BASE TABLE'\r\n            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) &gt; @TableName\r\n            AND    OBJECTPROPERTY(\r\n                    OBJECT_ID(\r\n                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)\r\n                         ), 'IsMSShipped'\r\n                           ) = 0\r\n    )\r\nEND TRY\r\nBEGIN CATCH\r\nEND CATCH\r\n    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)\r\n\r\n    BEGIN\r\n        SET @ColumnName =\r\n        (\r\n            SELECT MIN(QUOTENAME(COLUMN_NAME))\r\n            FROM     INFORMATION_SCHEMA.COLUMNS\r\n            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)\r\n                AND    TABLE_NAME    = PARSENAME(@TableName, 1) \r\n                AND    QUOTENAME(COLUMN_NAME) &gt; @ColumnName \r\n        )\r\n         \r\n\r\n        IF @ColumnName IS NOT NULL\r\n\r\n        BEGIN\r\n            INSERT INTO @Results\r\n            EXEC\r\n            (\r\n                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000) \r\n                FROM ' + @TableName + ' (NOLOCK) ' +\r\n                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2\r\n            )\r\n        END\r\n    END    \r\nEND\r\n\r\nSELECT ColumnName, ColumnValue FROM @Results\r\n<\/pre>\n<p>If we run above script for word &#8216;king&#8217; then output in below format will be displayed<!--more--><\/p>\n<figure id=\"attachment_5829\" aria-describedby=\"caption-attachment-5829\" style=\"width: 377px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value-Output.png?ssl=1\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-5829\" src=\"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value-Output.png?resize=377%2C293&#038;ssl=1\" alt=\"SQL Server search database for value - Output\" width=\"377\" height=\"293\" srcset=\"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value-Output.png?w=377&amp;ssl=1 377w, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value-Output.png?resize=300%2C233&amp;ssl=1 300w\" sizes=\"auto, (max-width: 377px) 100vw, 377px\" \/><\/a><figcaption id=\"caption-attachment-5829\" class=\"wp-caption-text\">SQL Server search database for value &#8211; Output<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Search complete Database in SQL Server for some value<\/p>\n","protected":false},"author":1,"featured_media":5830,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14],"tags":[292],"class_list":["post-5828","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqlserver","tag-sql-server"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-search-database-for-value.png?fit=1108%2C429&ssl=1","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":656,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/difference-in-var-char-and-nvar-char-in-sql-server\/","url_meta":{"origin":5828,"position":0},"title":"Difference in &#8220;varchar&#8221; and &#8220;nvarchar&#8221; in SQL Server","author":"Jitendra","date":"July 15, 2010","format":false,"excerpt":"What is the difference between varchar and nvarchar in MS SQL Server.","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":665,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/execute-dynamic-query-in-sql-server\/","url_meta":{"origin":5828,"position":1},"title":"Execute dynamic Query in SQL Server","author":"Jitendra","date":"July 16, 2010","format":false,"excerpt":"How to execute the dynamic query in microsoft SQL Server 2005.","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":622,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/get-foreign-key-related-details-for-the-table\/","url_meta":{"origin":5828,"position":2},"title":"Get Foreign Key related details for the Table","author":"Jitendra","date":"July 8, 2010","format":false,"excerpt":"Get Foreign Key related details for the Table in SQL Server","rel":"","context":"In &quot;SQL&quot;","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5882,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","url_meta":{"origin":5828,"position":3},"title":"SQL Server &#8211; Read all files in directory and store in Table","author":"Jitendra","date":"January 18, 2017","format":false,"excerpt":"T-SQL Script to read all files in directory and store back in table - SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1125,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/get-table-information-column-name-data-type-in-sql-server\/","url_meta":{"origin":5828,"position":4},"title":"Get Table information (Column Name, Data Type) in SQL Server","author":"Jitendra","date":"September 18, 2010","format":false,"excerpt":"Get Table information like Column Name, Data Type, Character length, Default Values etc in SQL Server","rel":"","context":"In &quot;SQL Server&quot;","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server INFORMATION_SCHEMA.COLUMNS","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/09\/SQL-Server-INFORMATION_SCHEMA.COLUMNS-300x44.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":5835,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/considerations-and-best-practices-for-salesforce-dataloading\/","url_meta":{"origin":5828,"position":5},"title":"Considerations and Best practices for Salesforce Dataloading","author":"Jitendra","date":"January 29, 2018","format":false,"excerpt":"Although we are doing data loading in Salesforce from ages, its very common to miss some steps or considerations. Therefore thought to list some of best practices and considerations I follow before or during data load process. Developer note field - I mostly create a text field on every object\u2026","rel":"","context":"In &quot;Salesforce&quot;","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"Salesforce Dataloader","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/01\/Salesforce-Dataloader.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/01\/Salesforce-Dataloader.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/01\/Salesforce-Dataloader.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/01\/Salesforce-Dataloader.png?resize=700%2C400&ssl=1 2x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5828","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=5828"}],"version-history":[{"count":2,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5828\/revisions"}],"predecessor-version":[{"id":5832,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5828\/revisions\/5832"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media\/5830"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=5828"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=5828"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=5828"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}