If you want to find a word in all your databases in an instance, you are in the right article. The below code block search a phone number “5454077068” in my all database.
But note that we use phone number in the script as “‘545%407%70%68′”. Because there may be space characters. You should change your search word in this place.
|
1 |
declare @the_telephone_we_will_search varchar(50)='545%407%70%68' |
You must also change the column names to be searched.
You should change the column names in the below code part:
|
1 2 |
WHERE (c.name = ''phone'' or c.name = ''telephone'' or c.name = ''tel'' or c.name = ''gsm'' );' |
Find a Word in All Databases in SQL Server:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
CREATE TABLE #a( [databasename] [varchar](50) NOT NULL, [table_name] [varchar](50) NOT NULL, [schema_name] [nvarchar](50) NULL, [column_name] [varchar](100) NULL ) GO CREATE TABLE #aa( [databasename] [varchar](50) NOT NULL, [table_name] [varchar](50) NOT NULL, [schema_name] [nvarchar](50) NULL, [column_name] [varchar](100) NULL ) GO EXEC sp_MSforeachdb ' USE [?] insert into #a SELECT ''?'' AS databasename,t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE (c.name = ''phone'' or c.name = ''telephone'' or c.name = ''tel'' or c.name = ''gsm'' );' go declare @the_telephone_we_will_search varchar(50)='545%407%70%68' declare @table_name varchar(50) declare @databasename varchar(50) declare @schema_name varchar(50) declare @colomn_name varchar(100) DECLARE @Statement AS NVARCHAR(max) declare tables_cursor cursor for select databasename,table_name,schema_name,column_name from #a open tables_cursor fetch next from tables_cursor into @databasename,@table_name,@schema_name,@colomn_name while @@FETCH_STATUS=0 begin begin try declare @i int begin begin exec ( N'use ['+@databasename+'] insert into #aa select ''' +@databasename+ ''' as databasename,''' +@schema_name+ ''' as schema_name,'''+@table_name+ ''' as table_name ,''' +@colomn_name+ ''' as column_name from ['+@schema_name+'].['+@table_name+'] with(nolock) where CONVERT(VARCHAR(MAX),'+@colomn_name+') like ''%'+@the_telephone_we_will_search+'%''') end end end try begin catch select ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; end catch fetch next from tables_cursor into @databasename,@table_name,@schema_name,@colomn_name end deallocate tables_cursor select * from #aa select * from #a drop table #aa drop table #a |
