搜索一个库中的所有表,所有列。不知道Sql Server有没有类似的工具,写了一个。有知道的望告知
begin -- 要搜索的关键字 declare @key varchar(128) set @key = '姚文婷' declare cur cursor for select object_id, name from sys.tables open cur declare @table_id varchar(255) declare @table_name varchar(255) fetch next from cur into @table_id, @table_name while @@fetch_status = 0 begin declare @sql varchar(max) set @sql = ( select 'rtrim(isnull(convert(varchar, ' + name + '), '''')) + ''$'' + ' from sys.columns where object_id = @table_id and user_type_id not in(34) for xml path('') ) set @sql = 'select ' + @sql + '''ABC'' as Data from ' + @table_name --print @sql set @sql = 'declare ccur cursor for ' + @sql exec(@sql) declare @d varchar(max) open ccur fetch next from ccur into @d while @@fetch_status = 0 begin if (charindex(@key, @d) > 0) begin print 'table: ' + @table_name + ', row: ' + @d end fetch next from ccur into @d end close ccur deallocate ccur fetch next from cur into @table_id, @table_name end close cur deallocate cur end
-- result table: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 1 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABCtable: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 2 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABCtable: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 3 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABCtable: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 4 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABCtable: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 5 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABCtable: Atdkaoqin1, row: 01$降落伞制造分厂$姚文婷$00000018$00018$0000303847$07 8 2013 12:00AM$$$$$$$$$$$$$$$$$$1.0$$ABC