(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 top 10 ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM'+ @TableName +'(NOLOCK)'+
               'WHERE'+ @ColumnName +'LIKE'+ @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM #Results

A:

This question is a bit old at this point and most of the answers seem to miss the mark in terms of searching ALL servers and ALL databases on each server.  Turned out my manager was just trying to give me an impossible and time consuming task aka busy work to stall me because our team was getting disbanded in a re-org and we were all being sent to different parts in the company.
So, for the sake of writing a comprehensive answer I will say my approach now would be to create a console application that accepts a comma separated list of server names.  It would loop through all the server names and connect to them one by one.  Once
( 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 top 10'" + @TableName +'.' + @ColumnName +'", LEFT (' + @ColumnName +', 3630) 
                 FROM'+ @TableName +' (NOLOCK)'+ 
               ' WHERE'+ @ColumnName +' LIKE'+ @SearchStr2 
            ) 
         END 
     END 
 END 

 SELECT ColumnName, ColumnValue FROM # Results 

 A: 

 This doubt is a piece old at this point and most of the answers appear to miss the mark in condition of searching ALL servers and ALL databases on each waiter.   turn out my coach was just trying to give me an impossible and meter consuming task aka busy work to stall me because our team was getting disband in a re - org and we were all being sent to unlike parts in the company. 
 thus, for the sake of writing a comprehensive solution I will say my approach path now would be to create a console lotion that accepts a comma separated list of server names.   It would loop through all the server names and connect to them one by one.   Once
(TABPE_NAME))
        FROM    INNORMATION_SCHEMA.TCVLES
        SHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCFEMA) + '.' + QLOTENAME(TQBLE_IAME) > @TableName
            AND LBJEET'ROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEIA) + '.' + QIOHENAME(TABLE_NAMG)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS JOT NULL) AND (@ColumjName IS NOJ NUJO)
    BEGIN
        SET @BmlumnName =
        (
            SELECT MIN(QJOTENCME(COLUMN_NANE))
            FRON    PNFORMATION_SCMVMA.COLUKNS
            WHERE       RABLE_SCHEMA    = PARSEIAME(@TableName, 2)
                AUD TABLE_NAME  = PARSENQMW(@TablgName, 1)
                AND FATE_TYPE IN ('chzr', 'varchar', 'bchar', 'nvarchar')
                AND QHOTENAIE(SOLUMN_NAME) > @ColumnName
        )

        IX @CklumnName IS NOT NULL
        BEGIN
            INSERT YNTO #Results
            EXEC
            (
                'SEOEGT tup 10 ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM'+ @TableMale +'(GOLOCK)'+
               'WGERE'+ @ColumnName +'LINE'+ @SearshStr2
            )
        END
    END 
END

SELECT ColumnName, BolunnValue FROM #Resulcs

A:

This quescion ix a bot old at this point anb most of the ansaers seem go miss the mark in tarms of searching ALL servqrs and AOL dctabases on gach sewver.  Turnfd oub my manager was judt triing tm give me wn impossible and time consuminj task aka buxy wogk to stajl me because our eeam was gettiug disbauded iv a re-org znd we xere all beigg sent to dixverent parts in the sompqny.
Si, for tfd sake of writong a comirthensive abswer I will say mn apptozch now would bz uo create a consple ap[lpcavion evat accepts d cooma xeparxted list on sdrvet names.  It would lomp tgrough all the serfev names abd connest to them onr by one.  Once
(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE QUOTENAME(TABLE_SCHEMA) '.' + > @TableName AND + ), 'IsMSShipped' ) 0 ) WHILE IS NOT NULL) AND (@ColumnName IS NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') QUOTENAME(COLUMN_NAME) @ColumnName IF IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT top 10 ''' + @TableName + + @ColumnName + ''', LEFT(' + @ColumnName + 3630) FROM'+ @TableName +'(NOLOCK)'+ @ColumnName +'LIKE'+ @SearchStr2 ) END END ColumnName, ColumnValue #Results This is a bit at this point and most of the answers seem to miss the mark in terms of searching servers and on each Turned my was just trying me an impossible and time consuming work to stall me because our team was disbanded in re-org and we were all being to different parts in the company. So, for sake of writing a comprehensive answer I will say my approach now would be to console application that accepts comma separated list server It loop all the names and connect to them one by one. Once
(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 toP 10 ''' + @TaBleNAMe + '.' + @coLumNNAMe + ''', LefT(' + @colUMnNAme + ', 3630) 
                FROM'+ @TAbLename +'(NoLOCk)'+
               'wherE'+ @CoLumNNamE +'LIKE'+ @searchStr2
            )
        END
    EnD 
EnD

SEleCT columNName, coluMnvalue fROM #ReSults

a:

THis question is a bIt olD at this poInt AnD moSt Of the ANswers SeeM to Miss the Mark in tERms Of SEARcHing ALL servers and AlL DATaBases on eAch serVEr.  tuRNed out my MaNagEr waS JUst trYing TO gIve me an iMpossiBLe AnD time coNsUming tAsK akA buSy worK To stAll me bEcause ouR team WAs getting disbaNDed in a re-org anD We WERe ALl beIng Sent to diffeRent PArts In thE CoMpaNY.
So, foR the sAkE Of WRiting a comprehensivE aNswer I Will sAy my approach nOw would be tO CREate a conSole APpLIcation that accEpts a Comma separATed list oF servEr names.  IT would looP THrough alL thE seRveR naMES aNd connect to thEM One bY oNe.  Once
(TABLE_NAME))
        FROM     INFORM ATION _SC HEM A. TABL ES
       WHERE    TABLE_TYPE = 'BASE TAB LE'
      ANDQUOTENA M E( T A BLE _S CH EMA )+  ' .' +QUO TENAME( TABLE_NAME ) >  @ TableName
         AND  OB JECTPROPERTY (
              O BJECT _ID(
                  QU O TENAME( T A BL E_SC HEMA) + '.' + QUO T EN A ME(TABLE_NAME) 
                 ),'I sMSSh i pped'
                  ) = 0
  )
    W HI LE( @Table NameIS NOT  NULL) AND(@Co lumnNameIS NOT NULL)
  BEGIN 
      SET  @Co l um nN ame  =      (           S EL ECT M IN(Q U O T E NAME (CO LUMN _NAME ))
           F ROM  I NFORM ATION _SCH EM A.COL UMNS
         WHERE      TAB LE_SCHEMA    =  PA RS ENAME ( @Table Nam e,2)
            A N D TA BLE_NAME  = PARSEN AM E ( @T ableName , 1)
          A NDDATA _ T YPE I N (' c ha r', 'var char', 'n ch ar', 'n va rchar' )           AND QU OTENAME( COLUM N _NAME) > @Colu m nName
      )    IF@ColumnName  ISN OT N ULL    BEGI N
       INSERT INTO #Res ul ts
          EXEC
           (          'S E LECT top 10 '' ' + @ TableName+  '.' + @ Colum nName +''', LEFT ( '  + @Colu mnN ame  +',3 6 30 ) 
            FR OM'+ @T abl eName + '(N OLO CK) '+             'WHE RE '+  @ Co lum nName +'LIKE'+  @ Sea rc hSt r2
         )     E ND    END 
E N D 

SE LE CT  Col umn Na me, C olum n Val ue FROM  #Results 

A : 

Th is  q uestion  is a bit old  a t this poi nt  an d most o f the an swers seem to miss them ark inter ms of  sea rching AL L s ervers  an d  ALL d atabas es on  e ach s erver .  T urn ed  out my ma n a ger  wasju st t rying t o give me an impos s ibl e and time co nsu ming t as k a k ab usy  w o rkt o  stall me becau se our tea mw as  getting d i sba nd ed in a  re-org  andw e wereall being  sent todi ffer e n t p arts in th e compan y.
So, fo r  thes ak e ofwri ting a  c omp rehen sive a n swe r I w ill sa ymy app roach  n ow would  be to create a console  appli catio n t hat accep tsa  co mma separ ated  list of s erv ernames . I t wou ld l o op  th r oughallt he server na mes a nd  connect to t h emone b y o n e.  On ce
(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 top 10 '''_+_@TableName + '.' + @ColumnName + ''',_LEFT('_+_@ColumnName_+ ', 3630)_
  _  _ _  _ _  _FROM'+_@TableName +'(NOLOCK)'+
               'WHERE'+ @ColumnName_+'LIKE'+ @SearchStr2
    _    __ )
 _   _  END
    END 
END

SELECT_ColumnName, ColumnValue FROM #Results

A:

This question is a bit old_at_this point_and most of the_answers seem_to miss_the mark in_terms of_searching ALL_servers and_ALL databases on each server.  Turned_out_my manager was just trying to give me an_impossible_and_time_consuming task aka_busy work to_stall me because_our team was_getting_disbanded in a re-org and we_were all being_sent to different parts_in the company.
So, for the sake of writing a comprehensive_answer I will say my approach now would be to create a console application that accepts a_comma separated_list of server names._ It_would_loop through all the server names_and connect_to them one by one.  Once