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.

result to grid

but when we view it in text mode, it does the break, as it should be.

result to text

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.'