Sql For Work

Front 1

see queries currently running on SQL Server

Back 1

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

Front 2

amount of connections to a sql server(w)

Back 2

select db_name(dbid) as db, count(dbid) as connection from sys.sysprocesses where dbid > 0 group by dbid

Front 3

kill a session

Back 3

KILL [session_id]

Front 4

kill all connections to a db

Back 4

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'navigator1'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'

FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--SELECT @SQL
EXEC(@SQL)

Front 5

see compatibility level of a db

Back 5

select compatibility_level, name from sys.databases

Front 6

list compatibility level (cmd)

Back 6

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008

Front 7

set compatibility level (cmd)

Back 7

1)ALTER DATABASE database_name SET SINGLE_USER;
2)ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
3)ALTER DATABASE database_name SET MULTI_USER;

Front 8

backup a database TSQL

Back 8

DECLARE @DB AS VARCHAR(200), @PATH AS VARCHAR(400);
SET @DB = N'navigator1';
SET @PATH = N'D:\MSSQL\BACKUP\'+ @DB + N'-09-30-2011.bak'

BACKUP DATABASE @DB TO
DISK = @PATH

Front 9

To confirm the Database Mail activation is started, execute the following statement

Back 9

EXEC msdb.dbo.sysmail_help_status_sp;

Front 10

3.To view the error messages returned by Database Mail, execute the following statement

Back 10

SELECT * FROM msdb.dbo.sysmail_event_log;

Front 11

add account to idocSendReceive

Back 11

DECLARE @WMSGuid VARCHAR(80),
@URL VARCHAR(80),
@TODAY DATE,
@WHEN DATE;
SET @WMSGuid = '120836fc-05a8-4659-b093-cf2a6a386feb';
SET @URL = 'http://www.nscnavigator.com/' + 'ultera_nav';
SET @TODAY = GETDATE();
SET @WHEN = DATEADD(YEAR, 16, @TODAY);



INSERT INTO dbo.idocAccount VALUES (0, @WMSGuid, 51, 7, 7, @URL, @TODAY, @WHEN, 1)

Front 12

last query ran

Back 12

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Front 13

last querry ran more detailed

Back 13

SELECT TOP 50 * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,
execution_count,s2.objectid,
(SELECT TOP 1 SUBSTRING(s2.TEXT,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(NVARCHAR(MAX),s2.TEXT)) * 2)
ELSE statement_end_offset END)- statement_start_offset) / 2+1)) AS sql_statement,
last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement NOT like 'SELECT TOP 50 * FROM(SELECT %'
--and OBJECTPROPERTYEX(x.objectid,'IsProcedure') = 1
ORDER BY last_execution_time DESC

Front 14

get a list of all user tables in a DB

Back 14

SELECT * FROM sysobjects WHERE type = 'U'

Front 15

get a list of all DB in sql

Back 15

EXEC sp_databases
EXEC sp_helpdb

Front 16

query to backup all DB in SQL

Back 16

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Front 17

list of all user tables

Back 17

SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

Front 18

generate sha1

Back 18

declare @hash varchar(100)
SELECT @hash = '2011!V0dka'
SELECT HASHBYTES('SHA1', @hash)

Front 19

concat fields in query result

Back 19

SELECT FirstNAme + ' ' + LastName, EmailAddress as FullName FROM SalesLT.Customer

Front 20

generate an MD5 hash

Back 20

declare @hash varchar(100)
SELECT @hash = '2011!V0dka'
SELECT HASHBYTES('MD5', @hash)

Front 21

backup all db in an instance

Back 21

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'd:\MSSQL\backup\2012-03-06\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Front 22

extract date from date and time

Back 22

declare @date datetime
set @date=CONVERT(varchar(8), GETDATE(), 112)
SELECT @date

Front 23

Look up a value in all tables

Back 23

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL

BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

Front 24

3 line kill all connections to a DB

Back 24

DECLARE @SQL VARCHAR(8000)

SELECT @SQL=COALESCE(@SQL,'')+'Kill '+CAST(spid AS VARCHAR(10))+ '; '
FROM sys.sysprocesses
WHERE DBID=DB_ID('catglobal_test')

EXEC(@SQL)

Front 25

kill all connections to a DB

Back 25

-- Create the sql to kill the active database connections
declare @execSql varchar(1000), @databaseName varchar(100)
-- Set the database name for which to kill the connections
set @databaseName = 'myDatabase'

set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql)

Front 26

kill all connections to a DB
using alter database

Back 26

alter database dbName set single_user with rollback immediate

alter database dbName set multi_user with rollback immediate

Front 27

list each table in a DB with its particular size

Back 27

SELECT
Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
, 8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb
, Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb
, so.name
FROM dbo.sysobjects AS so
JOIN dbo.sysindexes AS si
ON (si.id = so.id)
WHERE 'U' = so.type
GROUP BY so.name
ORDER BY so.name

Front 28

find all columns with a particular name in a DB

Back 28

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

Front 29

find a value in all tables

Back 29

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'Corrective Action Report'
-- Purpose: To search all columns of all tables for a given search string
-- Updated and tested by Tim Gaunt
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL

BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

Front 30

move tempdb

Back 30

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
GO

Front 31

size of each table in a DB

Back 31

SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
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
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name

Front 32

disable alerts on a site after it has being copied

Back 32

--delete emails from accounts
UPDATE tbl_WMSAccounts SET Email='' WHERE Username!='isupport'
--delete values from all 'email' information fields
DELETE v FROM tbl_WMSCustomFieldValues v JOIN tbl_WMSCustomFields f ON v.CustomFieldID=f.ID AND f.DataType=18
--disable alerts
UPDATE tbl_SmartAlerts SET enabled=0
UPDATE tbl_SmartViewAlerts SET Active=0
--remove automatic run of custom queries
DELETE FROM tbl_Schedules WHERE ScheduleType=0

Front 33

Update MS Reports pat

Update MS reports templates path

(use for when copying sites)

Back 33

UPDATE tbl_MSReports SET Path = REPLACE (Path, '#old path#', '#new path#')
UPDATE tbl_DocType SET MsReport = REPLACE (MsReport, '/vion2/', '/vion2_test/') WHERE MsReport IS NOT NULL

Front 34

delete all records, not binders or modules, just the records so it is clean in a site called highreach

Back 34

delete tbl_CustomObjRecords

Front 35

clear all emails in IDM1

Back 35

--delete emails from accounts
UPDATE tbl_WMSAccounts SET Email='' WHERE Username!='isupport'
update tbl_WMSWorkers set Email=''
update tbl_WMSContacts set Email=''

--delete values from all 'email' information fields
DELETE v FROM tbl_WMSCustomFieldValues v JOIN tbl_WMSCustomFields f ON v.CustomFieldID=f.ID AND f.DataType=18
--disable alerts
UPDATE tbl_SmartAlerts SET enabled=0
UPDATE tbl_SmartViewAlerts SET Active=0
--remove automatic run of custom queries
DELETE FROM tbl_Schedules WHERE ScheduleType=0
-----------

Front 36

top 10 most poorly performing queries across all
databases

Back 36

SELECT TOP 10 execution_count as [Number of Executions],
total_worker_time/execution_count as [Average CPU Time],
Total_Elapsed_Time/execution_count as [Average Elapsed Time],
(
SELECT SUBSTRING(text,statement_start_offset/2,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max), [text])) * 2
ELSE statement_end_offset END - statement_start_offset) /2)
FROM sys.dm_exec_sql_text(sql_handle)
) as query_text
FROM sys.dm_exec_query_stats
ORDER BY [Average CPU Time] DESC;

Front 37

Finding frequency Counts with SQL

Back 37

SELECT tag, count(*) AS total FROM tags GROUP by tag