MSSQL (38) 썸네일형 리스트형 [python] sp 명으로 sp create 구문 가져오기 파이썬 3.x + mssql import pymssql def execute(query): conn = pymssql.connect(server='{서버IP}', user='{아이디}', password='{패스워드}', database='{db명}', charset='utf8') cursor = conn.cursor(as_dict=True) result_data = [] if conn: # print('{서버IP} connected') cursor.execute(query) index = 0 for row in cursor: result_data.insert(index, row) index = index + 1 else: # print('{서버IP} connect error') pass conn.clo.. [regex][mssql] INSERT SELECT, SELECT expression /(INSERT\s+(.*?)|)SELECT\s+(.*?)\s*FROM\s+(.*?)\s*(WITH|)\(NOLOCK\)\s(WHERE\s(.*)\s*)?/g matches SELECT D.CN_COA FROM TPE_04C D (NOLOCK) WHERE D.C_COA = A.C_COA SELECT D.CN_COA FROM TPE_04C D WITH(NOLOCK) WHERE D.C_COA = A.C_COA SELECT D.CN_COA FROM TPE_04C AS D WITH(NOLOCK) WHERE D.C_COA = A.C_COA SELECT D.CN_COA FROM TPE_04C AS D WITH(NOLOCK) WHERE D.C_COA = A.C_COA GROUP BY D.CN_CO.. [regex][mssql] INSERT | INSERT INTO expression /(INSERT+?(INTO|)\s+(.*?)|)SELECT\s+(.*?)\s*FROM\s+(.*?)\s*(WITH|)\(NOLOCK\)\s(WHERE\s(.*)\s*)?/g matches X - INSERT TPE_40C X - INSERT #TPE_40C O - INSERT TPE_40C SELECT C_COA FROM TPP_10C AS D WITH(NOLOCK) WHERE 1=1 AND C_COA = 1 GROUP BY C_COA O - INSERT INTO TPE_40C SELECT C_COA FROM TPP_10C AS D WITH(NOLOCK) WHERE 1=1 AND C_COA = 1 GROUP BY C_COA demo link https://regexr.com/44ndb [regex][mssql] CRATE TABLE expression /CREATE\s(TABLE)\s(#|@)+(.*?)\s\((.*)\)+(;|)/g matches O - CREATE TABLE #TPE_04C (C_COA CHAR(3)); O - CREATE TABLE #TFP_14D (C_UNT CHAR(3), C_COA CHAR(3), C_FINC CHAR(5), P_COL SMALLINT, A_PN MONEY) O - CREATE TABLE #TFP_14D1 (S_SORT INT, CN_COA VARCHAR(50), C_FINC CHAR(5), P_COL SMALLINT, A_PN MONEY) X - CREATE INDEX #XIE1TFP_14D1 ON #TFP_14D1 (C_FINC) demo link https://regexr.com/44.. [regex][mssql] CREATE INDEX expression /(CREATE)\s(INDEX)\s(#|@)+(.*?)\s(ON)\s(#|@)+(.*?)\((.*)\)+(;|)/g matches X - CREATE TABLE #TPE_04C (C_COA CHAR(3)) X - CREATE TABLE #TFP_14D (C_UNT CHAR(3), C_COA CHAR(3), C_FINC CHAR(5), P_COL SMALLINT, A_PN MONEY) X - CREATE TABLE #TFP_14D1 (S_SORT INT, CN_COA VARCHAR(50), C_FINC CHAR(5), P_COL SMALLINT, A_PN MONEY) O - CREATE INDEX #XIE1TFP_14D1 ON #TFP_14D1 (C_FINC) O - CREATE IN.. [mssql][winform][ado.net] SSMS 와 Application 에서 쿼리 실행 시간이 너무 많이 차이 날때 1.점검사항 - ado, ado.net 을 사용하는가? sql server 의 기본 설정은 SET ARITHABORT ON 이나 ADO, ADO.NET에서는 실행 구문의 함수에 따라 재설정이 되기도 함. SET ARITHABORT ON; {{실행쿼리}} - ssms 에서 느린가? 조인 조건 및 where 절 오류일 가능성이 높음. 참조 https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6e5353b9-4837-4022-a535-5408ed6f852a/stored-procedure-running-slow-in-adonet?forum=sqldataaccess https://www.mssqltips.com/sqlservertip/4318/sql-server-.. [mssql] sp명으로 정의 가져오기 SELECT OBJECT_NAME(object_id) AS sp_name, OBJECT_DEFINITION(object_id) AS sp_context FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) like '%{0}%' [mssql] 특정 문자열 포함 sp 명 반환 SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%{{문자열}}}%' AND ROUTINE_TYPE = 'PROCEDURE' order by ROUTINE_NAME 이전 1 2 3 4 5 다음
