Geek Answers Handbook

In SQL Server Management Studio, can I search for assets in multiple databases?

My everyday IDE is Eclipse, which has a wonderful Open Resource function (CTRL + SHIFT + R or Navigate> Open Resource) that allows the user to search for files / resources for several projects.

I cannot find a similar function in SQL Server Management Studio, is there any?

+5
source share
6 answers

You can search for objects in the sql database using representations of the information scheme http://msdn.microsoft.com/en-us/library/ms186778.aspx There is one for tables, columns, functions, sprocs, etc.

* INFORMATION_SCHEMA.routines ROUTINE_DEFINITION "% xp% _"

+3

. SMS , .

+1

sp_MSforeachdb :

sp_MSforeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.routines WHERE ROUTINE_TYPE = ''PROCEDURE'''

. , , ..

+1

, - , . CURSOR . temp .

. , - . , DMV. -?

0

CLR . , , . , , , .., XML. , , /DBA, ... AdventureWorks 2 , .

https://pastebin.com/RRTrt8ZN

/*
    This script creates a CLR stored procedure and its assembly on a database that will let you search for
    keywords separated by a space on all columns of all tables of all types except 'binary', 'varbinary', 'bit',
    'timestamp', 'image', 'sql_variant', and 'hierarchyid'. This was made as a CLR stored proc to take advantage
    of explicit parallelism to make the search a lot faster. Be aware that this will use many cores so only use
    this for occasional DBA work. This has the potential to cause a DDoS type of situation if broad searches with
    many results are hammered into the server, since each request can try to parallelize its search. An optional
    parameter exists to limit parallelism to a set number of cores. You can also set filters on the tables or
    columns to search, including logical operators OR, AND, NOT, and parenthesis (see examples below). Results
    are returned as XML rows.

    To install you need owner rights. Also, because SQL Server does not allow secondary CLR threads access to 
    the stored procedure context, we extract the connection string from the first context connection we make.
    This works fine, but only if you are connected with a trusted connection (using a Windows account).

    ------------------------------------------------------------------
    -- CLR access must be enabled on the instance for this to work. --
    ------------------------------------------------------------------
    -- sp_configure 'show advanced options', 1;                     --
    -- GO                                                           --
    -- RECONFIGURE;                                                 --
    -- GO                                                           --
    -- sp_configure 'clr enabled', 1;                               --
    -- GO                                                           --
    -- RECONFIGURE;                                                 --
    -- GO                                                           --
    ------------------------------------------------------------------

    -----------------------------------------------------------------------------------
    -- Database needs to be flagged trustworthy to be able to access CLR assemblies. --
    -----------------------------------------------------------------------------------
    -- ALTER DATABASE [AdventureWorks] SET TRUSTWORTHY ON;                           --
    -----------------------------------------------------------------------------------

    Example usages:
    ---------------
    Using all available processors on the server:
        EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael'

    Limiting the server to 4 concurrent threads:
        EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @maxDegreeOfParallelism = 4

    Using logical operators in search terms:
        EXEC [dbo].[SearchAllTables] @valueSearchTerm = '(john or michael) and not jack', @tablesSearchTerm = 'not contact'

    Limiting search to table names and/or column names containing some search terms:
        EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @tablesSearchTerm = 'person contact', @columnsSearchTerm = 'address name'

    Limiting search results to the first row of each table where the terms are found:
        EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @getOnlyFirstRowPerTable = 1

    Limiting the search to the schema only automatically returns only the first row for each table:
        EXEC [dbo].[SearchAllTables] @tablesSearchTerm = 'person contact'

    Only return the search queries:
        EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john michael', @tablesSearchTerm = 'person contact', @onlyOutputQueries = 1

    Capturing results into temporary table and sorting:
        CREATE TABLE #temp (Result NVARCHAR(MAX));
        INSERT INTO #temp
            EXEC [dbo].[SearchAllTables] @valueSearchTerm = 'john';
        SELECT * FROM #temp ORDER BY Result ASC;
        DROP TABLE #temp;
*/
0

More articles:


All Articles