AipGateway/Database/MS SQL Server Query.SQL

SELECT login_name, COUNT(session_id) AS session_Count
FROM sys.dm_exec_sessions
GROUP BY login_name;

SELECT A.spid, A.login_time, A.loginame, A.last_batch, A.status, A.program_name, A.cmd, B.client_net_address
FROM sys.sysprocesses A?JOIN sys.dm_exec_connections B ON A.spid = B.session_id
where a.loginame = 'aipuser';


/* 현재 Connection Pool 수 */
SELECT
    DB_NAME(dbid) AS 'Database',
    COUNT(dbid) AS 'Connection Pool Count'
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid;


/* 전체 Connection Pool 수 */
SELECT
    COUNT(dbid) AS 'Total Connection Pool Count'
FROM
    sys.sysprocesses
WHERE
    dbid > 0;

/* 최대 Connection Pool 수 */
SELECT
    * 
FROM
    sys.configurations
WHERE
    name = 'user connections';


/* 현재 연결된 세션 정보 */
SELECT
    S.SPID AS '세션 ID', S.LOGINAME AS '로그인 이름', S.LOGIN_TIME AS '로그인 시간', S.LAST_BATCH AS '마지막 일괄 처리 시간', C.CLIENT_NET_ADDRESS AS '클라이언트 IP 주소'
FROM
    sys.sysprocesses S, sys.dm_exec_connections C
WHERE
    S.spid = C.SESSION_ID
ORDER BY
    S.LOGIN_TIME DESC;


SELECT DB_NAME(dbid) as DB이름, COUNT(dbid) as 접속자수 FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid;

SELECT S.spid, S.loginame, S.login_time, S.last_batch, C.client_net_address FROM sys.sysprocesses S, sys.dm_exec_connections C WHERE S.spid = C.session_id;




SELECT @@identity;
DBCC checkident(tb_aip_label, reseed, 0);

-- 증가값 자동 지정 (기본 설정)
SET IDENTITY INSERT tb_aip_label OFF; -- test : 테이블명

-- 증가값 수동 지정
SET IDENTITY INSERT tb_aip_label ON;

-- 시작값 재지정
DBCC CHECKIDENT (tb_aip_label, RESEED, 0) -- 0 : 시작값


-- 현재 세션의 테이블에서 생성된 마지막 ID 값 반환 (제한 x)
SELECT @@IDENTITY

-- 현재 세션의 테이블에서 생성된 마지막 ID 값 반환 (현재 범위만)
SELECT SCOPE_IDENTITY()

-- 지정된 테이블에서 생성된 마지막 ID 값 반환
SELECT IDENT_CURRENT(tb_aip_label) -- 테이블명(test)


SELECT message_id AS Error,
    severity AS Severity,
    [Event Logged] = CASE is_event_logged
        WHEN 0 THEN 'No' ELSE 'Yes'
        END,
    [text] AS [Description]
FROM sys.messages
WHERE language_id = 1040 /* replace 1040 with the desired language ID, such as 1033 for US English */
ORDER BY message_id;