During our investigation, we often wonder where a data comes from. We often use capture SQL statements such as Event Viewer to find out. In fact, we can also use the following SQL statement. However, when the data volume is large, it is indeed slow.
Drop table #
Declare @ t varchar (255), @ C varchar (255)
Create Table # (name varchar (256), cols varchar (4000 ))
Declare table_cursor cursor
Select a. Name, B. name from sysobjects A, syscolumns B
Where a. ID = B. ID and A. xtype = 'U' and B. xtype in (167,175,231,239)
-- The above numbers are obtained from SYS. types, which limits the search for only text types. You can modify them when using them.
Open table_cursor fetch next from table_cursor
Into @ T, @ C
While (@ fetch_status = 0)
Begin
Exec ('
Set nocount on
If exists (select top 1 '+ @ C +' from ['+ @ T +'] Where ['+ @ C +'] Like ''' % Lond %'' )
Begin
If not exists (select 1 from # Where name = ''' + @ T + ''')
Insert into # select ''' + @ T + ''', ''' + @ C + '''
Else
Update # Set Cols = Cols + '', '+ @ C + ''' where name = ''' + @ T + '''
End
')
Fetch next from table_cursor into @ T, @ C
End
Close table_cursor deallocate table_cursor;
Select * from #