Meta Data search in SQL Server - Closer Lookup to Data Dictionaries

Following are SQL script which can search metadata codes in MS-SQL Server Database

Please ensure db connectivity before running the scripts and these scripts will change the results as per the connected user privileges.

<> – Need to replace with search keyword identifier wherever SQL code is incorporated.

Table search

SELECT table_name,schema_name,table_type from (
SELECT name AS table_name ,SCHEMA_NAME(schema_id) AS schema_name, type_desc table_type
FROM sys.objects
WHERE type_desc LIKE '%TABLE%') all_tables
Where all_tables.table_name like '%'+upper('id')+'%';

Column or table structure Search

SELECT table_name,column_name,schema_name,type_name,max_length, precision,scale from (
SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS schema_name
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
    ,o.name table_name
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
JOIN sys.objects AS o on c.object_id = o.object_id
WHERE o.type_desc LIKE '%TABLE%') all_tab_columns
WHERE all_tab_columns.column_name like '%'+upper('id')+'%';

Constraint Search:
SELECT constraint_name,schema_name,constraint_type from (
SELECT name AS constraint_name ,SCHEMA_NAME(schema_id) AS schema_name, type_desc Constraint_type
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT%') all_constraints
Where all_constraints.constraint_name like '%'+upper('Search_Keyword')+'%';
Stored Function search:

Keyword occurrence count in function body

SELECT all_functions.object_id,all_functions.function_name,all_functions.schema_name,
all_functions.function_type,
(LEN(upper(f_source.definition)) - LEN(REPLACE(upper(f_source.definition), upper('Search_Keyword'), ''))) / LEN('Search_Keyword') no_of_occurr
from (SELECT object_id,name AS function_name ,SCHEMA_NAME(schema_id) AS schema_name,
type_desc function_type
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%') all_functions,sys.sql_modules f_source
Where (all_functions.function_name like '%'+upper('Search_Keyword')+'%'
or
exists (Select 'X'
from sys.sql_modules sm where all_functions.object_id=sm.object_id
and sm.definition like '%'+upper('Search_Keyword')+'%'))
and all_functions.object_id=f_source.object_id;

List of functions where keyword found

SELECT object_id,function_name,schema_name,function_type from (
SELECT object_id,name AS function_name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc function_type
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%') all_functions
Where all_functions.function_name like '%'+upper('search_keyword')+'%'
or
exists (Select 'X'
from sys.sql_modules sm where all_functions.object_id=sm.object_id
and sm.definition like '%'+upper('search_keyword')+'%');
 
Index Search:

SELECT i.name AS index_name
    ,i.type_desc
    ,is_unique
    ,is_primary_key
    ,is_unique_constraint
  FROM sys.indexes AS i
WHERE i.name like '%'+upper('search_keyword')+'%';


Package search:
Not supported by SQL Server


Stored Procedure search:

Keyword occurrence count in procedure body

SELECT all_procedures.object_id,all_procedures.procedure_name,all_procedures.schema_name,
all_procedures.procedure_type,
(LEN(upper(p_source.definition)) - LEN(REPLACE(upper(p_source.definition), upper('Search_Keyword'), ''))) / LEN('Search_Keyword') no_of_occurr
from (SELECT object_id,name AS procedure_name ,SCHEMA_NAME(schema_id) AS schema_name,
type_desc procedure_type
FROM sys.objects
WHERE type_desc LIKE '%PROCEDURE%') all_procedures,sys.sql_modules p_source
Where (all_procedures.procedure_name like '%'+upper('Search_Keyword')+'%'
or
exists (Select 'X'
from sys.sql_modules sm where all_procedures.object_id=sm.object_id
and sm.definition like '%'+upper('Search_Keyword')+'%'))
and all_procedures.object_id=p_source.object_id;

List of procedures where keyword found



SELECT object_id,procedure_name,schema_name,procedure_type from (
SELECT object_id,name AS procedure_name ,
SCHEMA_NAME(schema_id) AS schema_name, type_desc procedure_type
FROM sys.objects
WHERE type_desc LIKE '%PROCEDURE%') all_procedures
Where all_procedures.procedure_name like '%'+upper('search_keyword')+'%'
or
exists (Select 'X'
from sys.sql_modules sm where all_procedures.object_id=sm.object_id
and sm.definition like '%'+upper('search_keyword')+'%');

Sequence Search:
Not supported by SQL Server instead of that it is part of Columns search.

User defined Data Type search:

SELECT name AS data_type_name ,SCHEMA_NAME(schema_id) AS schema_name
FROM sys.types
WHERE name like '%'+upper('search_keyword')+'%';

Synonym Search

SELECT synonym_name,schema_name, synonym_type from (
SELECT name AS synonym_name ,
SCHEMA_NAME(schema_id) AS schema_name,type_desc synonym_type
FROM sys.objects
WHERE type_desc LIKE '%SYNONYM%') all_synonyms
Where all_synonyms.synonym_name like '%'+upper('search_keyword')+'%';





Trigger search

Keyword occurrence count in Trigger Body

SELECT all_triggers.object_id,all_triggers.trigger_name,all_triggers.schema_name,
all_triggers.trigger_type,
(LEN(upper(t_source.definition)) - LEN(REPLACE(upper(t_source.definition), upper('Search_Keyword'), ''))) / LEN('Search_Keyword') no_of_occurr
from (SELECT object_id,name AS trigger_name ,SCHEMA_NAME(schema_id) AS schema_name,
type_desc trigger_type
FROM sys.objects
WHERE type_desc LIKE '%TRIGGER%') all_triggers,sys.sql_modules t_source
Where (all_triggers.trigger_name like '%'+upper('Search_Keyword')+'%'
or
exists (Select 'X'
from sys.sql_modules sm where all_triggers.object_id=sm.object_id
and sm.definition like '%'+upper('Search_Keyword')+'%'))
and all_triggers.object_id=t_source.object_id;

List of triggers where keyword found


SELECT object_id, trigger_name,schema_name, trigger_type from (
SELECT object_id,name AS trigger_name ,
SCHEMA_NAME(schema_id) AS schema_name,type_desc trigger_type
FROM sys.objects
WHERE type_desc LIKE '%TRIGGER%') all_triggers
Where all_triggers.trigger_name like '%'+upper('search_keyword')+'%'
or
exists (Select 'X'
from sys.sql_modules sm where all_triggers.object_id=sm.object_id
and sm.definition like '%'+upper('search_keyword')+'%')
or
exists (Select 'X'
from sys.server_sql_modules ssm where all_triggers.object_id=ssm.object_id
and ssm.definition like '%'+upper('search_keyword')+'%');

MView Search:
Not supported by SQL Server instead of that it is part of View Search

View Search

Keyword occurrence count in view

SELECT all_views.object_id,all_views.view_name,all_views.schema_name,
all_views.view_type,
(LEN(upper(v_source.definition)) - LEN(REPLACE(upper(v_source.definition), upper(' search_keyword '), ''))) / LEN('search_keyword') no_of_occurr
from (SELECT object_id,name AS view_name ,SCHEMA_NAME(schema_id) AS schema_name,
type_desc view_type
FROM sys.objects
WHERE type_desc LIKE '%VIEW%') all_views,sys.sql_modules v_source
Where (all_views.view_name like '%'+upper('search_keyword')+'%'
or
exists (Select 'X'
from sys.sql_modules sm where all_views.object_id=sm.object_id
and sm.definition like '%'+upper('search_keyword')+'%'))
and all_views.object_id=v_source.object_id


List of views where keyword found

SELECT object_id, view_name,schema_name, view_type from (
SELECT object_id,name AS view_name ,
SCHEMA_NAME(schema_id) AS schema_name,type_desc view_type
FROM sys.objects
WHERE type_desc LIKE '%VIEW%') all_views
Where all_views.view_name like '%'+upper('search_keyword')+'%'
or
exists (Select 'X'
from sys.sql_modules sm where all_views.object_id=sm.object_id
and sm.definition like '%'+upper('search_keyword')+'%');

Count the string pattern in search

While developing a search engine, I had requirement of creating a function which can count the string occurrence. This function is written in T-SQL.

Here's a user-defined function that returns the number of times a given string exists on another string.  The first parameter is the string to be searched while the second parameter is the string to be searched for.
CREATE FUNCTION [dbo].[ufn_CountString]
( @pInput VARCHAR(8000), @pSearchString VARCHAR(100) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) -
LEN(REPLACE(@pInput, @pSearchString, ''))) /
LEN(@pSearchString)

END
GODescription
Counting the number of times a string exists in another string is quite easy, as can be seen from the simple code of the user-defined function.  The process can be summarized into the following steps:

1. REPLACE(@pInput, @pSearchString, '') - First step involves removing the string we are looking for from the input string by replacing it with an empty string.
2. LEN(@pInput) - LEN() - After replacing the search string with an empty string, we now get the difference between the length of the original string and the new string with the replaced value.
3. / LEN(@pSearchString) - The final step, which becomes the return value of the user-defined function, is to divide the result of the previous step with the length of the search string.  This will give use the number of times the search string exists within the other string.