edit: question flagged duplicate, not. other answers on show how search tables in single database, need search tables in every database on given server.
i need search tables databases on server search string. i've got email address littered throughout tables going have change of domain , need prepare report shows these email addresses located. not going able add stored procedure databases need query that's doesn't involve exec-ing sp repeatedly. pulled code off the net , using search tables haven't been able figure out how run on databases.
drop table #results create table #results (columnname nvarchar(370), columnvalue nvarchar(3630)) set nocount on declare @searchstr nvarchar(100), @tablename nvarchar(256), @columnname nvarchar(128), @searchstr2 nvarchar(110) set @searchstr = '@domaintobereplaced.com' set @tablename = '' set @searchstr2 = quotename('%' + @searchstr + '%','''') while @tablename not null begin set @columnname = '' set @tablename = ( select min(quotename(table_schema) + '.' + quotename(table_name)) information_schema.tables table_type = 'base table' , quotename(table_schema) + '.' + quotename(table_name) > @tablename , objectproperty( object_id( quotename(table_schema) + '.' + quotename(table_name) ), 'ismsshipped' ) = 0 ) while (@tablename not null) , (@columnname not null) begin set @columnname = ( select min(quotename(column_name)) information_schema.columns table_schema = parsename(@tablename, 2) , table_name = parsename(@tablename, 1) , data_type in ('char', 'varchar', 'nchar', 'nvarchar') , quotename(column_name) > @columnname ) if @columnname not null begin insert #results exec ( 'select top 10 ''' + @tablename + '.' + @columnname + ''', left(' + @columnname + ', 3630) ' + @tablename + ' (nolock) ' + ' ' + @columnname + ' ' + @searchstr2 ) end end end select columnname, columnvalue #results
first, have collect list of databases' names sys.databases.
have create dynamic sql extract names of tables in databases in format [database].[schema].[table name]'. can linking following tables:[database].sys.schemas' & [database].sys.tables'<br/> list of text columns linking found tables to[database].sys.columns'
when of that, can create dynamic queries tables , text columns.
btw if hide data inside of text column have include in search , conversion.
Comments
Post a Comment