Useful Commands - MSSQL

Here is a start of a regular posting, “useful commands” Over the years I have saved snippets, commands and notes of things that have been useful or saved alot of time. I start off with a gathering of T-SQL commands to do useful things within a windows database. Some of these relate directly to modifying a Kentico database (the CMS of choice where I work), but may be useful anyway

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- Turn off SSL

UPDATE [CMS_SettingsKey]  SET KeyValue = 'False'WHERE KeyName LIKE 'CMSUseSSL%'


-- Take Database offline forcefully and come back online

#Go offline
USE master
GO
ALTER DATABASE YourDatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
#Go online
USE master
GO
ALTER DATABASE YourDatabaseName
SET ONLINE
GO
# get out of recovery mode
RESTORE DATABASE <database name> WITH RECOVERY


-- Get MDF/BAK file SQL version


#Bak file (in sql)
RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\base106.bak'

#MDF (powershell)
get-content -Encoding Byte "...\foo.mdf" | select-object -skip 0x12064 -first 2 number 2 * 256 + number 1


-- Get DB Size


EXEC sp_spaceused
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

SELECT *
FROM   #t

SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t
DROP TABLE #t


-- Get RAM usage of server

SELECT db = DB_NAME(t.dbid), plan_cache_kb = SUM(size_in_bytes/1024) 
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
WHERE t.dbid < 32767
GROUP BY t.dbid
ORDER BY plan_cache_kb DESC;