useful sql server queries
list and kill running queries
-- To list running queries
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
AS sqltext
-- To kill a query, use the session_id.
KILL [session_id]
reference: pinal dave (http://blog.SQLAuthority.com)
list size and record count of all tables
SELECT
t.NAME AS Entity,
p.rows AS Records,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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 NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
Records DESC
reference: stackoverflow
add line break
SELECT 'First Line' + CHAR(13)+CHAR(10) + 'Second Line';
CHAR(13)
is the same as CR
. To follow the CRLF
line break on Windows,
you need CHAR(13)+CHAR(10)
.
what if i'm not seeing the line break?
when executing the queries in management studio, we have two ways to visualize the results: in grid mode, and in text mode.
when viewing in grid mode, management studio does not show the line break.
but when we view it in text mode, it does the break, as it should be.
but what about my app?
if you use this tip in an application developed by you, you will need to handle
the query result, and display the line break characters as as you wish. for
example, if you are developing a web app, you can, in visualization layer,
replace line breaks with <br/>
.
server uptime
SET
NOCOUNT
ON DECLARE @crdate DATETIME,
@hr VARCHAR(50),
@min VARCHAR(5) SELECT
@crdate=crdate
FROM
sysdatabases
WHERE
NAME='tempdb' SELECT
@hr=(DATEDIFF ( mi,
@crdate,
GETDATE()))/60 IF ((DATEDIFF ( mi,
@crdate,
GETDATE()))/60)=0 SELECT
@min=(DATEDIFF ( mi,
@crdate,
GETDATE()))
ELSE SELECT
@min=(DATEDIFF ( mi,
@crdate,
GETDATE()))-((DATEDIFF( mi,
@crdate,
GETDATE()))/60)*60 PRINT 'The server "' + CONVERT(VARCHAR(20),
SERVERPROPERTY('SERVERNAME'))+'" is up for '+@hr+' hours and '+@min+' minutes.'