Transact SQL – Find Text – dbo.sp_findText

Background

Trying to track some data in our DB.  And, so looked for sample code on the .Net

Found out that Symantec has sample codes.

Let us use that and see how it develops.

Our customization is by no means completed, but still wanted to post it and flesh it out as time goes.

 

Code

Sample Codes

  1. Symantec – Find all tables that contain a specific GUID
    Link
  2. How to search a Microsoft SQL database for a data value
    Link
  3. How to find text within database tables
    Link

 

Stored Procedure – sp_findText



use [master]
go

if object_id('dbo.sp_findText') is null
begin

	exec('create procedure dbo.sp_findText as ')

end
go


alter procedure [dbo].[sp_findText]
(
	  @textSought							  varchar(120)
	, @searchDataTypeGUID					  bit = 0
	, @searchDataTypeCHAR					  bit = 0
	, @scriptOnly							  bit = 0
	, @skipObjectsMissingIndexWithRowCountEGT bigint = 0
	, @optimizeOptionRecompile				  bit = 1
)
as
begin

	/*
		Symantec - Find all tables that contain a specific GUID
		https://support.symantec.com/en_US/article.HOWTO1191.html
		Last Revision: 3 August 2012
	*/

	set nocount on;
	set transaction isolation level read uncommitted


	/* Declare variables */
	declare @strSql			 nvarchar(max)
	declare @strLog			 varchar(600)
	declare	@lowrow			 smallint
	declare	@SrchTextTypes	 bit

	declare @skippedObjectsCount bigint
	declare @iNumberofObjectsSkipped int

	declare @DATATYPE_GUID_LENGTH tinyint
	declare @DATATYPE_GUID_LENGTH_AS_STRING varchar(30)

	declare @textSought_Length tinyint
	declare @textSought_Length_AsString  varchar(10)

	declare @CHAR_CRLF		 varchar(30)
	declare @CHAR_QUOTE_SINGLE	varchar(30)
	declare @CHAR_QUOTE_DOUBLE  varchar(30)

	declare @QUERY_OPTION_RECOMPILE varchar(60)

	/* Table variable to hold all the list of Qualifying Tables */
	declare @TablesToBeSearched table
	(
		  RowId				smallint identity(1,1)
		, SchemaName		varchar(255)
		, TableName			varchar(255)
		, ColName			varchar(128)
		, IsGuidType		bit
		, SQLQuery			nvarchar(255)
		, fullTableName as QuoteName(SchemaName) + '.' + QuoteName(TableName)
		, [rowcount]		bigint null default 0
		, [correspondingIndexExist] bit not null default (0)
	) 

	declare @tblSQL TABLE
	(
		  RowId		int not null identity(1,1) 
		, [sqlText] varchar(8000) not null
	)


	declare @IndexColumnsKey TABLE
	(
		  [schemaname]	sysname			not null
		, objectID		int				not null
		, objectName	sysname			not null
		, objectType	varchar(10)		not null
		, index_id		int				null
		, indexName		sysname			null
		, [columnNames]	varchar(600)	null
		, [Column_1]	sysname			null		
	)

	declare @tableRowCount TABLE
	(
		  objectID		int not null
		, [schemaName]	sysname not null
		, objectName	sysname not null
		, [rowcount]	bigint not null
	)

	declare @FORMAT_ERROR_INVALID_GUID varchar(600)


	set @FORMAT_ERROR_INVALID_GUID
		 = 'Passed in String (%s) has a length of %s. To check against GUID Types it must be %s characters'


	/* 
	   Search Character Type Columns: There are some columns in the database that hold GUIDs, 
	   but the column is not a uniqueidentifier type (they have the GUID as a character string
	   instead).  If you want to search character column types (char, nchar, varchar, and 
	   nvarchar) then you must set the @SrchCharTypes value to 1.  
      
	*/
	set @CHAR_CRLF = char(13) + char(10)
	set @CHAR_QUOTE_SINGLE = ''''
	set @CHAR_QUOTE_DOUBLE = ''''''

	set @DATATYPE_GUID_LENGTH = 36
	set @DATATYPE_GUID_LENGTH_AS_STRING = '36'

	set @QUERY_OPTION_RECOMPILE = ' option (recompile ) '

	/* Search text and ntext type columns: These columns are designed to hold lots that may inlcude the guid as part of it.
	   Warrning: Searching text and ntext fields will significatly slow down the search process and put extra load on SQL.
	   To search text and ntext fields for the guid then set @SrchTextTypes to 1 */
	set @SrchTextTypes = 0

	set @textSought_Length = len(@textSought);
	set @textSought_Length_AsString = cast( @textSought_Length as varchar(10))


	/* 
		Qualify passed in check, if we will be validating against GUID Columns 
	*/
	if (@searchDataTypeGUID = 1)
	begin

		/*
			If Length is not 36
		*/
		if (@textSought_Length != @DATATYPE_GUID_LENGTH)
		begin

			exec master.dbo.xp_sprintf
					  @strLog output
					, @FORMAT_ERROR_INVALID_GUID
					, @textSought
					, @textSought_Length_AsString
					, @DATATYPE_GUID_LENGTH_AS_STRING

			raiserror(@strLog, 16,1)

			return

		end

	end
	/*
		Index Column Keys
	*/
	; with cteIndexColumnsKey
	(
		  [schema_name]	
		, object_id
		, object_name
		, object_type
		, index_id
		, index_name
		, [columnNames]
		, [Column_1]
	)
	as
	(

		select
				  schemaName = object_schema_name(tblSI.[object_id])
				, tblSI.object_id
				, object_name(tblSI.object_id)
				, tblSO.[type]
				, tblSI.index_id
				, tblSI.name
				, [Columns]
					= Stuff
					(
						(
							Select 
									', ' 
									+ tblSC.[name]
									+ case tblSIC.is_descending_key
											when 1 then '-'
											else ''
									  end

							from  sys.index_columns  tblSIC

							inner join sys.columns  tblSC

							on   tblSIC.object_id = tblSC.object_id
							and  tblSIC.column_id = tblSC.column_id

							where tblSI.object_id = tblSIC.object_id
							and   tblSI.index_id = tblSIC.index_id

							and   tblSIC.is_included_column = 0

							order by 
									tblSIC.key_ordinal

					        For Xml Path('')
						)
						, 1
						, 2
						, ''
					) 

				, [Column_1]
					= (
							Select 
									  tblSC.[name]
	
							from  sys.index_columns  tblSIC

							inner join sys.columns  tblSC

							on   tblSIC.object_id = tblSC.object_id
							and  tblSIC.column_id = tblSC.column_id

							where tblSI.object_id = tblSIC.object_id
							and   tblSI.index_id = tblSIC.index_id

							and   tblSIC.is_included_column = 0

							and   tblSIC.key_ordinal = 1


					) 

		from   sys.indexes tblSI

		inner join sys.objects tblSO

				on tblSI.object_id = tblSO.object_id

		where tblSI.index_id != 0

		and   tblSO.[type] in ( 'U')

	)

	insert into @IndexColumnsKey
	(
			  [schemaName]
			, objectID		
			, objectName
			, objectType	
			, index_id		
			, indexName	
			, [columnNames]	
			, [Column_1]	
	)
	select 
			  [schema_name]
			, object_id		
			, object_name	
			, object_type	
			, index_id		
			, index_name	
			, [columnNames]	
			, [Column_1]	
	from cteIndexColumnsKey cteICK

		
	/*
		Get Row Count
	*/
	; with [cteRowCount]
    (
          [objectID]
		, [schemaName]
		, [objectName]
        , [rowcount]
    )
    as
    (
 
        select
                    tblSP.object_ID
				  , object_schema_name(tblSP.object_id)	
				  , object_name(tblSP.object_id)	
                  , [rows] = sum(tblSP.rows)
 
        from    sys.partitions tblSP

		inner join sys.objects tblO

				on tblSP.object_id = tblO.object_id
 
        /*
           Only consider Heaps (0) or the Clustered Index (1)
           as DMV contains row for each index defined on the table
        */
        where   tblSP.index_id in (0, 1)

		and     tblO.[type] = 'U'
 
        group by tblSP.object_ID
 
    )
	insert into @tableRowCount
	(
			  objectID		
			, [schemaName]
			, objectName
			, [rowcount]
	)
	select 
			  cteRC.objectID
			, [schemaName]
			, [objectName]
			, cteRC.[rowcount]
	from   [cteRowCount] cteRC

	/* Get meta data for columns and tables that are uniqueidentifiers */
	if (@searchDataTypeGUID = 1)
	begin

		insert into @TablesToBeSearched
		(
			  SchemaName
			, TableName
			, ColName
			, IsGuidType
			, SQLQuery
		)
		select 
			[TABLE_SCHEMA],
			[TABLE_NAME],
			[COLUMN_NAME],
			[IsGuidType],
			SQLQuery
				= ' from [' 
					 + [TABLE_SCHEMA] + ']'
					 + '.[' + [TABLE_NAME] + '] '
					 + ' with (nolock) '
					 + ' where [' + [COLUMN_NAME] + '] = '''
					 + @textSought + ''''
					 + ' '
		from
		(

			select 
				  ist.[TABLE_SCHEMA]
				, isc.[TABLE_NAME]
				, isc.[COLUMN_NAME]
				, 1 as [IsGuidType]

			from INFORMATION_SCHEMA.COLUMNS isc
				inner join INFORMATION_SCHEMA.TABLES ist
					on isc.[TABLE_NAME] = ist.[TABLE_NAME]
					and isc.TABLE_SCHEMA = ist.TABLE_SCHEMA
			where isc.[DATA_TYPE] = 'uniqueidentifier'
			and   ist.[TABLE_TYPE] = 'BASE TABLE'


		) t1

	end

	/* If @SrchCharTypes is 1 then get meta data for character type columns as well. */
	if (@searchDataTypeCHAR = 1)
	begin
		insert into @TablesToBeSearched
		(
			  SchemaName
			, TableName
			, ColName
			, IsGuidType
			, SQLQuery
		)
		select 
			[TABLE_SCHEMA],
			[TABLE_NAME],
			[COLUMN_NAME],
			[IsGuidType],
				 + ' from [' 
				 + [TABLE_SCHEMA] + ']'
				 + '.[' + [TABLE_NAME] + '] '
				 + ' with (nolock) '
				 + ' where ([' + [COLUMN_NAME] + ']) like ''' 
				 + @textSought + '%'' '  
		from 
		(
			select 
				isc.[TABLE_SCHEMA],
				isc.[TABLE_NAME],
				isc.[COLUMN_NAME],
				0 [IsGuidType]

			from INFORMATION_SCHEMA.COLUMNS isc 
				join INFORMATION_SCHEMA.TABLES ist 
					on isc.[TABLE_NAME] = ist.[TABLE_NAME]
					and isc.TABLE_SCHEMA = ist.TABLE_SCHEMA
			where isc.[DATA_TYPE] in
			(
				'char',
				'nchar',
				'varchar',
				'nvarchar'
			)
			and ist.[TABLE_TYPE] = 'BASE TABLE'
		) t1

	end

	/*
		If @SrchTextTypes is 1 then get meta data for text and ntext type columns.
	*/
	if @SrchTextTypes  = 1
	begin

		insert into @TablesToBeSearched
		(
			  SchemaName
			, TableName
			, ColName
			, IsGuidType
			, SQLQuery
		)
		select 
			[TABLE_SCHEMA],
			[TABLE_NAME],
			[COLUMN_NAME],
			[IsGuidType],
			+ ' from [' 
			+ [TABLE_SCHEMA] + ']'
			+ '.[' + [TABLE_NAME] + '] '
			+ ' with (nolock) '
			+ ' where lower(cast([' + [COLUMN_NAME] + '] as nvarchar(max))) like ''%' + @textSought + '%''' 
		from
		(
			select 
				ist.TABLE_SCHEMA,
				isc.[TABLE_NAME],
				isc.[COLUMN_NAME],
				null [IsGuidType]
			from INFORMATION_SCHEMA.COLUMNS isc 
				join INFORMATION_SCHEMA.TABLES ist 
					on isc.[TABLE_NAME] = ist.[TABLE_NAME]
					and isc.TABLE_SCHEMA = ist.TABLE_SCHEMA
			where isc.[DATA_TYPE] in ('text', 'ntext')
				and ist.[TABLE_TYPE] = 'BASE TABLE'
		) t1

	end

	
	update tblTS

	set   [rowcount] = tblRC.[rowcount]

	from  @TablesToBeSearched tblTS

	inner join @tableRowCount tblRC
			on  tblTS.SchemaName = tblRC.SchemaName
			and tblTS.TableName = tblRC.ObjectName


	update tblTS

	set   [correspondingIndexExist] = 1

	from  @TablesToBeSearched tblTS

	inner join @IndexColumnsKey tblICK
			on  tblTS.SchemaName = tblICK.SchemaName
			and tblTS.TableName = tblICK.ObjectName
			and tblTS.ColName    = tblICK.[Column_1]


	/*
		Drop Temp table being used if it exists 
	*/
	if (object_id('tempdb..##TextFound') is not null)
	begin
		drop table ##TextFound
	end


	/*
		Drop Temp table being used if it exists 
	*/
	if (object_id('tempdb..##TextFoundSkippedObjects') is not null)
	begin
		drop table ##TextFoundSkippedObjects
	end
	/*
		Temp table that will hold # of rows where data was found in each table
	*/
	create table ##TextFound 
	(
		  RowId smallint
		, SchemaName	sysname
		, [TableName]	sysname
		, [ColName]		sysname
		, [query]		varchar(4000)
		, RowsFound		int
	)

	create table ##TextFoundSkippedObjects
	(
		  RowId			smallint not null identity(1,1)
		, SchemaName	sysname
		, [TableName]	sysname
		, [ColName]		sysname
		, IsGuidType	bit
		, [query]		varchar(4000)
		, [rowcount]	bigint
	)

	if (
			    ( @skipObjectsMissingIndexWithRowCountEGT is not null )
			and ( @skipObjectsMissingIndexWithRowCountEGT != 0 )
	   )	
	begin

		insert into ##TextFoundSkippedObjects
		(
			  SchemaName
			, [TableName]	
			, [ColName]	
			, IsGuidType			
			, [query]		
			, [rowcount]	
		)
		select 
			  SchemaName
			, TableName	
			, ColName	
			, IsGuidType	
			, SQLQuery
			, [rowcount]

		from  @TablesToBeSearched tTBS

		where (
						(tTBS.[correspondingIndexExist] = 0 )
					and (
							( [rowcount] >= @skipObjectsMissingIndexWithRowCountEGT)
						)
			  )


		delete tTBS
		from  @TablesToBeSearched tTBS

		where (
						(tTBS.[correspondingIndexExist] = 0 )
					and (
							( [rowcount] >= @skipObjectsMissingIndexWithRowCountEGT)
						)
			  )

		set @iNumberofObjectsSkipped = @@ROWCOUNT

	end

	set @lowrow = 0

	while (
				@lowrow < 
							(
								select max(RowId) 
								from   @TablesToBeSearched 
							)
		 )

	begin

		set @strSql = null

		select @strSql = 
							coalesce(
										@strSql 
										  +	@CHAR_CRLF
										  + ' union all select '
										, 'insert into ##TextFound '
										    + @CHAR_CRLF
											+ '([RowId], [SchemaName], [TableName], [ColName],  [query], RowsFound ) '
										    + ' select ') + '''' 
											+ cast(ttbs.[RowId] as varchar(10)) + ''' [id] ' -- RowID
											+  ', ' + @CHAR_QUOTE_SINGLE + ttbs.[SchemaName] + @CHAR_QUOTE_SINGLE 
											+  ', ' + @CHAR_QUOTE_SINGLE + ttbs.[TableName] + @CHAR_QUOTE_SINGLE 
											+  ', ' + @CHAR_QUOTE_SINGLE + ttbs.[ColName] + @CHAR_QUOTE_SINGLE 
											+  ', ' + @CHAR_QUOTE_SINGLE 
													+ replace(ttbs.[SQLQuery], @CHAR_QUOTE_SINGLE, @CHAR_QUOTE_DOUBLE)
													+ @CHAR_QUOTE_SINGLE 
											+ ', count(*) [cnt] ' 
											+ ttbs.SQLQuery

							from @TablesToBeSearched ttbs

							where ttbs.[RowId] between @lowrow + 1 and @lowrow + 15

	  /*
		If Option Recompile is requested, then add it
	  */	
	  if ( @optimizeOptionRecompile = 1)
	  begin

		  set @strSql
				= @strSql	
					 + case ( @optimizeOptionRecompile)
							when 1 then @QUERY_OPTION_RECOMPILE
							else ' '
					   end	

	end

		print '@strSql: ' + @strSql

		insert into @tblSQL
		(
			 [sqlText]
		)
		values
		(
			@strSql
		)

		if (@scriptOnly = 0)
		begin

			exec(@strSql)

		end
    
		set @lowrow = @lowrow + 15
	end


	/*
		Select tables, columns, and rows found where rowsfound > 0 
	*/
	select distinct 
			   = '##TextFound'
			, ttbs.SchemaName
			, ttbs.[TableName]
			, ttbs.[ColName]
			, f.[RowsFound]
			, [query] 
				= 'select * ' + ttbs.SQLQuery

	from ##TextFound f

	join @TablesToBeSearched ttbs 
		on f.[RowId] = ttbs.[RowId]

	where RowsFound > 0 


	select  = '@TablesToBeSearched', *
	from   @TablesToBeSearched


	select  = '@tblSQL', *
	from   @tblSQL

	select  = '@IndexColumnsKey', *
	from   @IndexColumnsKey

	set @skippedObjectsCount = ( select count(*) from ##TextFoundSkippedObjects )

	if (
			( @skippedObjectsCount > 0)
       )
	begin

		select  = '##TextFoundSkippedObjects', *
		from   ##TextFoundSkippedObjects

		set @strLog =
				cast(@iNumberofObjectsSkipped as varchar(10)) + ' objects skipped'

		print @strLog

	end


end
go


exec sys.sp_MS_marksystemobject '[dbo].[sp_findText]'
go

 

Output

foundData

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s