MS SQL Search all tables, all rows?

Posted on 2009-02-13
Last Modified: 2012-05-06
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.?
Question by:Extreme66
    LVL 60

    Expert Comment


    Author Comment

    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
    LVL 60

    Expert Comment

    No, its not that easy of a thing to do really....this is a really effective way to do it though.

    Author Comment

    ok, thanks, I will see if I can get the privilages to do so.
    LVL 1

    Accepted Solution

    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, from sysobjects a, syscolumns b, systypes c where = and a.type = 'U' and c.xtype = b.xtype and 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

    Author Closing Comment

    wow, nice job, thanks

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now