Monday 16th of September 2019 10:23:36 PM
Our Services
Select Theme
Back
CREATE PROCEDURE [dbo].[sp_SearchInSP]
    (
      -- Add the parameters for the stored procedure here
      @SearchString VARCHAR(100) = ''
    )
 AS
    BEGIN
        --Declare
        DECLARE @SString NVARCHAR(50)
        DECLARE @getdbname SYSNAME
        DECLARE @sqlstm NVARCHAR(1000)
        CREATE TABLE #tmp
            (
              DBName VARCHAR(100) ,
              SPName VARCHAR(100)
            )
        DECLARE dbname CURSOR
        FOR
            --get all the names of the Databases in order by name
                SELECT  '[' + name + ']'
        FROM    master.dbo.sysdatabases
        ORDER BY name
        OPEN dbname

                --Get the first Name
        FETCH NEXT FROM dbname INTO @getdbname

        WHILE @@FETCH_STATUS = 0
            BEGIN
                PRINT @getdbname
                                --set the search string
                SET @SString = @SearchString

                                --append the search pattern
                SET @SString = '%' + @SString + '%'
                SET @sqlstm = 'SELECT  DISTINCT ''' + @getdbname + ''',
                                                                s.name
                                                                FROM    ' + @getdbname + '.dbo.syscomments c
                                                                INNER JOIN  ' + @getdbname + '.dbo.sysobjects s
                                                                ON      c.id = s.id
                                                                WHERE   s.type IN ( ''p'', ''tr'' )
                                                                AND c.text LIKE ''%' + @SString + '%''
                                                                ORDER BY [Name]'


                --Execute the Query
                INSERT  INTO #tmp
                        EXEC ( @sqlstm
                            )
                FETCH NEXT FROM dbname INTO @getdbname
            END

                --Close the Cursor and Deallocate it from memory
        CLOSE dbname
        DEALLOCATE dbname

        SELECT  *
        FROM    #tmp AS T
        DROP TABLE #tmp

    END
 
     
     
Home | About Us | Products | Services |  Templates | Demo | Contact Us | Careers | Feedback | SiteMap
2005-2014 Logical Technologies. All Rights Reserved.