{"id":428,"date":"2019-05-31T08:55:13","date_gmt":"2019-05-31T12:55:13","guid":{"rendered":"http:\/\/techref.camellarry.com\/?p=428"},"modified":"2019-05-31T08:55:13","modified_gmt":"2019-05-31T12:55:13","slug":"sql-search-all-tables-for-text","status":"publish","type":"post","link":"http:\/\/techref.camellarry.com\/?p=428","title":{"rendered":"SQL Search all tables for text"},"content":{"rendered":"

I was looking for a string inside of a table in a massive database.\u00a0 I was attempting to do this by trial-and-error until I ran across this method which does the searching for me.\u00a0 This script searches all the text\/string type fields in every table in the selected database.\u00a0 Simply specify the “StringToFind”.<\/p>\n

Declare @StringToFind VARCHAR(200), @Schema sysname, @Table sysname, @FullTable int, @NewMinID int, @NewMaxID int,
\n@SQLCommand VARCHAR(8000), @BaseSQLCommand varchar(8000), @Where VARCHAR(8000), @CountCheck varchar(8000) , @FieldTypes varchar(8000),
\n@cursor VARCHAR(8000), @columnName sysname, @SCn int, @SCm int
\nDeclare @TableList table (Id int identity(1,1) not null, tablename varchar(250))
\nDeclare @SQLCmds table (id int identity(1,1) not null, sqlcmd varchar(8000))
\nDeclare @DataFoundInTables table (id int identity(1,1) not null, sqlcmd varchar(8000))<\/p>\n

— Settings<\/p>\n

SET @StringToFind = ‘.NET Framework 4.7.2’
\nSET NOCOUNT ON
\nSET @StringToFind = ‘%’+@StringToFind+’%’<\/p>\n

— Gathering Info<\/p>\n

if ((select count(*) from sysobjects where name = ‘tempcount’) > 0)
\ndrop table tempcount<\/p>\n

create table tempcount (rowsfound int)
\ninsert into tempcount select 0<\/p>\n

— This section here is to accomodate the user defined datatypes, if they have
\n— a SQL Collation then they are assumed to have text in them.
\nSET @FieldTypes = ”
\nselect @FieldTypes = @FieldTypes + ”” + rtrim(ltrim(name))+”’,’ from systypes where collation is not null or xtype = 36
\nselect @FieldTypes = left(@FieldTypes,(len(@FieldTypes)-1))<\/p>\n

insert into @TableList (tablename)
\nselect name from sysobjects
\nwhere xtype = ‘U’ and name not like ‘dtproperties’
\norder by name<\/p>\n

— Start Processing Table List<\/p>\n

select @NewMinID = min(id), @NewMaxID = max(id) from @TableList<\/p>\n

while(@NewMinID <= @NewMaxID)
\nBegin<\/p>\n

SELECT @Table = tablename, @Schema=’dbo’, @Where = ” from @TableList where id = @NewMinID<\/p>\n

SET @SQLCommand = ‘SELECT * FROM ‘ + @Table + ‘ WHERE’
\n— removed ‘ + @Schema + ‘.<\/p>\n

SET @cursor = ‘DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME
\nFROM [‘ + DB_NAME() + ‘].INFORMATION_SCHEMA.COLUMNS
\nWHERE TABLE_SCHEMA = ”’ + @Schema + ”’
\nAND TABLE_NAME = ”’ + @Table + ”’
\nAND DATA_TYPE IN (‘+@FieldTypes+’)’
\n–Original Check, however the above implements user defined data types –AND DATA_TYPE IN (”char”,”nchar”,”ntext”,”nvarchar”,”text”,”varchar”)’<\/p>\n

EXEC (@cursor)<\/p>\n

SET @FullTable = 0
\nDELETE FROM @SQLCmds<\/p>\n

OPEN col_cursor
\nFETCH NEXT FROM col_cursor INTO @columnName<\/p>\n

WHILE @@FETCH_STATUS = 0
\nBEGIN<\/p>\n

SET @Where = @Where + ‘ [‘ + @columnName + ‘] LIKE ”’ + @StringToFind + ””
\nSET @Where = @Where + ‘ OR’<\/p>\n

–PRINT @Table + ‘|’+ cast(len(isnull(@Where,”))+len(isnull(@SQLCommand,”)) as varchar(10))+’|’+@Where<\/p>\n

if (len(isnull(@Where,”))+len(isnull(@SQLCommand,”)) > 3600)
\nBegin
\nSELECT @Where = substring(@Where,1,len(@Where)-3)
\ninsert into @SQLCmds (sqlcmd) select @Where
\nSET @Where = ”
\nEnd<\/p>\n

FETCH NEXT FROM col_cursor INTO @columnName
\nEND<\/p>\n

CLOSE col_cursor
\nDEALLOCATE col_cursor<\/p>\n

if (@Where <> ”)
\nBegin
\nSELECT @Where = substring(@Where,1,len(@Where)-3)
\ninsert into @SQLCmds (sqlcmd)
\nselect @Where –select @Table,count(*) from @SQLCmds
\nEnd<\/p>\n

SET @BaseSQLCommand = @SQLCommand<\/p>\n

select @SCn = min(id), @SCm = max(id) from @SQLCmds
\nwhile(@SCn <= @SCm)
\nBegin<\/p>\n

select @Where = sqlcmd from @SQLCmds where ID = @SCn<\/p>\n

if (@Where <> ”)
\nBegin<\/p>\n

SET @SQLCommand = @BaseSQLCommand + @Where
\nSELECT @CountCheck = ‘update tempcount set rowsfound = (select count(*) ‘+ substring(@SQLCommand,10,len(@SQLCommand)) + ‘)’
\nEXEC (@CountCheck)<\/p>\n

if ((select rowsfound from tempcount) > 0)
\nBegin
\nPRINT ‘— [‘+cast(@NewMinID as varchar(15))+’\/’+cast(@NewMaxID as varchar(15))+’] ‘+@Table + ‘ ———————————-[FOUND!]’
\n–PRINT ‘— [FOUND USING:] ‘ +@SQLCommand
\ninsert into @DataFoundInTables (sqlcmd) select @SQLCommand
\nEXEC (@SQLCommand)
\nupdate tempcount set rowsfound = 0
\nEnd
\nelse
\nBegin
\nPRINT ‘— [‘+cast(@NewMinID as varchar(15))+’\/’+cast(@NewMaxID as varchar(15))+’] ‘+@Table
\nEnd
\nEnd<\/p>\n

SET @SCn = @SCn + 1
\nEnd<\/p>\n

set @NewMinID = @NewMinID + 1
\nend<\/p>\n

if ((select count(*) from sysobjects where name = ‘tempcount’) > 0)
\ndrop table tempcount<\/p>\n

\/*<\/p>\n

This will now return all the sql commands you need to use<\/p>\n

*\/<\/p>\n

select @NewMinID = min(id), @NewMaxID = max(id) from @DataFoundInTables<\/p>\n

if (@NewMaxID > 0)
\nBegin
\nPRINT ‘ ‘
\nPRINT ‘ ‘
\nPRINT ‘—————————————–‘
\nPRINT ‘———– TABLES WITH DATA ————‘
\nPRINT ‘—————————————–‘
\nPRINT ‘ ‘
\nPRINT ‘We found ‘ + cast(@NewMaxID as varchar(10)) + ‘ table(s) with the string ‘+@StringToFind
\nPRINT ‘ ‘<\/p>\n

while(@NewMinID <= @NewMaxID)
\nBegin<\/p>\n

select @SQLCommand = sqlcmd from @DataFoundInTables where ID = @NewMinID<\/p>\n

PRINT @SQLCommand<\/p>\n

SET @NewMinID = @NewMinID + 1
\nEnd<\/p>\n

PRINT ‘ ‘
\nPRINT ‘—————————————–‘<\/p>\n

End<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

I was looking for a string inside of a table in a massive database.\u00a0 I was attempting to do this by trial-and-error until I ran across this method which does the searching for me.\u00a0 This script searches all the text\/string type fields in every table in the selected database.\u00a0 Simply specify the “StringToFind”. Declare @StringToFind […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"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,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[8],"tags":[45,70],"class_list":["post-428","post","type-post","status-publish","format-standard","hentry","category-sql","tag-microsoft","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9t3oE-6U","_links":{"self":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts\/428","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=428"}],"version-history":[{"count":0,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=\/wp\/v2\/posts\/428\/revisions"}],"wp:attachment":[{"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=428"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=428"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/techref.camellarry.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}