<p><br></p><p><a href="http://motolies.tistory.com/642" target="_blank">2017/10/10 - [프로그램 자료/MS-SQL] - [MSSQL] 테이블, 뷰, 프로시저 등의 텍스트 내용 검색 - 'sys.objects', sp_schobject</a></p><p><br></p><p><br></p><p><br></p><p>DB가 점점 복잡해져가면서 안에 내용을 검색해야 할 일이 늘었다.</p><p><span style="font-size: 1rem;"><br></span></p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div>#&nbsp;프로시저&nbsp;내용&nbsp;검색</div><div><span style="color: #569cd6;">SELECT</span>&nbsp;*</div><div><span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.ROUTINES</div><div><span style="color: #569cd6;">WHERE</span>&nbsp;ROUTINE_SCHEMA=<span style="color: #ce9178;">'DBNAME'</span></div><div><span style="color: #569cd6;">AND</span>&nbsp;ROUTINE_TYPE=<span style="color: #ce9178;">'PROCEDURE'</span></div><div><span style="color: #569cd6;">AND</span>&nbsp;ROUTINE_DEFINITION&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #ce9178;">'%검색어%'</span>;</div><br><div>#&nbsp;함수&nbsp;검색</div><div><span style="color: #569cd6;">SELECT</span>&nbsp;*</div><div><span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.ROUTINES</div><div><span style="color: #569cd6;">WHERE</span>&nbsp;ROUTINE_SCHEMA=<span style="color: #ce9178;">'DBNAME'</span></div><div><span style="color: #569cd6;">AND</span>&nbsp;ROUTINE_TYPE=<span style="color: #ce9178;">'FUNCTION'</span></div><div><span style="color: #569cd6;">AND</span>&nbsp;ROUTINE_DEFINITION&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #ce9178;">'%검색어%'</span>;</div><br><div>#&nbsp;칼럼명&nbsp;검색</div><div><span style="color: #569cd6;">SELECT</span>&nbsp;*</div><div><span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.COLUMNS</div><div><span style="color: #569cd6;">WHERE</span>&nbsp;TABLE_SCHEMA&nbsp;=&nbsp;<span style="color: #ce9178;">'DBNAME'</span></div><div><span style="color: #569cd6;">AND</span>&nbsp;COLUMN_NAME&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #ce9178;">'%검색어%'</span>;</div><br><div>#&nbsp;트리거&nbsp;조회</div><div><span style="color: #569cd6;">SELECT</span>&nbsp;*</div><div><span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.TRIGGERS</div><div><span style="color: #569cd6;">WHERE</span>&nbsp;TRIGGER_SCHEMA&nbsp;=&nbsp;<span style="color: #ce9178;">'DBNAME'</span></div><div><span style="color: #569cd6;">AND</span>&nbsp;ACTION_STATEMENT&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #ce9178;">'%검색어%'</span>;</div><br><div>#&nbsp;뷰&nbsp;조회</div><div><span style="color: #569cd6;">SELECT</span>&nbsp;*</div><div><span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.VIEWS</div><div><span style="color: #569cd6;">WHERE</span>&nbsp;TABLE_SCHEMA&nbsp;=&nbsp;<span style="color: #ce9178;">'DBNAME'</span></div><div><span style="color: #569cd6;">AND</span>&nbsp;VIEW_DEFINITION&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #ce9178;">'%검색어%'</span>;</div></div><p><span style="font-size: 1rem;"><br></span></p><p><span style="font-size: 1rem;"><br></span></p><p><span style="font-size: 1rem;">다음과 같이 프로시저를 만들어 호출해도 된다.</span></p><p><br></p><p><br></p><div style="color: rgb(212, 212, 212); background-color: rgb(30, 30, 30); font-family: Consolas, &quot;Courier New&quot;, monospace; font-size: 14px; line-height: 19px; white-space: pre;"><div>DELIMITER&nbsp;$$</div><div><span style="color: #569cd6;">DROP</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;<span style="color: #569cd6;">IF</span>&nbsp;<span style="color: #569cd6;">EXISTS</span>&nbsp;<span style="color: #ce9178;">`dsp_search`</span>$$</div><br><div><span style="color: #569cd6;">CREATE</span>&nbsp;<span style="color: #569cd6;">PROCEDURE</span>&nbsp;<span style="color: #ce9178;">`dsp_search`</span>(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">IN</span>&nbsp;<span style="color: #ce9178;">`iCondition`</span>&nbsp;<span style="color: #569cd6;">VARCHAR</span>(<span style="color: #b5cea8;">128</span>)</div><div>)</div><div>&nbsp;&nbsp;&nbsp;&nbsp;COMMENT&nbsp;<span style="color: #ce9178;">'쿼리&nbsp;검색&nbsp;(삭제금지)'</span></div><div><span style="color: #569cd6;">BEGIN</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;*</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;(</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;ROUTINE_TYPE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">TYPE</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;ROUTINE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">NAME</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;<span style="color: #569cd6;">NULL</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;REF</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;ROUTINE_DEFINITION&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">DEFINITION</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.ROUTINES</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;(ROUTINE_DEFINITION&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'%'</span>,&nbsp;iCondition,&nbsp;<span style="color: #ce9178;">'%'</span>)&nbsp;<span style="color: #569cd6;">OR</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ROUTINE_NAME&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'%'</span>,&nbsp;iCondition,&nbsp;<span style="color: #ce9178;">'%'</span>))</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AND</span>&nbsp;ROUTINE_SCHEMA&nbsp;=&nbsp;<span style="color: #569cd6;">DATABASE</span>()</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">UNION&nbsp;ALL</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;<span style="color: #ce9178;">'TRIGGER'</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">TYPE</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;TRIGGER_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">NAME</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;EVENT_OBJECT_TABLE&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;REF</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;ACTION_STATEMENT&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">DEFINITION</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.TRIGGERS</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;(ACTION_STATEMENT&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'%'</span>,&nbsp;iCondition,&nbsp;<span style="color: #ce9178;">'%'</span>)&nbsp;<span style="color: #569cd6;">OR</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;EVENT_OBJECT_TABLE&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'%'</span>,&nbsp;iCondition,&nbsp;<span style="color: #ce9178;">'%'</span>))</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AND</span>&nbsp;TRIGGER_SCHEMA&nbsp;=&nbsp;<span style="color: #569cd6;">DATABASE</span>()</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">UNION&nbsp;ALL</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;<span style="color: #ce9178;">'VIEW'</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">TYPE</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;TABLE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">NAME</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;<span style="color: #569cd6;">NULL</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;REF</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;VIEW_DEFINITION&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">DEFINITION</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.VIEWS</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;(VIEW_DEFINITION&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'%'</span>,&nbsp;iCondition,&nbsp;<span style="color: #ce9178;">'%'</span>)&nbsp;<span style="color: #569cd6;">OR</span>&nbsp;TABLE_NAME&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'%'</span>,&nbsp;iCondition,&nbsp;<span style="color: #ce9178;">'%'</span>))</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AND</span>&nbsp;TABLE_SCHEMA&nbsp;=&nbsp;<span style="color: #569cd6;">DATABASE</span>()</div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">UNION&nbsp;ALL</span></div><br><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">SELECT</span>&nbsp;<span style="color: #ce9178;">'COLUMN'</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">TYPE</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;COLUMN_NAME&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">NAME</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;TABLE_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;REF</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;,&nbsp;COLUMN_COMMENT&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;<span style="color: #569cd6;">DEFINITION</span></div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">FROM</span>&nbsp;INFORMATION_SCHEMA.COLUMNS</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">WHERE</span>&nbsp;(COLUMN_NAME&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'%'</span>,&nbsp;iCondition,&nbsp;<span style="color: #ce9178;">'%'</span>)&nbsp;<span style="color: #569cd6;">OR</span>&nbsp;TABLE_NAME&nbsp;<span style="color: #569cd6;">LIKE</span>&nbsp;<span style="color: #dcdcaa;">CONCAT</span>(<span style="color: #ce9178;">'%'</span>,&nbsp;iCondition,&nbsp;<span style="color: #ce9178;">'%'</span>))</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">AND</span>&nbsp;TABLE_SCHEMA&nbsp;=&nbsp;<span style="color: #569cd6;">DATABASE</span>()</div><div>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;<span style="color: #569cd6;">AS</span>&nbsp;a</div><div>&nbsp;&nbsp;&nbsp;&nbsp;<span style="color: #569cd6;">ORDER&nbsp;BY</span>&nbsp;<span style="color: #569cd6;">TYPE</span>,&nbsp;<span style="color: #569cd6;">NAME</span>,&nbsp;<span style="color: #569cd6;">DEFINITION</span>;</div><br><div><span style="color: #569cd6;">END</span>$$</div><div>DELIMITER&nbsp;;</div><br><br></div><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p><p><br></p>


[MySQL & MariaDB] 칼럼명 프로시저 함수 트리거 뷰 조회