Sql Servera bağlı kullanıcılar

SELECT
sessi.session_id ,
sessi.login_time ,
sessi.last_request_start_time ,
sessi.last_request_end_time ,
sessi.host_name ,
sessi.program_name ,
sessi.login_name ,
sessi.status ,
cnnct.last_read ,
cnnct.last_write ,
sessit.DbAdi ,
sessi.nt_domain ,
sessi.nt_user_name ,
cnnct.client_net_address ,
cnnct.local_net_address ,
sessit.Query ,
'Kill ' + CAST(sessi.session_id AS VARCHAR) KullaniciAt
FROM sys.dm_exec_sessions AS sessi
INNER JOIN sys.dm_exec_connections AS cnnct
ON cnnct.session_id = sessi.session_id
CROSS APPLY
( SELECT
DB_NAME( dbid ) AS DbAdi ,
COALESCE(
( SELECT
text AS [processing-instruction(definition)]
FROM sys.dm_exec_sql_text( cnnct.most_recent_sql_handle )
FOR XML PATH( '' ), TYPE) ,
'' ) AS Query
FROM sys.dm_exec_sql_text( cnnct.most_recent_sql_handle ) ) sessit
WHERE sessi.session_id <> @@SPID ;

İki Tarih arası Muhasebe bakiye raporu

SELECT 
GLACC.CODE,GLACC.DEFINITION_ , 
ISNULL (FORMAT(SUM(GLTRN.DEBIT),'##,##0.00'),0) AS BORC,
ISNULL (FORMAT(SUM(GLTRN.CREDIT),'##,##0.00'),0) AS ALACAK,
--ISNULL (CONVERT(NUMERIC(15,2),SUM(GLTRN.CREDIT)),0) AS ALACAK , 
--SELECT CONVERT( numeric(10,2), 5634.6334) as number
--ISNULL(SUM(GLTRN.CREDIT),0) AS ALACAK, 
--ISNULL (SUM(GLTRN.DEBIT),0)-ISNULL (SUM(GLTRN.CREDIT),0) AS BAKIYE 

ISNULL (FORMAT((SUM (GLTRN.DEBIT)-SUM(GLTRN.CREDIT)),'##,##0.00'),0) AS BAKIYE 



 FROM 
LG_049_EMUHACC GLACC WITH(NOLOCK) LEFT OUTER JOIN LG_049_01_EMFLINE GLTRN WITH(NOLOCK) ON (GLACC.LOGICALREF  =  GLTRN.ACCOUNTREF)
 WHERE 
(
(GLACC.CODE LIKE '120.10%')
OR (GLACC.CODE LIKE '120.11%')
OR (GLACC.CODE LIKE '120.20%')
OR (GLACC.CODE LIKE '120.30%')
OR (GLACC.CODE LIKE '120.40%')
OR (GLACC.CODE LIKE '120.41%')
OR (GLACC.CODE LIKE '120.42%')
OR (GLACC.CODE LIKE '320%' ) 
OR (GLACC.CODE LIKE '329%' ) ) 
AND ((GLTRN.DATE_ >= CONVERT(dateTime, '1-1-2023', 101)) AND (GLTRN.DATE_ <= CONVERT(dateTime, '12-31-2023', 101))) 
AND (GLTRN.TRCODE IN (1,2,3,4,6,7)) 
AND (GLTRN.BRANCH IN (0)) 
AND (GLTRN.CANCELLED = 0) 
AND (GLTRN.STATUS = 0) 
AND (GLTRN.DEPARTMENT IN (0))


GROUP BY  GLACC.CODE,GLACC.DEFINITION_ ORDER BY  GLACC.CODE

Tablolarda ki Kayıt Sayısı ve Tablo Boyutları

Aşağıdaki kod yardımıyla seçilen databese içinde yer alan tablolarda ne kadar kayıt olduğunu ve tablo boyutlarını görebilirsiniz.

Sorgu : tablolarda 1000 kayıt ve üstü veri olanları getirir.

SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
–t.NAME LIKE ‘LG_000_SYSLOG’ AND
t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
HAVING p.Rows>1000
ORDER BY p.rows

sorgu çıktısı aşağıdakine benzer olacaktır.