SQL Server Support for Linked Server and Distributed Queries against Windows Azure SQL Database

It is now possible to add a Windows Azure SQL Database as a Linked Server and then use it with Distributed Queries that span the on-premises and cloud databases. This is a new component for database hybrid solutions spanning on-premises corporate networks and the Windows Azure cloud.  

SQL Server box product contains a feature called “Distributed Query” that allows users to write queries to combine data from local data sources and data from remote sources (including data from non-SQL Server data sources) defined as Linked Servers. Previously Windows Azure SQL Databases didn’t support distributed queries natively, and needed to use the ODBC-to-OLEDB proxy, which was not recommended for performance reasons. We are happy to announce that Windows Azure SQL Databases can now be used through “Distributed Query”. In practical terms, every single Windows Azure SQL Database (except the virtual master) can be added as an individual Linked Server and then used directly in your database applications as any other database.

The benefits of using Windows Azure SQL Database include manageability, high availability, scalability, working with a familiar development model, and a relational data model. The requirements of your database application play an important role in deciding how it would use Windows Azure SQL Databases in the cloud. You can move all of your data at once to Window Azure SQL Databases, or progressively move some of your data while keeping the remaining data on-premises. For such a hybrid database application, Windows Azure SQL Databases can now be added as linked servers and the database application can issue distributed queries to combine data from Windows Azure SQL Databases and on-premise data sources.

 Here’s a simple example explaining how to connect to a Windows Azure SQL Database using Distributed Queries:

—— Configure the linked server – Add one Windows Azure SQB DB as Linked Server EXEC sp_addlinkedserver @server=‘myLinkedServer’, — here you can specify the name of the linked server @srvproduct=”,      @provider=‘sqlncli’, — using SQL Server native client @datasrc=‘myServer.database.windows.net’,   — add here your server name @location=”, @provstr=”, @catalog=‘myDatabase’  – add here your database name as initial catalog (you cannot connect to the master database) – Add credentials and options to this linked server EXEC sp_addlinkedsrvlogin @rmtsrvname = ‘myLinkedServer’, @useself = ‘false’, @rmtuser = ‘myLogin’,             – add here your login on Azure DB @rmtpassword = ‘myPassword’ – add here your password on Azure DB EXEC sp_serveroption ‘myLinkedServer’, ‘rpc out’, true; —— Now you can use the linked server to execute 4-part queries – You can create a new table in the Azure DBexec (‘CREATE TABLE t1tutut2(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )’) at myLinkedServer – Insert data from your local SQL Server exec (‘INSERT INTO t1tutut2 VALUES(1),(2),(3)’) at myLinkedServer   – Query the data using 4-part names select * from myLinkedServer.myDatabase.dbo.myTable

More information on Linked Servers and Distributed Queries is available here.

Recursive Triggers

A trigger may now call itself recursively if this option is set to on (Default) for a particular database.  Just like SQL Server 2012, the option can be configured via the following query:

ALTER DATABASE YOURDBNAME SET RECURSIVE_TRIGGERS ON|OFF;

For full information on recursive triggers, see the SQL Server 2012 Books Online Topic.

DBCC SHOW_STATISTICS Now Supported

DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the index scan operator in the query plan, improving query performance by avoiding a resource-intensive index scan.  For more information click here.

Ability to Configure SQL Database Firewall Rules at the Database Level

Previously Windows Azure SQL Database firewall rules could be set only at the server level, either through the management portal or via T-SQL commands. Now, firewall rules can be additionally set at the more granular database level, with different rules for different databases hosted on the same logical SQL Database server.  For more information click here. 

For questions or more technical information about these features, you can post a question on theSQL Database MSDN Support Forum.

Shell And Wait - Run External Application in VBA

Shell And Wait

This pages describes the VBA Shell function and provides a procedure that will wait for a Shell'd function to terminate.

The Shell Function

The VBA Shell function can be used to start an external program or perform any operation for which you would normally use the Run item on the Windows start menu. The Shell function starts the command text and then immediately returns control back to the calling VBA code -- it does not wait for the command used in Shell to terminate. This page describes a function named ShellAndWait that will call the Shell function and then wait for the Shell'd process to terminate or for a prescribed timeout interval to expire.

The ShellAndWait Function

The ShellAndWait function calls Shell and then waits for the Shell'd process to terminate. You can specify an interval after which the function times out and returns to the caller. The declaration of ShellAndWait is as follows:

    Public Function ShellAndWait(ShellCommand As String, _
                        TimeOutMs As Long, _
                        ShellWindowState As VbAppWinStyle, _
                        BreakKey As ActionOnBreak) As ShellAndWaitResult

In the declaration, ShellCommand is the string that is passed to the VBA Shell function. TimeOutMs is the number of milliseconds to wait before returning a time-out result. ShellWindowsState is an item in theVbAppWinStyle enum that specifies the window style in which to open the Shell'd process. This value is simply passed through to the Shell function. BreakKey indicates how to handle the Application's Cancel key (CTRL BREAK). These parameters are described below.

ShellCommand

This parameter is the command text that is passed to the Shell function. This must be a valid command string; that is, a string that would work in the Run dialog on the Windows Start menu. If the text is invalid (such as attempting to execute a non-existant exe file), the function returns a result indicating failure.

TimeOutMs

This parameter specifies the number of milliseconds to wait for the Shell'd process to terminate. If the specified interval expires before the process ends, the wait is abandoned and the function returns a value indicating a time out occurred. If TimeOutMs is 0, the wait never expires and ShellAndWait will wait indefinitely for the process to terminate.

ShellWindowState

This parameter should be one of the values in the VbAppWinStyle enum that indicates the type of window (e.g., maximized or minimized) in which the Shell'd process should open. This value is simply passed through to theShell command. If this value is not valid, the function returns a result indicating failure.

BreakKey

This parameter should be one of the values in the ActionOnBreak enum to indicate how the code should respond if the user presses CTRL Break. If BreakKey is IgnoreBreak, the cancel key is ignored. If BreakKey isAbandonWait, the wait is terminated and the function returns a valid indicating that the user abandoned the wait. IfBreakKey is PromptUser, the function displays a vbYesNo message box asking the user whether to abandon or continue waiting. The ActionOnBreak enum is shown below.

    Public Enum ActionOnBreak
        IgnoreBreak = 0
        AbandonWait = 1
        PromptUser = 2
    End Enum

Return Values

The function returns as its result one of the values in the ShellAndWaitResult enum, shown below:

    Public Enum ShellAndWaitResult
        Success = 0
        Failure = 1
        TimeOut = 2
        InvalidParameter = 3
        SysWaitAbandoned = 4
        UserWaitAbandoned = 5
        UserBreak = 6
    End Enum

Success indicates that the Shell'd process terminated successfully before the TimeOutMs interval expired. M

Failure indicates that the Shell function failed, most likely due to an invalid command string in theShellCommand parameter.

TimeOut indicates that the interval specified by TimeOutMs expired before the process terminated. This result is returned only if TimeOutMs is greater than zero.

InvalidParameter indicates that one of the parameters passed to ShellAndWait is invalid.

SysWaitAbandoned indicates that Windows abandoned the wait operation for internal reasons.

UserWaitAbandoned indicates that the user abandoned the wait operation by pressing CTRL Break. This is returne only if BreakKey is set to AbandonWait.

UserBreak indicates that the user press CTRL Break to terminate the wait and responded "no" to the "continue to wait" prompt. This value is returned only when BreakKey is set to PromptUser.

You can download a module file containing the code.

The complete code module is shown below.

Option Explicit
Option Compare Text

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modShellAndWait
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' This page on the web site: www.cpearson.com/Excel/ShellAndWait.aspx
' 9-September-2008
'
' This module contains code for the ShellAndWait function that will Shell to a process
' and wait for that process to end before returning to the caller.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function WaitForSingleObject Lib "kernel32" ( _
    ByVal hHandle As Long, _
    ByVal dwMilliseconds As Long) As Long

Private Declare Function OpenProcess Lib "kernel32.dll" ( _
    ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal dwProcessId As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" ( _
    ByVal hObject As Long) As Long

Private Const SYNCHRONIZE = &H100000

Public Enum ShellAndWaitResult
    Success = 0
    Failure = 1
    TimeOut = 2
    InvalidParameter = 3
    SysWaitAbandoned = 4
    UserWaitAbandoned = 5
    UserBreak = 6
End Enum

Public Enum ActionOnBreak
    IgnoreBreak = 0
    AbandonWait = 1
    PromptUser = 2
End Enum

Private Const STATUS_ABANDONED_WAIT_0 As Long = &H80
Private Const STATUS_WAIT_0 As Long = &H0
Private Const WAIT_ABANDONED As Long = (STATUS_ABANDONED_WAIT_0 + 0)
Private Const WAIT_OBJECT_0 As Long = (STATUS_WAIT_0 + 0)
Private Const WAIT_TIMEOUT As Long = 258&
Private Const WAIT_FAILED As Long = &HFFFFFFFF
Private Const WAIT_INFINITE = -1&


Public Function ShellAndWait(ShellCommand As String, _
                    TimeOutMs As Long, _
                    ShellWindowState As VbAppWinStyle, _
                    BreakKey As ActionOnBreak) As ShellAndWaitResult
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShellAndWait
'
' This function calls Shell and passes to it the command text in ShellCommand. The function
' then waits for TimeOutMs (in milliseconds) to expire.
'
'   Parameters:
'       ShellCommand
'           is the command text to pass to the Shell function.
'
'       TimeOutMs
'           is the number of milliseconds to wait for the shell'd program to wait. If the
'           shell'd program terminates before TimeOutMs has expired, the function returns
'           ShellAndWaitResult.Success = 0. If TimeOutMs expires before the shell'd program
'           terminates, the return value is ShellAndWaitResult.TimeOut = 2.
'
'       ShellWindowState
'           is an item in VbAppWinStyle specifying the window state for the shell'd program.
'
'       BreakKey
'           is an item in ActionOnBreak indicating how to handle the application's cancel key
'           (Ctrl Break). If BreakKey is ActionOnBreak.AbandonWait and the user cancels, the
'           wait is abandoned and the result is ShellAndWaitResult.UserWaitAbandoned = 5.
'           If BreakKey is ActionOnBreak.IgnoreBreak, the cancel key is ignored. If
'           BreakKey is ActionOnBreak.PromptUser, the user is given a ?Continue? message. If the
'           user selects "do not continue", the function returns ShellAndWaitResult.UserBreak = 6.
'           If the user selects "continue", the wait is continued.
'
'   Return values:
'            ShellAndWaitResult.Success = 0
'               indicates the the process completed successfully.
'            ShellAndWaitResult.Failure = 1
'               indicates that the Wait operation failed due to a Windows error.
'            ShellAndWaitResult.TimeOut = 2
'               indicates that the TimeOutMs interval timed out the Wait.
'            ShellAndWaitResult.InvalidParameter = 3
'               indicates that an invalid value was passed to the procedure.
'            ShellAndWaitResult.SysWaitAbandoned = 4
'               indicates that the system abandoned the wait.
'            ShellAndWaitResult.UserWaitAbandoned = 5
'               indicates that the user abandoned the wait via the cancel key (Ctrl+Break).
'               This happens only if BreakKey is set to ActionOnBreak.AbandonWait.
'            ShellAndWaitResult.UserBreak = 6
'               indicates that the user broke out of the wait after being prompted with
'               a ?Continue message. This happens only if BreakKey is set to
'               ActionOnBreak.PromptUser.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim TaskID As Long
Dim ProcHandle As Long
Dim WaitRes As Long
Dim Ms As Long
Dim MsgRes As VbMsgBoxResult
Dim SaveCancelKey As XlEnableCancelKey
Dim ElapsedTime As Long
Dim Quit As Boolean
Const ERR_BREAK_KEY = 18
Const DEFAULT_POLL_INTERVAL = 500

If Trim(ShellCommand) = vbNullString Then
    ShellAndWait = ShellAndWaitResult.InvalidParameter
    Exit Function
End If

If TimeOutMs < 0 Then
    ShellAndWait = ShellAndWaitResult.InvalidParameter
    Exit Function
ElseIf TimeOutMs = 0 Then
    Ms = WAIT_INFINITE
Else
    Ms = TimeOutMs
End If

Select Case BreakKey
    Case AbandonWait, IgnoreBreak, PromptUser
        ' valid
    Case Else
        ShellAndWait = ShellAndWaitResult.InvalidParameter
        Exit Function
End Select

Select Case ShellWindowState
    Case vbHide, vbMaximizedFocus, vbMinimizedFocus, vbMinimizedNoFocus, vbNormalFocus, vbNormalNoFocus
        ' valid
    Case Else
        ShellAndWait = ShellAndWaitResult.InvalidParameter
        Exit Function
End Select

On Error Resume Next
Err.Clear
TaskID = Shell(ShellCommand, ShellWindowState)
If (Err.Number <> 0) Or (TaskID = 0) Then
    ShellAndWait = ShellAndWaitResult.Failure
    Exit Function
End If

ProcHandle = OpenProcess(SYNCHRONIZE, False, TaskID)
If ProcHandle = 0 Then
    ShellAndWait = ShellAndWaitResult.Failure
    Exit Function
End If

On Error GoTo ErrH:
SaveCancelKey = Application.EnableCancelKey
Application.EnableCancelKey = xlErrorHandler
WaitRes = WaitForSingleObject(ProcHandle, DEFAULT_POLL_INTERVAL)
Do Until WaitRes = WAIT_OBJECT_0
    DoEvents
    Select Case WaitRes
        Case WAIT_ABANDONED
            ' Windows abandoned the wait
            ShellAndWait = ShellAndWaitResult.SysWaitAbandoned
            Exit Do
        Case WAIT_OBJECT_0
            ' Successful completion
            ShellAndWait = ShellAndWaitResult.Success
            Exit Do
        Case WAIT_FAILED
            ' attach failed
            ShellAndWait = ShellAndWaitResult.Failure
            Exit Do
        Case WAIT_TIMEOUT
            ' Wait timed out. Here, this time out is on DEFAULT_POLL_INTERVAL.
            ' See if ElapsedTime is greater than the user specified wait
            ' time out. If we have exceed that, get out with a TimeOut status.
            ' Otherwise, reissue as wait and continue.
            ElapsedTime = ElapsedTime + DEFAULT_POLL_INTERVAL
            If Ms > 0 Then
                ' user specified timeout
                If ElapsedTime > Ms Then
                    ShellAndWait = ShellAndWaitResult.TimeOut
                    Exit Do
                Else
                    ' user defined timeout has not expired.
                End If
            Else
                ' infinite wait -- do nothing
            End If
            ' reissue the Wait on ProcHandle
            WaitRes = WaitForSingleObject(ProcHandle, DEFAULT_POLL_INTERVAL)
            
        Case Else
            ' unknown result, assume failure
            ShellAndWait = ShellAndWaitResult.Failure
            Exit Do
            Quit = True
    End Select
Loop

CloseHandle ProcHandle
Application.EnableCancelKey = SaveCancelKey
Exit Function

ErrH:
Debug.Print "ErrH: Cancel: " & Application.EnableCancelKey
If Err.Number = ERR_BREAK_KEY Then
    If BreakKey = ActionOnBreak.AbandonWait Then
        CloseHandle ProcHandle
        ShellAndWait = ShellAndWaitResult.UserWaitAbandoned
        Application.EnableCancelKey = SaveCancelKey
        Exit Function
    ElseIf BreakKey = ActionOnBreak.IgnoreBreak Then
        Err.Clear
        Resume
    ElseIf BreakKey = ActionOnBreak.PromptUser Then
        MsgRes = MsgBox("User Process Break." & vbCrLf & _
            "Continue to wait?", vbYesNo)
        If MsgRes = vbNo Then
            CloseHandle ProcHandle
            ShellAndWait = ShellAndWaitResult.UserBreak
            Application.EnableCancelKey = SaveCancelKey
        Else
            Err.Clear
            Resume Next
        End If
    Else
        CloseHandle ProcHandle
        Application.EnableCancelKey = SaveCancelKey
        ShellAndWait = ShellAndWaitResult.Failure
    End If
Else
    ' some other error. assume failure
    CloseHandle ProcHandle
    ShellAndWait = ShellAndWaitResult.Failure
End If

Application.EnableCancelKey = SaveCancelKey

End Function

---------------------------------------------------------------------

This page is from http://www.cpearson.com/excel/ShellAndWait.aspx

Max length of SQL Server instance name?

Instance names for SQL Server are limited to 16 characters

http://msdn.microsoft.com/en-us/library/ms143531(v=SQL.105).aspx

It doesn't list SQL Server 2000 online but looking at the BOL that I have installed for SQL Server 2000 it is also limited to 16 characters.

EDIT

Use this screen to add and maintain instances of Microsoft® SQL Server™ 2000.

Options

Default

• When selected, a default instance of SQL Server 2000 is installed. Click Next to proceed with the install process.
• When cleared, you can install or maintain a named instance of SQL Server 2000.
  Note If this check box is not enabled, Setup has detected a default instance of SQL Server on this computer. The default instance could be an installation of SQL Server 6.5, SQL Server version 7.0, or it could be the default instance of SQL Server 2000, already installed. Only one installation of SQL Server, any version, can be the default instance at any one time. For more information, see Multiple Instances of SQL Server.

Instance Name

Enter a new instance name, or the name of the instance to maintain. Review and follow the rules for instance names.

Important It is recommended that instance names be kept to less than 10 characters. Instance names can appear in the user interface of various SQL Server and system tools; shorter names are more readable.

*Instance Naming Rules *

• An instance name is not case-sensitive.
• An instance name cannot be the terms Default or MSSQLServer.
• Instance names must follow the rules for SQL Server identifiers and cannot be reserved keywords.
• Instance names are limited to 16 characters.
• The first character in the instance name must be a letter, an ampersand (&), an underscore (_), or a number sign (#). Acceptable letters are those defined by the Unicode Standard 2.0, which includes Latin characters a-z and A-Z, in addition to letter characters from other languages.
• Subsequent characters can be:
  • Letters as defined in the Unicode Standard 2.0.
  • Decimal numbers from either Basic Latin or other national scripts.
  • The dollar sign ($), a number sign (#), or an underscore (_).
• Embedded spaces or special characters are not allowed in instance names. Neither is the backslash (), a comma (,), a colon (:), or the at sign (@).

Warning Only characters that are valid in the current Microsoft Windows® code page can be used in instance names in SQL Server 2000. If a Unicode character not supported under the current code page is used, an error occurs.

Search for a string in all tables of SQL Server Database

This article is from: http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58

Description

This is a simple stored procedure which can search through all the data in the SQL Server database tables. Also this has capability to search in the selected tables if the table names are specified with comma separated values. This has a capability to generate the SQL alone without executing the SQL. Enclosing the script version of it also.

Parameters and usage

@Tablenames -- Provide a single table name or multiple table name with comma seperated.

If left blank , it will check for all the tables in the database

@SearchStr -- Provide the search string. Use the '%' to coin the search.

EX : X%--- will give data staring with X

%X--- will give data ending with X

%X%--- will give data containig X

@GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.

By default it is 0 and it will search.

Samples :

1. To search data in a table

EXEC SearchTables @Tablenames = 'T1'

,@SearchStr = '%TEST%'

The above sample searches in table T1 with string containing TEST.

2. To search in a multiple table

EXEC SearchTables @Tablenames = 'T2'

,@SearchStr = '%TEST%'

The above sample searches in tables T1 & T2 with string containing TEST.

3. To search in a all table

EXEC SearchTables @Tablenames = '%'

,@SearchStr = '%TEST%'

The above sample searches in all table with string containing TEST.

4. Generate the SQL for the Select statements

EXEC SearchTables @Tablenames = 'T1'

,@SearchStr = '%TEST%'

,@GenerateSQLOnly = 1

Script

SQL

IF OBJECT_ID('SP_SearchTables','P') IS NOT NULL 
    DROP PROCEDURE SP_SearchTables 
GO 
CREATE PROCEDURE SP_SearchTables 
 @Tablenames VARCHAR(500) 
,@SearchStr NVARCHAR(60) 
,@GenerateSQLOnly Bit = 0 
AS 
 
/* 
    Parameters and usage 
 
    @Tablenames        -- Provide a single table name or multiple table name with comma seperated.  
                        If left blank , it will check for all the tables in the database 
    @SearchStr        -- Provide the search string. Use the '%' to coin the search.  
                        EX : X%--- will give data staring with X 
                             %X--- will give data ending with X 
                             %X%--- will give data containig  X 
    @GenerateSQLOnly -- Provide 1 if you only want to generate the SQL statements without seraching the database.  
                        By default it is 0 and it will search. 
 
    Samples : 
 
    1. To search data in a table 
 
        EXEC SP_SearchTables @Tablenames = 'T1' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in table T1 with string containing TEST. 
 
    2. To search in a multiple table 
 
        EXEC SP_SearchTables @Tablenames = 'T2' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in tables T1 & T2 with string containing TEST. 
     
    3. To search in a all table 
 
        EXEC SP_SearchTables @Tablenames = '%' 
                         ,@SearchStr  = '%TEST%' 
 
        The above sample searches in all table with string containing TEST. 
 
    4. Generate the SQL for the Select statements 
 
        EXEC SP_SearchTables @Tablenames        = 'T1' 
                         ,@SearchStr        = '%TEST%' 
                         ,@GenerateSQLOnly    = 1 
 
*/ 
 
    SET NOCOUNT ON 
 
    DECLARE @MatchFound BIT 
 
    SELECT @MatchFound = 0 
 
    DECLARE @CheckTableNames Table 
    ( 
    Tablename sysname 
    ) 
 
    DECLARE @SQLTbl TABLE 
    ( 
     Tablename        SYSNAME 
    ,WHEREClause    VARCHAR(MAX) 
    ,SQLStatement   VARCHAR(MAX) 
    ,Execstatus        BIT  
    ) 
 
    DECLARE @SQL VARCHAR(MAX) 
    DECLARE @tmpTblname sysname 
    DECLARE @ErrMsg VARCHAR(100) 
 
    IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%') 
    BEGIN 
 
        INSERT INTO @CheckTableNames 
        SELECT Name 
          FROM sys.tables 
    END 
    ELSE 
    BEGIN 
 
        SELECT @SQL = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + '''' 
 
        INSERT INTO @CheckTableNames 
        EXEC(@SQL) 
 
    END 
 
    IF NOT EXISTS(SELECT 1 FROM @CheckTableNames) 
    BEGIN 
         
        SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
 
    END 
     
    INSERT INTO @SQLTbl 
    ( Tablename,WHEREClause) 
    SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME), 
            ( 
                SELECT '[' + SC.Name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10) 
                  FROM SYS.columns SC 
                  JOIN SYS.types STy 
                    ON STy.system_type_id = SC.system_type_id 
                   AND STy.user_type_id =SC.user_type_id 
                 WHERE STY.name in ('varchar','char','nvarchar','nchar','text') 
                   AND SC.object_id = ST.object_id 
                 ORDER BY SC.name 
                FOR XML PATH('') 
            ) 
      FROM  SYS.tables ST 
      JOIN @CheckTableNames chktbls 
                ON chktbls.Tablename = ST.name  
      JOIN SYS.schemas SCh 
        ON ST.schema_id = SCh.schema_id 
     WHERE ST.name <> 'SearchTMP' 
      GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' +  QUOTENAME(ST.NAME) ; 
     
 
      UPDATE @SQLTbl 
         SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 
 
      DELETE FROM @SQLTbl 
       WHERE WHEREClause IS NULL 
     
    WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0) 
    BEGIN 
 
        SELECT TOP 1 @tmpTblname = Tablename , @SQL = SQLStatement 
          FROM @SQLTbl  
         WHERE ISNULL(Execstatus ,0) = 0 
 
         IF @GenerateSQLOnly = 0 
         BEGIN 
 
            IF OBJECT_ID('SearchTMP','U') IS NOT NULL 
                DROP TABLE SearchTMP 
                 
            EXEC (@SQL) 
 
            IF EXISTS(SELECT 1 FROM SearchTMP) 
            BEGIN 
                SELECT Tablename=@tmpTblname,* FROM SearchTMP 
                SELECT @MatchFound = 1 
            END 
 
         END 
         ELSE 
         BEGIN 
             PRINT REPLICATE('-',100) 
             PRINT @tmpTblname 
             PRINT REPLICATE('-',100) 
             PRINT replace(@SQL,'INTO SearchTMP','') 
         END 
 
         UPDATE @SQLTbl 
            SET Execstatus = 1 
          WHERE Tablename = @tmpTblname 
 
    END 
 
    IF @MatchFound = 0  
    BEGIN 
        SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
    END 
     
    SET NOCOUNT OFF 
 
go