useful sql server queries
list and kill running queries
-- To list running queriesSELECT sqltext.TEXT, req.session_id, req.status, req.command, req.cpu_time, req.total_elapsed_timeFROM sys.dm_exec_requests reqCROSS 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 UnusedSpaceKBFROM sys.tables tINNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN sys.allocation_units a ON p.partition_id = a.container_idLEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255GROUP BY t.Name, s.Name, p.RowsORDER 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
SETNOCOUNT ON DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5) SELECT @crdate=crdateFROM sysdatabasesWHERE 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.'