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')+'%');