{"id":664,"date":"2024-01-05T20:12:00","date_gmt":"2024-01-05T20:12:00","guid":{"rendered":"https:\/\/eipsoftware.com\/musings\/?p=664"},"modified":"2024-04-14T20:19:45","modified_gmt":"2024-04-14T20:19:45","slug":"search-value-in-multiple-columns","status":"publish","type":"post","link":"https:\/\/eipsoftware.com\/musings\/search-value-in-multiple-columns\/","title":{"rendered":"Search Value in Multiple Columns"},"content":{"rendered":"\n<p class=\"has-large-font-size wp-block-paragraph\"><em>Where is it?  WHERE IS IT?  WHERE????<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes you need to search for a value in the table, but you have no idea where it is.  The query will use a cursor allowing you to search through multiple tables and multiple columns  to find the value.<\/p>\n\n\n\n<!--more-->\n\n\n\n<div class=\"wp-block-urvanov-syntax-highlighter-code-block\"><pre class=\"lang:tsql decode:true \" >USE\t\t\tYOUR_DATABASE;\n\nDECLARE\t\t@search_columns\t\tAS NVARCHAR(512);\nDECLARE\t\t@search_value\t\tAS NVARCHAR(512);\nDECLARE\t\t@search_datatype\tAS NVARCHAR(32); \n\nSET\t\t\t@search_columns\t\t= 'persid'\t-- if you know the column name\nSET\t\t\t@search_value\t\t= CHAR(39) + 'CO%' + CHAR(39) -- value to search for\nSET\t\t\t@search_datatype\t= 'VARCHAR' -- search only specified column datatype\n\nBEGIN TRY\n\t IF OBJECT_ID('tempdb..#column_list') IS NOT NULL\n\t\tBEGIN\n\t\t\tTRUNCATE TABLE #column_list;\n\t\tEND\n\tELSE\n\t\tBEGIN\n\t\t\tCREATE TABLE #column_list\n\t\t\t(\n\t\t\t\ttable_name\t\t\t\tNVARCHAR(512)\tNOT NULL\n\t\t\t\t, ordinal_position\t\tINT\t\t\t\tNOT NULL\n\t\t\t\t, column_name\t\t\tNVARCHAR(512)\tNOT NULL\n\t\t\t\t, data_type\t\t\t\tNVARCHAR(512)\tNOT NULL\n\t\t\t\t, max_length\t\t\tINT\t\t\t\t\tNULL\n\t\t\t\t, column_default\t\tNVARCHAR(512)\t\tNULL\n\t\t\t\t, is_nullable\t\t\tNVARCHAR(8)\t\t\tNULL\n\t\t\t\t, values_found\t\t\tINT\t\t\t\t\tNULL\n\t\t\t)\n\t\tEND\n\n\t\t-- find the columns that will be searched\n\t\tINSERT INTO\t\t#column_list\n\t\t\t\t\t\t(table_name\t,ordinal_position ,column_name \n\t\t\t\t\t\t,data_type\t,max_length\t,column_default ,is_nullable)\n\n\t\tSELECT\t\t\tTOP 250\n\t\t\t\t\t\tinfocolumns.table_name\n\t\t\t\t\t\t, infocolumns.ordinal_position\n\t\t\t\t\t\t, infocolumns.column_name\n\t\t\t\t\t\t, infocolumns.data_type\n\t\t\t\t\t\t, infocolumns.character_maximum_length\n\t\t\t\t\t\t, infocolumns.column_default\n\t\t\t\t\t\t, infocolumns.is_nullable\n\n\t\tFROM\t\t\tINFORMATION_SCHEMA.COLUMNS AS infocolumns\n\t\t\t\t\t\t-- exclude viewa\n\t\t\t\t\t\tLEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS AS infoviews\n\t\t\t\t\t\t\tON\tinfocolumns.TABLE_SCHEMA = infoviews.TABLE_SCHEMA\n\t\t\t\t\t\t\tAND infocolumns.TABLE_NAME = infoviews.TABLE_NAME\n\n\t\tWHERE\t\t\tinfoviews.TABLE_NAME IS NULL -- exclude views\n\t\t\t\t\t\tAND infocolumns.column_name LIKE @search_columns\n\t\t\t\t\t\tAND infocolumns.data_type = @search_datatype\n\t\t\t\t\t\tAND infocolumns.TABLE_NAME NOT IN\n\t\t\t\t\t\t\t('si_ci_rec_rel', 'si_rec_prop'\n\t\t\t\t\t\t\t,'si_log_prop'\n\t\t\t\t\t\t\t,'si_wf_prop'\n\t\t\t\t\t\t\t,'attmnt_06272016'\n\t\t\t\t\t\t\t,'attmnt_delete'\n\t\t\t\t\t\t\t,'attmnt_OLD'\n\t\t\t\t\t\t\t)\n\t\t;\n\n\t\t-- -------------------------------------------------------------------\n\t\t-- set the cursor\n\t\t-- -------------------------------------------------------------------\n\n\t\tDECLARE\t\t@main_cursor\t\tAS CURSOR;\n\t\tDECLARE\t\t@table_name\t\t\tAS NVARCHAR(512);\n\t\tDECLARE\t\t@column_name\t\tAS NVARCHAR(512);\n\t\tDECLARE\t\t@sql_command\t\tAS NVARCHAR(MAX);\n\t\tDECLARE\t\t@result_rows\t\tAS INT;\n\n\t\tSET\t\t\t@main_cursor = CURSOR FOR \n\t\tSELECT\t\tDISTINCT \n\t\t\t\t\tc_li.table_name\n\t\t\t\t\t,c_li.column_name\n\t\tFROM\t\t#column_list AS c_li\n\t\tORDER BY\t1,2\n\t\t;\n\t\t-- -------------------------------------------------------------------\n\t\t-- Loop through the table and columns names and store results in the temp table\n\t\t-- -------------------------------------------------------------------\n\n\t\tOPEN\t\t@main_cursor;\n\n\t\tFETCH\t\tNEXT FROM @main_cursor INTO\n\t\t\t\t\t@table_name\n\t\t\t\t\t,@column_name\n\t\t\t\t\t;\n\n\t\tWHILE\t\t@@FETCH_STATUS = 0\n\t\t\t\t\tBEGIN\t-- begin while loop\n\t\t\t\t\t\tPRINT 'SEARCHING: ' + @table_name + '.' + @column_name\n\t\t\t\t\t\n\t\t\t\t\t\tSET @sql_command = N' SELECT\t@result_rows = COUNT(*)' +\n\t\t\t\t\t\t\t\t\t\t\t' FROM  CAITMDBPRP01.mdb_rpt.dbo.' + @table_name + \n\t\t\t\t\t\t\t\t\t\t\t' WHERE ' + @column_name + ' LIKE ' + @search_value +';'\n\t\t\t\t\t\t\n\t\t\t\t\t\tPRINT @sql_command\n\t\t\t\t\t\tEXECUTE sp_executesql @sql_command, N'@result_rows INT OUTPUT' ,@result_rows OUTPUT\n\t\t\t\t\t\n\t\t\t\t\t\t-- UPDATE Temp Table\n\t\t\t\t\t\tUPDATE\t#column_list\n\t\t\t\t\t\t\t\tSET values_found = @result_rows\n\t\t\t\t\t\tWHERE\ttable_name\t\t= @table_name\n\t\t\t\t\t\t\t\tAND column_name = @column_name\n\t\t\t\t\t\t;\n\t\t\t\t\t\t\n\t\t\t\t\t\t-- get the next row\n\t\t\t\t\t\tFETCH\t\tNEXT FROM @main_cursor INTO\n\t\t\t\t\t\t\t\t\t@table_name\n\t\t\t\t\t\t\t\t\t,@column_name\n\t\t\t\t\t\t\t\t\t;\n\t\t\t\t\t\n\t\t\t\t\tEND\t\t-- end while loop\n\nEND TRY\n\n\n-- -----------------------------------------------------------------------\n-- standard error handler\n-- -----------------------------------------------------------------------\nBEGIN CATCH\n\t--ROLLBACK TRANSACTION @current_transaction;\n\t-- Execute the error retrieval routine.\n\t\tSELECT \n\t\tERROR_NUMBER() AS ErrorNumber,\n\t\tERROR_SEVERITY() AS ErrorSeverity,\n\t\tERROR_STATE() as ErrorState,\n\t\tERROR_PROCEDURE() as ErrorProcedure,\n\t\tERROR_LINE() as ErrorLine,\n\t\tERROR_MESSAGE() as ErrorMessage;\nEND CATCH;\n\nSELECT\t\t*\nFROM\t\t#column_list\nORDER BY\t1,2\n;<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Where is it? WHERE IS IT? WHERE???? Sometimes you need to search for a value in the table, but you have no idea where it is. The query will use a cursor allowing you to search through multiple tables and multiple columns to find the value.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[43,4,6],"tags":[14,15],"series":[],"class_list":["post-664","post","type-post","status-publish","format-standard","hentry","category-sql-t-sql","category-code","category-sql","tag-sql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/664","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=664"}],"version-history":[{"count":2,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/664\/revisions"}],"predecessor-version":[{"id":666,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/664\/revisions\/666"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=664"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=664"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=664"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=664"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}