▼TRY..CATCH 

출처&상세: TRY...CATCH(Transact-SQL)

-- TRY..CATCH 사용예
BEGIN TRY  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
        -- 프로시저 파라미터 사용 가능
 	DECLARE @ParamInfoStr varchar(1000)
	= '@ResultDate='+CASE WHEN @ResultDate IS NULL THEN 'null' ELSE CONVERT(varchar(19), @ResultDate, 120) END
		+', @ModelID='++CASE WHEN @ModelID IS NULL THEN 'null' ELSE CAST(@ModelID as varchar) END

	-- error retrieval .  
	SELECT  
		 ERROR_NUMBER() AS ErrorNumber  
		,ERROR_SEVERITY() AS ErrorSeverity  
		,ERROR_STATE() AS ErrorState  
		,ERROR_PROCEDURE() AS ErrorProcedure  
		,ERROR_LINE() AS ErrorLine  
		,ERROR_MESSAGE() AS ErrorMessage; 

	--출력
	PRINT
	   'ERROR_NUMBER = ' + CAST(ERROR_NUMBER() AS VARCHAR)
	 +'ERROR_SEVERITY = ' + CAST(ERROR_SEVERITY() AS VARCHAR)
	 +'ERROR_STATE = ' + CAST(ERROR_STATE() AS VARCHAR)
	 +'ERROR_PROCEDURE = ' + ERROR_PROCEDURE()
	 +'ERROR_LINE = ' + CAST(ERROR_LINE() AS VARCHAR)
	 +'ERROR_MESSAGE = ' + ERROR_MESSAGE();
END

포트상태 감시자 만드는 중 간단히 Listen포트정보와 Tcp연결포트 정보를 가져오는 API를 발견하여 정리해둔다.
단, IPv4정보만 출력된다.
netstat 명령어와 같이 모든 정보를 얻고 싶으면 아래 링크의 샘플코드를 참조할 것▼
C# Sample to list all the active TCP and UDP connections using Windows Form appl | 2016-01-20

using System.Net.NetworkInformation; //IPAddress, IPGlobalProperties
using System.Net;//IPEndPoint

static void Main(string[] args)
{
    _Print("IPAddress.Any = " + IPAddress.Any.ToString());
    _Print("IPAddress.None = " + IPAddress.None.ToString());
    _Print("IPAddress.Broadcast = " + IPAddress.Broadcast.ToString());
    _Print("IPAddress.Loopback = " + IPAddress.Loopback.ToString());

    _Print("IPAddress.IPv6Any = " + IPAddress.IPv6Any.ToString());
    _Print("IPAddress.IPv6Loopback = " + IPAddress.IPv6Loopback.ToString());
    _Print("IPAddress.IPv6None = " + IPAddress.IPv6None.ToString());

    Console.WriteLine("\r\npress any key to exit..");
    Console.ReadKey();

    PortList();

    Console.WriteLine("\r\npress any key to exit..");
    Console.ReadKey();
}
public static void PortList()
{
    IPGlobalProperties ipProperties = IPGlobalProperties.GetIPGlobalProperties();
    IPEndPoint[] ipEndPoints = ipProperties.GetActiveTcpListeners();
    _Print("▼ GetActiveTcpListeners ----------------");
    foreach (IPEndPoint endPoint in ipEndPoints)
    {
        _Print(string.Format("AddressFamily={0}, IP={1}, PORT={2}", endPoint.AddressFamily.ToString(), endPoint.Address.ToString(), endPoint.Port));
    }

    TcpConnectionInformation[] tcpConnInfoArray = ipProperties.GetActiveTcpConnections();

    _Print("▼ GetActiveTcpConnections ----------------");
    _Print("Local\t\t\t ┃Remote\t\t\t ┃State");
    foreach (TcpConnectionInformation tcpConnInfo in tcpConnInfoArray)
    {
        TcpState tcpState = tcpConnInfo.State;
        IPEndPoint localEndPoint = tcpConnInfo.LocalEndPoint;
        IPEndPoint remotrEndPoint = tcpConnInfo.RemoteEndPoint;

        _Print(string.Format("{0}:{1}\t ┃{2}:{3}\t ┃{4}",
            tcpConnInfo.LocalEndPoint.Address, tcpConnInfo.LocalEndPoint.Port,
            tcpConnInfo.RemoteEndPoint.Address, tcpConnInfo.RemoteEndPoint.Port,
            tcpConnInfo.State.ToString()));
    }
}

static public void _Print(string aMsg)
{
    Console.WriteLine(aMsg);
    System.Diagnostics.Debug.WriteLine(aMsg);
}

 

IPAddress.Any = 0.0.0.0
IPAddress.None = 255.255.255.255
IPAddress.Broadcast = 255.255.255.255
IPAddress.Loopback = 127.0.0.1
IPAddress.IPv6Any = ::
IPAddress.IPv6Loopback = ::1
IPAddress.IPv6None = ::

▼ GetActiveTcpListeners ----------------
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=80
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=135
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=623
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=1025
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=5357
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=10004
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=31234
AddressFamily=InterNetwork, IP=0.0.0.0, PORT=55920
AddressFamily=InterNetwork, IP=14.9.217.129, PORT=139
AddressFamily=InterNetwork, IP=127.0.0.1, PORT=1065
AddressFamily=InterNetwork, IP=127.0.0.1, PORT=16105
AddressFamily=InterNetwork, IP=127.0.0.1, PORT=55501

▼ GetActiveTcpConnections ----------------
Local		 	 ┃Remote				 ┃State
14.9.217.129:1045	 ┃12.4.11.221:9992	 	┃Established
14.9.217.129:1049	 ┃12.4.11.221:9991	 	┃Established
14.9.217.129:2029	 ┃64.233.188.188:5228	 ┃Established
14.9.217.129:23449	 ┃211.115.106.205:80	 ┃CloseWait
14.9.217.129:24345	 ┃104.17.129.217:443	 ┃Established
14.9.217.129:24675	 ┃121.156.118.102:80	 ┃CloseWait
127.0.0.1:24836	 ┃127.0.0.1:9229		 ┃SynSent
127.0.0.1:24837	 ┃127.0.0.1:9229		 ┃SynSent

해당포트의 Process ID값까지 필요하다면 - 예를 들어 kill process로 포트를 닫아야할 때 - 아래 샘플 소스 사용가능

.NET Framework 4.0 이상 / iphlpapi.dll 활용

C# Sample to list all the active TCP and UDP connections using Windows Form appl

예를 들어 스키마를 변경하려는 테이블을 사용하는 모든 스크립트-영향도-를 검색해야할 경우 사용한다.

-- SQL_EXP 스크립트 검색 (VIEW/FUNCTION/PROCEDURE/TRIGGER)
-- 유의사항: 현재 선택된 DB 만 검색
SELECT O.name, O.type_desc
FROM sys.sql_modules M
	JOIN sys.objects O ON M.object_id = O.object_id
	LEFT OUTER JOIN sys.all_objects P ON O.parent_object_id = P.object_id
WHERE 1=1
  AND M.definition LIKE '%_DEL_%'                       -- 검색결과 이상함!
  AND M.definition LIKE '%[_]DEL[_]%'		-- 밑줄(_) 검색방법1: []로 감싸기
  AND M.definition LIKE '%|_DEL|_%' ESCAPE '|'	-- 밑줄(_) 검색방법2: 이스케이프 문자 정의해서 사용
ORDER BY O.name

--2) 메타데이터 테이블 사용 (4000byte이상은 잘리므로 미사용.)
SELECT * 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%[_]DEL[_]%'

참고: http://sqldbpros.com/2013/01/escaping-from-an-underscore-in-a-sql-server-wildcard-like-search/

ALTER PROCEDURE dbo.INSERT_ACTION_HISTORY
--CREATE PROCEDURE dbo.INSERT_ACTION_HISTORY
        @ResultDate datetime,
	@LOGID bigint,
	@ActionCode int,
	@Description varchar(500) = NULL
AS
BEGIN
	SET NOCOUNT ON;
	BEGIN TRANSACTION;
	BEGIN TRY
		INSERT INTO TBL_SAMPLE (LOGID, ActionCode, Description, UpdateDate) 
		VALUES (@LOGID, @ActionCode, @Description, GETDATE());
	END TRY
    BEGIN CATCH
        -- 파라미터 정보도 저장 가능 (스트링 만들때 오류 안나도록 반드시 테스트 후에 적용할 것)
        DECLARE @ParamInfo VARCHAR(1000) = '';
	SET @ParamInfo = 
		  '입력파라미터: '
		+ '@ResultDate=[' + ISNULL(CONVERT(VARCHAR(19), @ResultDate, 120), 'null')
		+ '], @LOGID=['+CASE WHEN @LOGID IS NULL THEN 'null' ELSE CAST(@LOGID AS VARCHAR) END
		+ '], @ActionCode=['+CASE WHEN @ActionCode IS NULL THEN 'null' ELSE CAST(@ActionCode AS VARCHAR) END
		+ '], @Description=['+CASE WHEN @Description IS NULL THEN 'null' ELSE SUBSTRING(@Description, 0, 100) END
		+ ']';
		PRINT @ParamInfo;

	-- 이 부분에서 DB에러 이력 테이블에 저장하거나 출력
      SELECT ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage;
      IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;
   END CATCH	
   
   BEGIN
	IF @@TRANCOUNT > 0   
		COMMIT TRANSACTION;
   END

STUFF(): 문자열의 위치와 길이를 지정하여 다른 문자로 치환

STUFF (문자열, 위치, 길이, 치환할 문자)

FOR XML PATH: 쿼리의 결과 데이터를 XML 형태로 표현

FOR XML PATH ([row element명])

SELECT type
     , STUFF((SELECT ',' + name
                FROM t
               WHERE type = a.type
               ORDER BY code
                 FOR XML PATH('')
              ), 1, 1, '') name_MSSQL
  FROM t a
 GROUP BY type
 ORDER BY type

출처: http://hspmuse.tistory.com/entry/group-by에서-문자열-합치기 [개발자인생]

--컬럼명 나열 만들기(INSERT문 작성시 유용)
SELECT STUFF((SELECT ',' + COLUMN_NAME
                        FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = 'USERS' 
		 ORDER BY COLUMN_NAME
                 FOR XML PATH('')
              ), 1, 1, '') AS COLS

참고: https://blog.sonim1.com/107