Solved

Sql script to  search a database

Posted on 2014-11-24
4
119 Views
Last Modified: 2014-11-24
Does anyone have a sql script that can search an entire database for a text string? Lets say I want to search for all tables that have the name "Joseph" in any column of data?
Or maybe a good hyperlink to such sql code to do this?
0
Comment
Question by:brgdotnet
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 333 total points
Comment Utility
I wrote this procedure a while ago, just checked it again and tweaked it somewhat to make it more generic than my original purpose. It should get you what you need I think.
The script creates a stored procedure which takes 2 parameters, the datatype of the value you're looking for, and the value itself. So if you're looking for Joseph, assuming your database is unicode, your datatype would be nvarchar and your procedure call would be

Exec uSP_Check_data_used 'nvarchar', 'Joseph'

Open in new window


The output is a list of all the columns that contain this value. Note it doesn't search inside strings, it just looks for the value supplied "as is".

/****** Object:  StoredProcedure [dbo].[KVW_SP_Check_data_used]    Script Date: 11/25/2014 09:31:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[uSP_Check_data_used]

	@Data_type		nvarchar(100)	--The datatype of the column you're checking.
,	@Data_name		nvarchar(100)	--The value which you want to check

--For example, if you're checking if the name 'Jonathan' is used in the system, @Data_type could be 'nvarchar' for instance,
--and @data_name would be 'Jonathan'. The output is a list of all the tables and corresponding columns where the code is used.

AS

Set nocount on

--Declare additional variables
Declare		@SQL			nvarchar(max)
		,	@Schema_name	nvarchar(100)
		,	@Table_name		nvarchar(100)
		,	@column_name	nvarchar(100)
		,	@database_name	nvarchar(100)

--Declare table variable to hold database names
Declare	@V_databases	table
(Database_name	nvarchar(100))

--Insert ABS database names into table variable
Insert into @V_databases
Select	name
from	sys.databases
where	name not in ('master', 'model', 'msdb', 'tempdb')

--Create table to hold all possible table and column names for selected data_type and data_name
Create table	#Datacheck
(	Database_name	nvarchar(100)
,	Schema_nm		nvarchar(100)
,	Table_name		nvarchar(100)
,	column_name		nvarchar(100)
)

--Create table to hold all table and column names where the selected value exists.
Create table	#DataExists
(	Database_name	nvarchar(100)
,	Schema_nm		nvarchar(100)
,	Table_name		nvarchar(100)
,	column_name		nvarchar(100)
,	Value_checked	nvarchar(100)
)

--Insert all possible table and column names for selected data_type and data_name
--Declare cursor to check each database
	Declare	T_databases cursor for

	Select	Database_name
	from	@V_databases
	Order by Database_name
	
	Open	T_databases
	Fetch	Next from T_databases
	into
			@database_name
			
	While	(@@FETCH_STATUS <> -1)
	Begin
	
--Create dynamic SQL query to loop through each of the databases
	Set	@SQL = 
		'Insert into	#Datacheck

		Select	'	+ '''' + @database_name + '''	,
						s.name as ''Schema''		,
						o.name as ''Table''			,
						c.name as ''Column_name''
		from	'	+ '' + @Database_name + '.sys.types t
				inner join ' + '' + @Database_name + '.sys.columns c
					on t.system_type_id = c.system_type_id
				inner join ' + '' + @Database_name + '.sys.objects o
					on c.object_id = o.object_id
					and o.type <> ''V''
				inner join ' + '' + @database_name + '.sys.schemas s
					on o.schema_id = s.schema_id
		where	t.name = ' + '''' + @Data_type + '''' + 'and o.type = ''U'''

	Exec	(@SQL)
	
	Set		@SQL = ''

	Fetch	Next from T_databases
	into
			@database_name
			
	End
	
	Close	T_databases
	Deallocate T_databases

--Clear @database_name variable
Set @database_name = ''

--Create cursor which loops through all selected tables and checks the columns for the selected value
	Declare	T_Datacheck	cursor for

	Select		Database_name
			,	Schema_nm
			,	Table_name
			,	'[' + Column_name + ']'
	From	#Datacheck
	Order by	Table_name
	
	Open	T_Datacheck
	Fetch	next from T_Datacheck
	Into
				@Database_name
			,	@schema_name
			,	@Table_name
			,	@column_name
	
	While (@@FETCH_STATUS) <> -1
	Begin
	
	--Dynamic SQL statement to check each table
	Set		@SQL =	'IF Exists	(
								Select	1
								From	' + @database_name + '.' + @Schema_name + '.' + @table_name	+ ' 
								Where	' + @column_name + ' = ''' + @Data_name + ''''
								+ ' )
					Begin
					Insert	Into	#DataExists
					Select	'''			+ @database_name	+ ''''
							+ ', '''	+ @Schema_name		+ ''''
							+ ', '''	+ @Table_name		+ ''''
							+ ', '''	+ @column_name		+ ''''
							+ ', '''	+ @Data_name		+ ''''
					+ ' End'
			
	Print	@SQL
--Execute the dynamic SQL to insert possible values into the final dataset
	Exec	(@SQL)
	
--Clear the dynamic SQL variable for next loop
	Set @SQL = ''
	
	Fetch	next from T_Datacheck
	Into
				@Database_name
			,	@Schema_name
			,	@Table_name
			,	@column_name
	End
	
	Close	T_Datacheck
	Deallocate T_Datacheck

--Select final dataset
Select	*
from	#DataExists	
	
--Drop temp tables
Drop table #Datacheck
Drop table #DataExists
	
Set nocount off

Open in new window

0
 
LVL 9

Assisted Solution

by:teebon
teebon earned 167 total points
Comment Utility
I've tried the following stored procedure before, which is quite slow (depending on how big your database is):

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'Test'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

        EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

Open in new window


Source
0
 
LVL 12

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 333 total points
Comment Utility
Ok, didn't like the fact it was only possible to find an exact match, so I tweaked it a bit further. Below procedure allows you to select whether you want an exact match for the value, or if the search value is part of a longer value in the column (e.g. a first name as part of the full name).

/****** Object:  StoredProcedure [dbo].[uSP_Check_data_used]    Script Date: 11/25/2014 10:12:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[uSP_Check_data_used]

	@Data_type		nvarchar(100)	--The datatype of the column you're checking.
,	@Data_name		nvarchar(100)	--The value which you want to check
,	@Exact_match	smallint

--For example, if you're checking if the name 'Jonathan' is used in the system, @Data_type could be 'nvarchar' for instance,
--and @data_name would be 'Jonathan'. The output is a list of all the tables and corresponding columns where the code is used.

AS

Set nocount on

--Declare additional variables
Declare		@SQL			nvarchar(max)
		,	@Schema_name	nvarchar(100)
		,	@Table_name		nvarchar(100)
		,	@column_name	nvarchar(100)
		,	@database_name	nvarchar(100)
		


--Declare table variable to hold database names
Declare	@V_databases	table
(Database_name	nvarchar(100))

--Insert ABS database names into table variable
Insert into @V_databases
Select	name
from	sys.databases
where	name not in ('master', 'model', 'msdb', 'tempdb')

--Create table to hold all possible table and column names for selected data_type and data_name
Create table	#Datacheck
(	Database_name	nvarchar(100)
,	Schema_nm		nvarchar(100)
,	Table_name		nvarchar(100)
,	column_name		nvarchar(100)
)

--Create table to hold all table and column names where the selected value exists.
Create table	#DataExists
(	Database_name	nvarchar(100)
,	Schema_nm		nvarchar(100)
,	Table_name		nvarchar(100)
,	column_name		nvarchar(100)
,	Value_checked	nvarchar(100)
)

--Insert all possible table and column names for selected data_type and data_name
--Declare cursor to check each database
	Declare	T_databases cursor for

	Select	Database_name
	from	@V_databases
	Order by Database_name
	
	Open	T_databases
	Fetch	Next from T_databases
	into
			@database_name
			
	While	(@@FETCH_STATUS <> -1)
	Begin
	
--Create dynamic SQL query to loop through each of the databases
	Set	@SQL = 
		'Insert into	#Datacheck

		Select	'	+ '''' + @database_name + '''	,
						s.name as ''Schema''		,
						o.name as ''Table''			,
						c.name as ''Column_name''
		from	'	+ '' + @Database_name + '.sys.types t
				inner join ' + '' + @Database_name + '.sys.columns c
					on t.system_type_id = c.system_type_id
				inner join ' + '' + @Database_name + '.sys.objects o
					on c.object_id = o.object_id
					and o.type <> ''V''
				inner join ' + '' + @database_name + '.sys.schemas s
					on o.schema_id = s.schema_id
		where	t.name = ' + '''' + @Data_type + '''' + 'and o.type = ''U'''

	Exec	(@SQL)
	
	Set		@SQL = ''

	Fetch	Next from T_databases
	into
			@database_name
			
	End
	
	Close	T_databases
	Deallocate T_databases

--Clear @database_name variable
Set @database_name = ''

--Create cursor which loops through all selected tables and checks the columns for the selected value
	Declare	T_Datacheck	cursor for

	Select		Database_name
			,	Schema_nm
			,	Table_name
			,	'[' + Column_name + ']'
	From	#Datacheck
	Order by	Table_name
	
	Open	T_Datacheck
	Fetch	next from T_Datacheck
	Into
				@Database_name
			,	@schema_name
			,	@Table_name
			,	@column_name
	
	While (@@FETCH_STATUS) <> -1
	Begin
	
	--Dynamic SQL statement to check each table
	If @Exact_match = 0
	Begin
	Set		@SQL =	'IF Exists	(
								Select	1
								From	' + @database_name + '.' + @Schema_name + '.' + @table_name	
								+ ' Where	' + @column_name + ' like ''%' + @Data_name + '%'''
								+ ' )
					Begin
					Insert	Into	#DataExists
					Select	'''			+ @database_name	+ ''''
							+ ', '''	+ @Schema_name		+ ''''
							+ ', '''	+ @Table_name		+ ''''
							+ ', '''	+ @column_name		+ ''''
							+ ', '''	+ @Data_name		+ ''''
					+ ' End'
	End
	Else
	Begin
	Set		@SQL =	'IF Exists	(
								Select	1
								From	' + @database_name + '.' + @Schema_name + '.' + @table_name	
								+ ' Where	cast(' + @column_name + ' as nvarchar(max)) = Cast(''' + @Data_name + ''' as nvarchar(max))'
								+ ' )
					Begin
					Insert	Into	#DataExists
					Select	'''			+ @database_name	+ ''''
							+ ', '''	+ @Schema_name		+ ''''
							+ ', '''	+ @Table_name		+ ''''
							+ ', '''	+ @column_name		+ ''''
							+ ', '''	+ @Data_name		+ ''''
					+ ' End'
	End
			
--Execute the dynamic SQL to insert possible values into the final dataset

	Exec	(@SQL)
	
--Clear the dynamic SQL variable for next loop
	Set @SQL = ''
	
	Fetch	next from T_Datacheck
	Into
				@Database_name
			,	@Schema_name
			,	@Table_name
			,	@column_name
	End
	
	Close	T_Datacheck
	Deallocate T_Datacheck

--Select final dataset
Select	*
from	#DataExists	
	
--Drop temp tables
Drop table #Datacheck
Drop table #DataExists
	
Set nocount off

Open in new window


If you want an exact match, set the last parameter to 1, otherwise set it to 0.

--Exact match:
Exec uSP_Check_data_used 'nvarchar', 'Joseph', 1

--Search value exists inside the column, but not necessarily and exact match:
Exec uSP_Check_data_used 'nvarchar', 'Joseph', 0

Open in new window

0
 
LVL 2

Author Closing Comment

by:brgdotnet
Comment Utility
Thanks
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now