CODEWITHSUNDEEP
sqlsql-server-2008
user1898525
user1898525

Reputation: 133

Cycle through database and every table to find any column that contains phone number

I need to cycle through every database table and every column to see if it contains a telephone number. Anything beginning with the text + or 00 or 0.

I have found code/cursors that loop through for column names but they don't search the actual data. Any ideas how I can do this?

Upvotes: 0

Views: 1115

Answers (2)

Scott Chamberlain
Scott Chamberlain

Reputation: 127593

You need to use those cursors in conjunction with a query to find what you want.

Here is a script to search all columns of all tables. I will leave it to you to add in the search all databases

(this is assuming you are using SQL Server 2005 or newer. it may work with other systems but it is untested.

--fields types to search within
declare table_field_cursor cursor local fast_forward for select sys.tables.name, sys.columns.name from sys.columns inner join sys.tables on (sys.columns.object_id = sys.tables.object_id)inner join sys.types on (sys.columns.system_type_id = sys.types.system_type_id)
where sys.tables.type = 'U' and sys.types.name in ('varchar', 'nvarchar', 'char', 'nchar', 'text')

--Stop printing the Rows affected message.
set NOCOUNT ON

--loop through all tables & fields seaching for your string
open table_field_cursor

declare @table_name sysname
declare @field_name sysname

fetch next from table_field_cursor into @table_name, @field_name
while @@fetch_status <> -1
begin
    if @@FETCH_STATUS <> -2
        execute ('
        if EXISTS(select [' + @field_name + '] from [' + @table_name + '] with (nolock) where cast([' + @field_name + '] as nvarchar(3)) like ''+%'' or cast([' + @field_name + '] as nvarchar(3)) like ''0%'')
        begin
        print ''Found in table ' + @table_name + '.' + @field_name + '!''
        select * from [' + @table_name + ']  with (nolock) where cast([' + @field_name + '] as nvarchar(3)) like ''+%'' or cast([' + @field_name + '] as nvarchar(3)) like ''0%''
        end
        ')

    fetch next from table_field_cursor into @table_name, @field_name
end 

close table_field_cursor
deallocate table_field_cursor

This will search all tables that have a varchar, nvarchar, char, nchar, text column and see if it begins with 0 or + (we don't need to check for 00 as it is included with the 0 check). If you are able to get rid of the requirement for checking text columns you can remove the nvarchar(3) castings and possibly speed things up, the casting is done because text does not support the like query.

To search multiple databases take a look in to sp_MSForeachdb

Upvotes: 1

Costel
Costel

Reputation: 33

if you're using Oracle Database, in your loop create an sql statement using EXECUTE IMMEDIATE, there are many resources on the web, one of them is this one http://www.dba-oracle.com/t_oracle_execute_immediate.htm). using EXECUTE IMMEDIATE you can build sql statements. hope it helps. (sorry, i just saw that you're using sql server but there are equivalent things: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/4572e8eb-265c-4e6d-b501-22eef2111caf)

Upvotes: 0

Related Questions