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