Internal framework stored procedure used to search across objects definitions to provided search predicate. Can be used to enforce code conventions or search for to bad practices like "select * from table1". 

At this point this procedure under review and we anticipate signature change in the future versions. 

Note:
This is internal DBTD Framework procedure;
we might change business logic and signature in future releases, for that reason do not use it in your tests.

 

Arguments

  • v_TargetDB - Target Database where search will be perfromed. Parameter type SYSNAME.
  • v_SearchString - The Search String that will be looked for. Parameter type NVARCHAR(MAX).
  • v_Exclude - Comma separated list of excluded criteria. Parameter type NVARCHAR(MAX). 
  • v_CleanOldDefinitionSearchResult - Defalt value is 1. set it to 0 if you would like to preserve result that already stored in the #DefinitionSearchResult. Parameter type BIT
  • v_SearchResult - OUTPUT parameter that contains search result. Parameter type NVARCHAR(MAX).
    Search can be also stored in the tabular format, for that user need to create temporary table #DefinitionSearchResult in the following format:
    CREATE TABLE #DefinitionSearchResult(
          [Object_ID] INT,
          ObjectName VARCHAR(500), --Full object name with database and schema prefix 
          [Type]  VARCHAR(50),
          Name  SYSNAME,  --Just an object name
          SearchString NVARCHAR(MAX)
    );

 

Returns

  • v_SearchResult - NULL if no results are found. Comma separated list of the database objects names where search string was found. 
  • #DefinitionSearchResult - table will be populated with search result values if any were found

Note:

  • this procedure can be relatively slow when target database have many objects
  • does not search through encrypted objects
  • supported in the SQL Server version of DB Test Driven framework

 

Examples

SQL Server

DECLARE @v_SearchResult NVARCHAR(MAX)


CREATE TABLE #DefinitionSearchResult(
    [Object_ID] INT,
    ObjectName VARCHAR(500), --Full object name with database and schema prefix 
    [Type]  VARCHAR(50),
    Name  SYSNAME, --Just an object name
    SearchString NVARCHAR(MAX)
);

EXEC DBTD_SEARCH_ObjectsDefinition
    @v_SearchString = '%select * from my product%',
    @v_TargetDB = 'MyDatabase',
    @v_Exclude = 'sp_Add%', --excludes all projects that add records
    @v_CleanOldDefinitionSearchResult = 1, --clean result table 
    @v_SearchResult = @v_SearchResult OUTPUT

SELECT @v_SearchResult AS [@v_SearchResult]

EXEC DBTD_SEARCH_ObjectsDefinition 

    @v_SearchString = '%select * from my v_productt%', 
    @v_TargetDB = 'MyDatabase', 
    @v_Exclude = 'sp_Add%', --excludes all projects that add records
    @v_CleanOldDefinitionSearchResult = 0, --and now do not clean prior search results 
    @v_SearchResult = @v_SearchResult OUTPUT

--show everything that were have found
SELECT * FROM  #DefinitionSearchResult

SELECT @v_SearchResult AS [@v_SearchResult]
 

See Also