Tuesday, June 24, 2014

T-SQL: Search text inside tables

Script to search inside tables
 
DECLARE @SearchStr nvarchar(100)
 
SET @SearchStr = 'STRING TO SEARCH'
 
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
SET NOCOUNT ON
 
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
 
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )
 
        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END
 
SELECT ColumnName, ColumnValue FROM #Results

Link to source

17 comments:


  1. اننا في شركة الرائد للخدمات المنزلية نقدم خدمات ممتازة في الدمام بافضل الاسعار بجودة عالية لمزيد من الخدمات تفضل بزيارة
    شركة مكافحة حشرات بالدمام شركة مكافحة حشرات بالدمام
    شركة تنظيف سجاد بالدمام شركة تنظيف سجاد بالدمام
    شركة تسليك مجاري بالدمام شركة تسليك مجارى بالدمام
    افضل شركة تنظيف بالدمام افضل شركة تنظيف بالدمام
    شركة مكافحة الفئران بالدمام شركة مكافحة الفئران بالدمام
    ارخص شركة نقل عفش بالدمام ارخص شركة نقل عفش بالدمام

    ReplyDelete

  2. Packers and Movers Pune Provide High Quality ***Household Shifting, Home/Office Relocation, Insurance, Packing, Loading, ###Car Transportation Service Pune and High experiences, Top Rated, Safe and Reliable, Best and Secure Packers and Movers Pune Team List. Get ✔✔✔ Affordable Rate Charts and Compare Quotation and Save Money and Time .
    Packers And Movers Pune

    ReplyDelete
  3. Hire Best Packers And Movers Mumbai for hassle-free Household Shifting, ***Office Relocation, ###Car Transporation, Loading Unloading, packing Unpacking at affordable ✔✔✔ Price Quotation. Top Rated, Safe and Secure Service Providers who can help you with 24x7 and make sure a Untroubled Relocation Services at Cheapest/Lowest Rate
    Packers And Movers Mumbai

    ReplyDelete

  4. Book Best and Verified packers and movers in Pune, Expert in Home Relocation within or outside Pune. Compare 3 quotes from nearby packers and movers in Pune. Get 100% safe & affordable packing moving from Professional Packers and Movers.

    Packers and Movers in Pune
    Packers and Movers in Hinjewadi
    Packers and Movers in hadapsar
    Packers and Movers in baner
    Packers and Movers in pimple-saugagar
    Packers and Movers in Viman nagar
    Packers and Movers in Pimpri
    Packers and Movers in Kothrud
    Packers and Movers in Aundh
    Packers and Movers in Magarpatta

    ReplyDelete
  5. Book Best and Verified packers and movers in Pune, Expert in Home Relocation within or outside Pune. Compare 3 quotes from nearby packers and movers in Pune. Get 100% safe & affordable packing moving from Professional Packers and Movers.

    Packers and Movers in Pune
    Packers and Movers in Hinjewadi
    Packers and Movers in hadapsar
    Packers and Movers in baner
    Packers and Movers in pimple-saugagar
    Packers and Movers in Viman nagar
    Packers and Movers in Pimpri
    Packers and Movers in Kothrud
    Packers and Movers in Aundh
    Packers and Movers in Magarpatta

    ReplyDelete
  6. This is really a pleasant and useful, containing all data furthermore greatly affect the new innovation. Much obliged for sharing it @ Packers And Movers Mumbai

    ReplyDelete
  7. I think your blog will easily to get the correct market, because it’s having the piece of content. So it can easily to attract the visitors. Please feel free visit our website @ Local Packers And Movers Bangalore

    ReplyDelete
  8. Packers and Movers Bangalore as a Services providing company can make all the difference to your ###Home Relocation experience. Bangalore based Company which offers versatile solutions, Right team that easily reduce the stress associated with a ✔✔✔Household Shifting, ***Vehicle Transportation. we help things run smoothly and reduce breakages and offer you seamless, Affordable, Reliable Shifting Services, Compare Shifting Charges @ Packers And Movers Bangalore

    ReplyDelete
  9. Packers and Movers Hyderabad Give Certified and Verified Service Providers, Cheap and Best ###Office Relocation Charges, ***Home Shifting, ✔✔✔Goods Insurance worth Rs. 10,000, Assurance for Local and Domestic House Shifting. Safe and Reliable Household Shifting Services in Hyderabad with Reasonable Packers and Movers Price Quotation @ Packers And Movers Hyderabad

    ReplyDelete
  10. Certified Technicians We at SPOLU offer the best mobile phone repairing services for all branded smartphones & tablets, across India through our dedicated mobile phone repair service centers in Ghaziabad, Noida, Greater Noida, and Gurgaon.
    Doorstep Mobile Repair Service in Ghaziabad

    ReplyDelete
  11. Orel State University which is also known as Government State Educational Institution of Higher Education is identified as a prime higher education Institution in the metropolis of Orel. Orel State University was founded in 1931 as an industrial-Pedagogical Institute. In 1932 Orel Pedagogical University became Orel State University. Orel State University is unique in many aspects.
    To get Admission and more information-
    Orel State Medical University

    ReplyDelete
  12. Orel State University is unique in many ways. It is the only classical university Orel, a member of the Association of classical Russian universities. It is the only institution in the region, which is training on a wide range of areas and specialties of higher and postgraduate education, training, re-training (more than 300).
    To Get more information and Admission-
    Orel State Medical University

    ReplyDelete
  13. Orel State University is unique in many aspects. Orel State University is the only classical university who is the member of the Association of classical universities of the Russian Federation and the only educational institution in the region which provide training on a wide range of areas and specialties of higher and postgraduate education, training, retraining.

    Orel State Medical University

    ReplyDelete
  14. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔ ✔ ✔ Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @ Packers And Movers Chennai

    ReplyDelete
  15. I must say you had done a tremendous job,I appreciate all your efforts.Thanks alot for your writings......@ Packers and Movers Ahmedabad

    ReplyDelete