[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3584
  • Last Modified:

MS SQL Search all tables, all rows?

Hi I am wondering if there is a simple way to search all tables and all columns for a string of text in MS Sql Server 2005.?
  • 3
  • 2
1 Solution
Extreme66Author Commented:
thanks, I saw that on google, I was wondering if there was any way to do this without creating more tables. I should have been more clear
No, its not that easy of a thing to do really....this is a really effective way to do it though.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Extreme66Author Commented:
ok, thanks, I will see if I can get the privilages to do so.
the following code will do what you are looking for.  however, while this script is functional, it is not optimal.  it will consume a large amount of system resources

declare @sql varchar(8000), @tbl varchar(255), @col varchar(255), @data varchar(50)

set @data = 'string you are looking for'

declare cur_tbl cursor for
      select a.name, b.name from sysobjects a, syscolumns b, systypes c where a.id = b.id and a.type = 'U' and c.xtype = b.xtype and c.name in ( 'varchar', 'nvarchar', 'text', 'ntext' )
open cur_tbl
fetch next from cur_tbl into @tbl, @col
while @@fetch_status = 0
      set @sql = '
                        if exists (select * from [' + @tbl + '] where convert( varchar(255), [' + @col + '] ) like ''%' + @data + '%'')
                              select tbl=''' + @tbl + ''', col=''' + @col + ''', [' + @col + '], * from [' + @tbl + '] where convert( varchar(255), [' + @col + '] ) like ''%' + @data + '%''

      fetch next from cur_tbl into @tbl, @col
close cur_tbl
deallocate cur_tbl
Extreme66Author Commented:
wow, nice job, thanks

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now