activity monitor in SSMS – see also database processes, user processes, sp_who2, sp_whoisactive kill a process
agent, SQL Server Agent - permission to see
use
msdb
EXECUTE
sp_addrolemember
@rolename
=
'SQLAgentReaderRole',
@membername
= 'domain\user'
(need to use msdb)
ALTER DATABASE failed because a lock could not be placed on database -
You might get this error if you run a command to take a database offline.
Need to find out who's preventing you from getting a lock. Run:
EXEC
sp_who2
or sp_whoisactive
to find out who's clogging up your database
followed by kill <SPID>
to actually kill the offender
append records
insert into
DestinationTable
select OrigID
,
'xx'
as
Source,
CompanyName,
LastName,
FirstName,
Address
,
City,
ST,
ZipCode,
Phone,
Fax, email
from SourceTable
or, if you only want to specify some of the fields, leaving the others to null or whatever their default is:
insert intoCustomer
(Address,
Apartment,
CITY,
STATE,
ZIP_CODE,
ZIP_Plus4, REFERRAL)
SELECT distinct Address,
Apartment,
CITY,
STATE,
ZIP_CODE,
ZIP_Plus4,
'xx'
as REFERRAL
from Source
array, loop through see – loop through array
declare @databaseName
VARCHAR(64)
declare @pathBackup
VARCHAR(100)
declare @pathWithName
VARCHAR(120)
declare @Name
VARCHAR(64)
SELECT @databaseName
=
'starfleet'
SELECT @pathBackup
=
'R:\MSSQL11.MSSQLSERVER\MSSQL\Backup\'
+ @databaseName
+
'\'
SELECT @pathWithName
=
@pathBackup +
@databaseName +
'1'
+
'.bak'
SELECT @Name
=
'Full Backup of '
+ @databaseName
BACKUP
DATABASE @databaseName
TO
DISK
= @pathWithName
WITH
FORMAT,
MEDIANAME
=
@databaseName,
NAME
= @Name;
GO
backup directory, default - C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup
backup script
SQL Server Backup script of SQL Server Maintenance Solution
backup, schedule (2008) – here Login to Sql Management studio and connect to the required database. Expand the Management Node from the object explorer, and then select the maintenance plan node. Right click the maintenance plan and then select “new maintenance plan” or the wizard. I could only ever use the wizard; nothing happened when I simply tried a new maintenance plan. Anyway, it failed with error #22022 (see below)
backup, location of last
select database_name, case type when 'L' then 'Log' else 'Data' end as Backuptype,
physical_device_name, backup_start_date
from
msdb.dbo.backupset a
join
msdb..backupmediaset b
on
a.media_set_id =
b.media_set_id
join
msdb.dbo.backupmediafamily
c on a.media_set_id
= c.media_set_id
where type
in
('L','D') -- L = Logbackup, D = Databackup
and backup_start_date
> getdate()-10
--and 'your_db_name' = database_name
order by backup_start_date
desc, database_name
asc, Backuptype
backup failed with error # 22022 – need to try one of the 3 methods described here. The 3rd one worked for me. 1st one did NOT.
First go through this sequence
Open SQL Server Configuration Manager.
In SQL Server Configuration Manager, click SQL Native Client Configuration, right-click Aliases, and then click New Alias.
In the Alias - New dialog box, select Named Pipes in the Protocol list.
In the Alias Name box, specify the name of the alias.
In the Server box, specify the instance of SQL Server 2005, and then click OK.
Open SQL Server Management Studio, and then connect the instance of SQL Server 2005.
Right-click SQL Server Agent, and then click Properties.
In the SQL Server Agent Properties dialog box, click Connection.
In the Alias local host server box, type the name of the alias that you specified in step 4, and then click OK.
In SQL Server Management Studio, right-click SQL Server Agent, and then click Restart.
This still didn’t work ‘til I enabled Named Pipes
In SQL Server Configuration Manager, in the console pane, expand SQL Server 2005 Network Configuration.
In the console pane, click Protocols for <instance name>.
In the details pane, right-click the protocol you want to change, and then click Enable or Disable.
In the console pane, click SQL Server 2005 Services.
Inthe details pane, right-click SQL Server (<instance name>), and then click Restart, to stop and restart the SQL Server service.
blanks, strip – ltrim(rtrim(@TempValue))
– see also special characters, strip out
carriage return
find
select count(*) from ExportContacts
where companyCode like '%'+char(13)+'%' or companyCode like '%'+char(10)+'%'
replace
update companyCode set companyCode = replace(replace(companyCode,char(10),''),char(13),'')
from companyCode where charindex(char(13), companyCode)<>0
cascade delete
You may have to first drop the existing foreign key constraint
ALTER TABLE dbo.T2
DROP CONSTRAINT FK_T1_T2
Once that's gone, you can go ahead and add the foreign key constraint with the cascade delete
ALTER TABLE dbo.T2
ADD CONSTRAINT FK_T1_T2_Cascade
FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID)
ON DELETE CASCADE
category, trim each category proportionally – see NTILE to trim each category proportionally
changes to table cannot be saved – see Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created.
child-parent relationships inside table, reveal relationship among separate records in one row - see parent-child relationship fields inside table, reveal relationship among separate records in one row
example:
Our main table, the one we start with, is all our AD users: usersADUIHealthServices.
From there, left join usersADUIHealthServices to both of the
Duo subscriptions: DuoUsers_prod & DuoUsers_epcs duoep.
Finally, scavenge any hc users (from our other domain) by
left join DuoUsers_prod & DuoUsers_epcs both to usersADhc.
We branched out to two different Duo subscriptions and thus get two different
lists of joined usersADhc, but want to combine the three separate columns
pertaining to usersADhc which would otherwise be spread over 6 columns down TO
just the 3 columns (OU, enabled, lastLogonDate), combining results from 6 columns
(3 each for the two Duo subscriptions) back down to 3 columns.
select
usrUI.OU OU_UICC,
usrUI.DisplayName,
usrUI.sAMAccountName,
usrUI.Enabled
Enabled_UI,
usrUI.lastLogonDate
lastLogon_U,
COALESCE(
usrHCpr.
OU,
usrHCep.
OU)
AS
OU_HC,
COALESCE(
usrHCpr.Enabled,
usrHCep.Enabled)
AS
Enabled_HC,
COALESCE(
usrHCpr.
lastLogonDate,
usrHCep.
lastLogonDate)
AS
lastLogon_HC,
duopr.user_id,
duopr.
is_enrolled,
duopr.status,
duoep.user_id,
duoep.
is_enrolled,
duoep.status
from
usersADUIHealthServices usrUI
left join
DuoUsers_prod duopr
ON
duopr.username
=
usrUI.sAMAccountName
left join
DuoUsers_epcs duoep
ON
duoep.
username =
usrUI.sAMAccountName
left join
usersADhc usrHCpr
ON
duopr.username
=
usrHCpr.sAMAccountName
left join
usersADhc usrHCep
ON
duoep.
username =
usrHCep.sAMAccountName
order by
usrUI.OU,
usrUI.DisplayName
column name invalid – see invalid column name in SSMS when looking at a query
columns, combine like – see coalesce
combine like columns – see coalesce
command line SQL – see sqlcmd
comment
just one line: --
a block of lines:
/*
stuff
*/
compare fields in one table (or joined tables) to other fields in same table (or joined tables) – see fields, compare, maximum of two fields in a table
compare tables from 2 different databases – see join tables from 2 different databases
compare two tables - using a join
here's an example from here, assuming we are comparing tables A and B, and the primary key of both tables is ID:
SELECT 'Table A'
as
TableName,
A.ID,
A.COL1,
A.COL2,
A.COL3,
...
FROM A
UNION ALL
SELECT 'Table B'
as TableName,
B.ID,
B.COL1,
B.COl2,
B.COL3,
...
FROM B
)tmp
GROUP BY
ID,
COL1,
COL2,
COL3
...
HAVING
COUNT(*)
= 1
ORDER BY
ID
The above returns all rows in either table that do not completely match all columns in the other. In addition, it returns all rows in either table that do not exist in the other table. It handles nulls as well, since GROUP BY normally consolidates NULL values together in the same group. If both tables match completely, no rows are returned at all.
The MIN() aggregate function used on the TableName column is just arbitrary -- it has no effect since we are only returning groups of rows in which there has been no consolidation with the GROUP BY (note the HAVING clause).
compatibility level of a database – see database compatibility level, database, properties pertinant to migration
SELECT @@SERVERNAME
$SQLInstances
=
(Get-ItemProperty
'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
foreach
($sql
in
$SQLInstances) {[PSCustomObject]@{InstanceName
=
$sql}}
Write-Host
"The following SQL Instances were detected on the server
$($env:Computername):
$SQLInstances"
-ForegroundColor Yellow
- when everything is varchar, very simple: +
- concatenate an integer to varchar:
Note = Note + ', #' + cast(@CustomerGoingAway as varchar(10))
declare @now
datetime
set
@now =
getdate()
print
'now: '
+
cast(@now
as varchar(20))
- concatenate a memo or ntext to varchar:
DECLARE @ptrval
binary(16)
SELECT @ptrval
=
TEXTPTR(SalesNote)
FROM CustomerNote
where CustomerFK =
@CustomerGoingAway
-- the "null" 1st arg means tack it on the end
(0 would put at beginning); 0 2nd arg deletes no data
UPDATETEXT
CustomerNote.SalesNote @ptrval null 0 @strCustomerGoingAway;
concatenate several records of a related table into one field –
SELECT
dupes.howMany
as
howManyFMOs,
c.COMPANY_NAME,
Dupes.CustomerFK,
STUFF((SELECT
', '
+
x.f AS [text()]
FROM
(select
distinct
CustomerFK,
(ISNULL(FMO,'none')) as f
from
Project) x
WHERE
x.CustomerFK
= Dupes.CustomerFK
FOR
XML
PATH('')), 1,
1,
''
)
AS whichFMOs
FROM
(SELECT
count(ISNULL(FMO,'none')) as
howMany,
CustomerFK
FROM (SELECT
DISTINCT FMO,
CustomerFK
FROM
dbo.Project AS
p)
as PossibleDupes
group
by CustomerFK
having
count(ISNULL(FMO,'none'))>1)
as
Dupes
join Customer c
on
c.ID=Dupes.CustomerFK
order
by howMany
desc
connection actively refused - see error 10061
connections to database – see database processes, activity monitor in SSMS, sp_who2, sp_whoisactive, kill a process
connections by user – see user processes, activity monitor in SSMS, sp_who2, sp_whoisactive, kill a process
connections, view active
SELECT
DB_NAME(dbid)
as DBName,
COUNT(dbid)
as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid
> 0
GROUP BY
dbid, loginame
order by DBName
select top
5 l.ID
as
start,
(
select
min(a.ID) as ID
from timesheet as a
left outer
join timesheet as b
on a.ID
= b.ID
- 1
where b.ID
is null
and a.ID
>= l.ID
) as endID
from timesheet as l
left
outer
join timesheet
as r
on r.ID
= l.ID
- 1
where r.ID
is null
order by
start desc
contiguous records, find gap – if ID is supposed to start off as contiguous and you want to find gaps where records were deleted
select
top
5 start,
stop,
stop-start+1
as diff from
(
select m.ID
+ 1
as
start,
(select min(ID)
- 1
from timesheet
as x
where
x.ID
>
m.ID)
as stop
from timesheet
as m
left outer join
timesheet as
r on
m.ID
=
r.ID
- 1
where
r.ID
is null
)
as x
where
stop
is
not null
order by stop desc
constraint, rename
sp_rename 'PK_ExportCompany', 'PK_ExportCompany_old'>
conversion failed when converting date and/or time from character string – See concatenate date time to varchar
conversion failed when converting the varchar value ‘sample text’ to data type int. I get this when trying to cast(SomeField) as int. User-defined function:
CREATE FUNCTION dbo.UDF_ParseNumericChars
(
@string VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @IncorrectCharLoc SMALLINT
SET @IncorrectCharLoc
=
PATINDEX('%[^0-9]%',
@string)
WHILE
@IncorrectCharLoc
> 0
BEGIN
SET @string
=
STUFF(@string,
@IncorrectCharLoc,
1,
'')
SET
@IncorrectCharLoc
=
PATINDEX('%[^0-9]%',
@string)
END
SET @string
= @string
RETURN @string
END
GO
copy a table
SELECT * INTO newtable FROM originaltable
CPU use, which databases taking up most of
WITH CPU_Per_Db
AS
(SELECT
dmpa.DatabaseID
,
DB_Name(dmpa.DatabaseID)
AS [Database]
,
SUM(dmqs.total_worker_time)
AS CPUTimeAsMS
FROM
sys.dm_exec_query_stats dmqs
CROSS APPLY
(SELECT
CONVERT(INT,
value)
AS [DatabaseID]
FROM
sys.dm_exec_plan_attributes(dmqs.plan_handle)
WHERE
attribute =
N'dbid') dmpa
GROUP BY
dmpa.DatabaseID)
SELECT
[Database]
,[CPUTimeAsMS]
,CAST([CPUTimeAsMS]
* 1.0 /
SUM([CPUTimeAsMS])
OVER()
* 100.0
AS DECIMAL(5,
2)) AS [CPUTimeAs%]
FROM CPU_Per_Db
ORDER BY
[CPUTimeAsMS] DESC;
create table directly from a query – see table, create from a query
created, when were tables created? – see tables, when created?
cross tab queries
links to code samples:
Dynamic Crosstab Queries – the best solution I've found. Slightly modified form below (added @whereclause parameter):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC
[dbo].[sp_CrossTab]
@table AS
sysname,
-- Table to crosstab
@onrows AS
nvarchar(128), -- Grouping key
values (on rows)
@onrowsalias AS sysname
= NULL,
-- Alias for grouping column
@oncols AS
nvarchar(128), -- Destination
columns (on columns)
@sumcol AS sysname
= NULL,
-- Data cells
@whereclause AS nvarchar(128)
= NULL
-- where clause
AS
DECLARE
@sql AS varchar(8000),
@NEWLINE AS
(1)
SET @NEWLINE
= CHAR(10)
-- step 1: beginning of SQL string
SET @sql =
'SELECT' + @NEWLINE
+
' '
+ @onrows
+
CASE
WHEN
@onrowsalias IS
NOT
NULL
THEN ' AS '
+ @onrowsalias
ELSE ''
END
CREATE TABLE
#keys(keyvalue nvarchar(100)
NOT NULL
PRIMARY KEY)
DECLARE @keyssql
AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys '
+
'SELECT DISTINCT CAST('
+
@oncols + ' AS
nvarchar(100)) ' +
'FROM '
+ @table
+ @NEWLINE +
CASE
WHEN @whereclause IS
NOT NULL THEN @whereclause +
@NEWLINE
ELSE
''
END
--PRINT @keyssql+ @NEWLINE -- For debug
EXEC
(@keyssql)
DECLARE @key\
AS
nvarchar(100)
SELECT @key
=
MIN(keyvalue)
FROM #keys
WHILE @key
IS
NOT
NULL
BEGIN
SET @sql = @sql
+ ','
+ @NEWLINE +
' SUM(CASE CAST('
+
@oncols +
' AS nvarchar(100))' +
@NEWLINE +
' WHEN N''' +
@key +
''' THEN ' +
CASE
WHEN @sumcol IS
NULL THEN
'1'
ELSE '1' --@sumcol
END + @NEWLINE +
'
ELSE 0' +
@NEWLINE +
' END) AS ['
+ @key
+ ']'
SELECT
@key =
MIN(keyvalue) FROM #keys
WHERE keyvalue
> @key
END
SET @sql = @sql
+ @NEWLINE +
'FROM ' +
@table + @NEWLINE +
CASE
WHEN @whereclause IS
NOT NULL
THEN @whereclause +
@NEWLINE
ELSE ''
END +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows
--PRINT @sql + @NEWLINE -- For debug
EXEC (@sql)
Microsoft SQL Server - Dynamic Cross-Tabs/Pivot Tables
create user - see user, create
view all database users with their assigned roles plain vanilla along with custom. Keep an eye out for odd-looking ones. Can also list custom role permissions assigned.
use starfleetCommand
select
dp.Name
userID,
dp2.Name role
from
sys.database_principals
left outer join
sys.database_role_members
rm on
dp.principal_id=rm.member_principal_id
left outer join
sys.database_principals
dp2 on
dp2.principal_id=rm.role_principal_id
where
dp2.Name
is not null and
dp.name
<>
'dbo'
order by
dp.Name
CREATE ROLE web
GRANT SELECT, INSERT, UPDATE, DELETE TO web
custom role permissions assigned
to see which permissions have been assigned to a custom database roles
use starfleetCommand
select
dp.NAME AS
principal_name,
dp.type_desc AS
principal_type_desc,
o.NAME AS
object_name,
perm.permission_name,
perm.state_desc
AS permission_state_desc
from
sys.database_permissions perm
left
OUTER
JOIN
sys.all_objects o
on
perm.major_id
=
o.OBJECT_ID
inner
JOIN
sys.database_principals dp
on
perm.grantee_principal_id
=
dp.principal_id
--where
dp.NAME not in ('guest','public','dbo')
where
dp.type_desc
=
'DATABASE_ROLE'
and
dp.NAME
<>
'public'
order by
dp.NAME
Create custom role and assign permissions
CREATE ROLE web
GRANT SELECT,
INSERT,
UPDATE,
DELET TO web
from list of users and roles that have permissions to an object (table) in SQL
query lists custom database roles and which access they were specifically granted or denied
select
permission_name,
state_desc,
type_desc,
U.name,
OBJECT_NAME(major_id)
from
sys.database_permissions P
JOIN
sys.tables T
ON
P.major_id
=
T.object_id
JOIN
sysusers U
ON U
.uid
= P
.grantee_principal_id
I've only found this above to work from the master DATABASE and haven't found this above to be useful
dark mode for SSMS, – see SSMS dark mode
database, back up – see back up database
database compatibility level – see also database, properties pertinant to migration which displays extra fields such as database owner, recovery model, data file location on disk, log file location on disk, data file size, log file size in addition to compatibility level
SELECT name,
compatibility_level
,
version_name =
CASE compatibility_level
WHEN
65 THEN
'SQL Server 6.5'
WHEN
70 THEN
'SQL Server 7.0'
WHEN
80 THEN
'SQL Server 2000'
WHEN
90 THEN
'SQL Server 2005'
WHEN
100 THEN
'SQL Server 2008/R2'
WHEN
110 THEN
'SQL Server 2012'
WHEN
120 THEN
'SQL Server 2014'
WHEN
130 THEN
'SQL Server 2016'
WHEN
140 THEN
'SQL Server 2017'
WHEN
150 THEN
'SQL Server 2019'
WHEN
160 THEN
'SQL Server 2022'
ELSE
'new unknown - '
+
CONVERT(varchar(10),compatibility_level)
END
FROM
sys.databases
change
ALTER DATABASE starfleet SET COMPATIBILITY_LEVEL = 150
Below also gets owner and recovery model
SELECT
name,
suser_sname(owner_sid)
AS
DBOwnerName,
recovery_model_desc,
compatibility_level
,
version_name =
CASE compatibility_level
WHEN
65 THEN
'SQL Server 6.5'
WHEN
70 THEN
'SQL Server 7.0'
WHEN
80 THEN
'SQL Server 2000'
WHEN
90 THEN
'SQL Server 2005'
WHEN
100 THEN
'SQL Server 2008/R2'
WHEN
110 THEN
'SQL Server 2012'
WHEN
120 THEN
'SQL Server 2014'
WHEN
130 THEN
'SQL Server 2016'
WHEN
140 THEN
'SQL Server 2017'
WHEN
150 THEN
'SQL Server 2019'
WHEN
160 THEN
'SQL Server 2022'
ELSE
'new unknown - '
+
CONVERT(varchar(10),compatibility_level)
END
FROM
sys.databases
ORDER BY name
see also Database Compatibility Levels in SQL Server, chart that maps numbers to SQL versions, Query Store Usage Scenarios to store how queries are run, query store, enable, query store, get options and status
database processes – see also activity monitor in SSMS, user processes, sp_who2, sp_whoisactive, kill a process
SELECT spidspan