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, declare – see also loop through array – esepcially if you want to create an array that includes an auto-incremented ID field
DECLARE
@fruitTableVariable
TABLE
(name
VARCHAR(50))
INSERT INTO
@fruitTableVariable (name)
VALUES
('apple'), ('orange')
SELECT
name
FROM
@fruitTableVariable
array, loop through see – loop through array,
array, reverse see – delimited string, scalar function to reverse order of to reverse the order of a delimited string.
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.
begin last month – see also month, last day of previous month, month, last day of current month, last month’s stuff
select dateadd(mm, -1, convert(datetime, convert(char(7), getdate(), 120) + '-01'))
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
spid ,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name
DBName,
cmd Command,
cpu CPUTime,
physical_io DiskIO,
last_batch LastBatch,
[program_name] ProgramName
FROM master.dbo.sysprocesses sp
JOIN
master.dbo.sysdatabases
sd ON
sp.dbid
=
sd.dbid
where
sd.name
like
'Meta%'
-- DBName
-- loginame like ad% -- user
ORDER BY spid
database, disk space used – see disk space used by database, disk space used by each object in a database
databases, join tables from 2 different – see join tables from 2 different databases
databases, list
SELECT [name]
AS DatabaseName,
CASE
WHEN
name
IN('master','msdb','model')
OR is_distributor
= 1
THEN
'S'
ELSE
'U'
END
AS DatabaseTyp
FROM
sys.databases
WHERE [name]
<>
'tempdb'
AND source_database_id
IS
NULL
ORDER
BY [name]
ASC
database owners, list – see also database, properties pertinant to migration
SELECT
name,
suser_sname(owner_sid)
AS DBOwnerName
FROM master.sys.databases
change
ALTER AUTHORIZATION ON DATABASE::starfleet TO sa
Below also gets recovery model and compatility level
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
ALTER AUTHORIZATION ON DATABASE::ILEBiTx TO sa
database, properties pertinant to migration
This query shows some salient properties of a database, especially those Properties pertinant to migrating database from older version to a newer version, including
- database owner
- recovery model
- compatibility level
- data file location on disk
- log file location on disk
- data file size
- log file size
SELECT
mdf.name
,
suser_sname(sdb.owner_sid
)
AS DBOwnerName
,
sdb.recovery_model_desc
as
'RecMdl'
,
sdb.compatibility_level as CmptblLvl
,
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
,
mdf.physical_name
as data_file
,
ldf.physical_name
as log_file
,
db_size =
CAST((mdf.size
*
8.0)/1024
AS
DECIMAL(8,2))
,
log_size =
CAST((ldf.size
* 8.0
/1024)
AS
DECIMAL(8,2))
FROM
(SELECT
*
FROM
sys.master_files
WHERE
type_desc
=
'ROWS') mdf
JOIN (SELECT
*
FROM
sys.master_files
WHERE
type_desc
=
'LOG') ldf
ON
mdf.database_id
=
ldf.database_id
join
sys.databases sdb
on
mdf.database_id
=
sdb.database_id
where
mdf.name
not
in
('master','tempdb','model','msdb')
and
mdf.name
in
('HyperVHosts','ManageServices','VDI','VDIController')
order by name;
SELECT
name,
recovery_model_desc
FROM
sys.databases
Below also gets owner and compatibility level
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
database size – see database, properties pertinant to migration
data location, where (in file system)
SELECT
mdf.database_id,
mdf.name,
mdf.physical_name
as data_file,
ldf.physical_name
as log_file,
db_size =
CAST((mdf.size
* 8.0)/1024
AS DECIMAL(8,2)),
log_size
=
CAST((ldf.size
* 8.0
/1024)
AS DECIMAL(8,2))
FROM
(SELECT
*
FROM
sys.master_files
WHERE type_desc
=
'ROWS') mdf
JOIN (SELECT
*
FROM
sys.master_files
WHERE type_desc
=
'LOG') ldf
ON mdf.database_id
=
ldf.database_id
date, convert string to -
CONVERT(datetime, MeetingDate + ' ' + MeetingTime, 102)
date, determine whether filled or not
SELECT CASE WHEN
DATEDIFF(dd,6/1/2023,
BlanksSent > 0
THEN 1 ELSE 0 END AS Expr2
FROM Project
date tables created? – see tables, when created?
date, truncate off time to get just date – see truncate datetime to date
date, format change
create FUNCTION
dbo.FormatDateTime (@dt
DATETIME,
@format VARCHAR(16))<
RETURNS VARCHAR(64)
AS
BEGIN
DECLARE @dtVC
VARCHAR(64)
SELECT @dtVC
= CASE @format
WHEN 'LONGDATE'
THEN
DATENAME(dw, @dt)
+
','
+
SPACE(1) + DATENAME(m, @dt) +
SPACE(1)
+
CAST(DAY(@dt)
AS VARCHAR(2))
+
','
+
SPACE(1)
+ CAST(YEAR(@dt)
AS CHAR(4))
WHEN
'LONGDATEANDTIME'
THEN DATENAME(dw, @dt) +
','
+
SPACE(1)
+
DATENAME(m, @dt)
+ SPACE(1)
+
CAST(DAY(@dt)
AS VARCHAR(2))
+ ','
+ SPACE(1)
+ CAST(YEAR(@dt)
AS CHAR(4))
+ SPACE(1)
+ RIGHT(CONVERT(CHAR(20), @dt -
CONVERT(DATETIME,
CONVERT(CHAR(8), @dt, 112)), 22), 11)
WHEN
'SHORTDATE'
THEN
LEFT(CONVERT(CHAR(19), @dt, 0), 11)
WHEN 'SHORTDATEANDTIME'
THEN REPLACE(REPLACE(CONVERT(CHAR(19),
@dt, 0),
'AM',
' AM'),
'PM',
' PM')
WHEN
'UNIXTIMESTAMP'
THEN
CAST(DATEDIFF(SECOND,
'19700101', @dt) AS VARCHAR(64))
WHEN
'YYYYMMDD'
THEN
CONVERT(CHAR(8), @dt, 112)
WHEN 'YYYY-MM-DD'
THEN CONVERT(CHAR(10), @dt, 23)
WHEN 'YYMMDD'
THEN CONVERT(VARCHAR(8), @dt, 12)
WHEN 'YY-MM-DD'
THEN STUFF(STUFF(CONVERT(VARCHAR(8), @dt, 12), 5, 0,
'-'), 3, 0,
'-')
WHEN
'MMDDYY'
THEN
REPLACE(CONVERT(CHAR(8), @dt, 10),
'-', SPACE(0))
WHEN 'MM-DD-YY'
THEN CONVERT(CHAR(8), @dt, 10)
WHEN 'MM/DD/YY'
THEN CONVERT(CHAR(8), @dt, 1)
WHEN 'MM/DD/YYYY'
THEN CONVERT(CHAR(10), @dt, 101)
WHEN 'DDMMYY'
THEN REPLACE(CONVERT(CHAR(8), @dt, 3),
'/',
SPACE(0))
WHEN 'DD-MM-YY'
THEN REPLACE(CONVERT(CHAR(8), @dt, 3),
'/',
'-')
WHEN 'DD/MM/YY'
THEN CONVERT(CHAR(8), @dt, 3)
WHEN 'DD/MM/YYYY'
THEN CONVERT(CHAR(10), @dt, 103)
WHEN 'HH:MM:SS
24' THEN CONVERT(CHAR(8), @dt, 8)
WHEN 'HH:MM
24' THEN LEFT(CONVERT(VARCHAR(8), @dt, 8), 5)
WHEN 'HH:MM:SS
12' THEN LTRIM(RIGHT(CONVERT(VARCHAR(20), @dt, 22), 11))
WHEN 'HH:MM
12' THEN
LTRIM(SUBSTRING(CONVERT(VARCHAR(20), @dt, 22), 10, 5)
+ RIGHT(CONVERT(VARCHAR(20), @dt, 22), 3))
ELSE
'Invalid format specified'
END
RETURN @dtVC
END
GO
Sample usage:
DECLARE @now
DATETIME
SET @now
=
GETDATE()
PRINT dbo.FormatDateTime(@now,
'LONGDATE')
PRINT dbo.FormatDateTime(@now,
'LONGDATEANDTIME')
PRINT dbo.FormatDateTime(@now,
'SHORTDATE')
PRINT dbo.FormatDateTime(@now,
'SHORTDATEANDTIME')
PRINT dbo.FormatDateTime(@now,
'UNIXTIMESTAMP')
PRINT dbo.FormatDateTime(@now,
'YYYYMMDD')
PRINT dbo.FormatDateTime(@now,
'YYYY-MM-DD')
PRINT dbo.FormatDateTime(@now,
'YYMMDD')
PRINT dbo.FormatDateTime(@now,
'YY-MM-DD')
PRINT dbo.FormatDateTime(@now,
'MMDDYY')
PRINT dbo.FormatDateTime(@now,
'MM-DD-YY')
PRINT dbo.FormatDateTime(@now,
'MM/DD/YY')
PRINT dbo.FormatDateTime(@now,
'MM/DD/YYYY')
PRINT dbo.FormatDateTime(@now,
'DDMMYY')
PRINT dbo.FormatDateTime(@now,
'DD-MM-YY')
PRINT dbo.FormatDateTime(@now,
'DD/MM/YY')
PRINT dbo.FormatDateTime(@now,
'DD/MM/YYYY')
PRINT dbo.FormatDateTime(@now,
'HH:MM:SS 24')
PRINT dbo.FormatDateTime(@now,
'HH:MM 24')
PRINT dbo.FormatDateTime(@now,
'HH:MM:SS 12')
PRINT dbo.FormatDateTime(@now,
'HH:MM 12')
PRINT dbo.FormatDateTime(@now,
'goofy')
date portion of GETDATE() –
DATEADD(dd,
DATEDIFF(dd, 0,
GETDATE()), 0)
day, subtract 3 days from today’s date –
DATEADD(d,
-
3,
GETDATE())
day, beginning of today –
DATEADD(dd,
DATEDIFF(dd, 0,
GETDATE()),0
)
day, end of (or, to be more precise, beginning of tomorrow) – DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1)
day, group by – see group by day
day portion of date, with
zeroes padded to left - RIGHT(REPLICATE('0', 2) + CONVERT(varchar(2), DATENAME(Day, theDate)), 2) AS theDay
days elapsed - (DATEDIFF (d,
getDate(),
EST_MAILING_DATE)) > 6
deadlocks, find – see activity monitor in SSMS, database processes, user processes, sp_who2, sp_whoisactive, kill a process
debug
Until version 18 of SSMS, you used to be able to debug directly in SSMS. But now you must use full-fledged Visual Studio (I don’t think just Visual Studio Code won’t do). Create a small SQL file to execute a stored procedure. With that small file open, from the Visual Studio ribbon: SQL → Execute with Debugger
debug print – print statement (will work in stored procedures, will NOT work in functions)
print 'HowManyPresentersThisCustomer' + cast(@HowManyPresentersThisCustomer as varchar(5))
decimal, view doesn’t display after a calculation like division – need to use the vCAST statement:
SELECT id,
AVG(CAST(qty AS
numeric(12,2))) AS avg_qty
FROM sales
If you divide, it seems you must use the cast statement in the numerator as well as for the whole evaluated expression:
SELECT TOP
(100) PERCENT P1.ID, P1.CustomerFK, SUM(ISNULL(dbo.RestaurSeminar.Count, 0)) AS TotAttendees, P1.NUM_ACTUALLY_MAILED,
P1.PROJECT_NAME,
P1.PRINTED, P1.EST_MAILING_DATE, P1.ACCOUNT_MANAGER, P1.NUM_TO_MAIL,
CAST(CASE WHEN P1.[NUM_ACTUALLY_MAILED] =
0 THEN 0 ELSE CAST(SUM(dbo.RestaurSeminar.[COUNT]) AS decimal(6, 4))
/ P1.[NUM_ACTUALLY_MAILED]
END AS decimal(6, 4)) AS results
FROM dbo.Customer
INNER JOIN
dbo.Project AS P1 ON dbo.Customer.ID = P1.CustomerFK INNER JOIN
dbo.Project ON dbo.Customer.ID = dbo.Project.CustomerFK LEFT OUTER JOIN
dbo.RestaurSeminar
ON
P1.ID
=
dbo.RestaurSeminar.ProjectFK
GROUP BY
P1.ID, P1.PRINTED, dbo.Project.ID, P1.CustomerFK, P1.NUM_ACTUALLY_MAILED, P1.PROJECT_NAME, P1.PRINTED, P1.EST_MAILING_DATE,
P1.ACCOUNT_MANAGER, P1.NUM_TO_MAIL, dbo.RestaurSeminar.Count
HAVING (dbo.Project.ID = 3232)
ORDER BY P1.PRINTED DESC
delete inner join
delete dbo.FirstTable
FROM dbo.SecondTable INNER
JOIN
dbo.FirstTable
ON
dbo.SecondTable.MaxID = dbo.FirstTable.ID
delimited string, scalar function to reverse order of
create the function
CREATE
FUNCTION
dbo.ReverseStringSplitValues
(
@InputString
VARCHAR(MAX),
@Delimiter
CHAR(1)
)
RETURNS
VARCHAR(MAX)
AS
BEGIN
DECLARE
@ReversedString
VARCHAR(MAX);
WITH
SplitValues
AS
(
SELECT
value,
ROW_NUMBER()
OVER
(ORDER BY
(SELECT
NULL))
AS
rn
FROM
STRING_SPLIT(@InputString, @Delimiter)
)
SELECT
@ReversedString
=
STRING_AGG(value, @Delimiter)
WITHIN
GROUP
(ORDER BY
rn
DESC)
FROM
SplitValues;
RETURN
@ReversedString;
END;
more complicated call to extract OU from DistinguishedName
SELECT dbo.ReverseStringSplitValues('This string\is\delimited\4\times', '\');
call directly
SELECT
Name, IPv4Address,
dbo.ReverseStringSplitValues(
REPLACE(SUBSTRING(DistinguishedName,
CHARINDEX('OU=',
DistinguishedName)
+
3,
LEN(DistinguishedName) -
CHARINDEX('OU=', DistinguishedName)
-
LEN(',DC=minions,DC=com')
-2),
',OU=',
'\'),'\')
AS OU, DistinguishedName,
OperatingSystem
FROM
computers
ORDER BY
OU
SELECT TOP 50
dense_rank() OVER (ORDER BY RIGHT(replicate('0', 2)
+
cast(COUNT(c1.ID)
AS varchar(3)), 3) + ' ' +
cast(CONVERT(CHAR(8), MAX(p1.EST_MAILING_DATE), 112) AS varchar(30)) DESC)
AS
'RANK',
COUNT(c1.ID) AS NumMailings, c1.COMPANY_NAME,
MAX(p1.EST_MAILING_DATE) AS MostRecentEMD
FROM dbo.Customer
AS c1 INNER JOIN
dbo.Project AS
p1 ON c1.ID = p1.CustomerFK
GROUP BY
c1.COMPANY_NAME
HAVING
max(p1.EST_MAILING_DATE) > '1/1/8'
ORDER BY
RIGHT(replicate('0', 2) + cast(COUNT(c1.ID)
AS varchar(3)), 3) + ' ' +
cast(CONVERT(CHAR(8), MAX(p1.EST_MAILING_DATE), 112) AS varchar(30)) DESC
disk space used by a bunch of similarly-named files, percentage of disk space
How much space are our SQL server error logs consuming on the F drive? (PowerShell)
First, finderror log files location.
Then put that in $errorFileLogLocation variable below
$errorFileLogLocation
=
"F:\Microsoft
SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log"
"{0:P}"
-f ((gci $errorFileLogLocation
Errorlog* | measure Length -s).sum / (Get-PSDrive
F |
%
{($_.used
+
$_.free)}))
EXEC sp_spaceused;
disk space used by each object in a database
SELECT
t.object_id,
OBJECT_NAME(t.object_id)
ObjectName,
sum(u.total_pages)
*
8 Total_Reserved_kb,
sum(u.used_pages)
*
8 Used_Space_kb,
u.type_desc,
max(p.rows) RowsCount
FROM
sys.allocation_units u
JOIN
sys.partitions
p on
u.container_id
=
p.hobt_id
JOIN
sys.tables
t on
p.object_id
=
t.object_id
GROUP BY
t.object_id,
OBJECT_NAME(t.object_id),
u.type_desc
ORDER BY
Used_Space_kb
desc,
ObjectName
display table info –
Select * From Information_Schema.Columns Where Table_Name = 'Customer'
or, for considerably more info:
sp_help Customer
select
'hi there'
or
print
'hi there'
Docmd.TransferText – convert this command from Access to SQL server – from here
Dim rs As New
ADODB.Recordset
Dim strMsg As String
Dim strSQL As String
Dim strText As String
Dim strFileText As String
Dim strHeaderText As String
Dim fs
Dim a
Dim CommandText As String
Dim QueryName As String
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
QueryName = "dbo.[" & QueriesCB.Value & "]"
strSQL = "SELECT * FROM " & QueryName
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
'rs.Open CommandText, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic, adCmdText
If rs.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No Records to Export!"
Exit Sub
End If
strHeaderText = strHeaderText & """First""" & ","
strHeaderText = strHeaderText & """Last""" & ","
strHeaderText = strHeaderText & """Email"""
strFileText = strFileText & strHeaderText & vbCrLf
rs.MoveFirst
Do While Not rs.EOF
strText = strText & """" & rs(0)
& """" & ","
strText = strText & """" & rs(1)
& """" & ","
strText = strText & """" & rs(2) & """"
strFileText = strFileText & strText & vbCrLf
strText = ""
rs.MoveNext
Loop
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:\Program Files\xx\xx.csv", True)
a.WriteLine (strFileText)
a.Close
rs.Close
Set rs = Nothing
'DoCmd.TransferText acExportDelim, , QueriesCB.Value,
"C:\Program Files\xx\xx.csv", True
DOS command, get SQL from - see sqlcmd
drop index, test to see whether it exists first
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_yourTable_companyCode') DROP INDEX IX_yourTable_companyCode ON yourTable
drop, grant permission to delete/drop - DROP is not a grantable permission. People who can drop a table are:
- dbo
- owner of the schema
- owner of the table (usually the schema owner but it can be changed to someone else)
- members of the db_ddladmin fixed database role
- members of the db_owner fixed database role
- members of the sysadmin fixed server role
- grantees of the CONTROL permission on the table or permissions that imply control on the table
- grantees of the ALTER permission on the schema or permissions that imply alter on the schema.
to grant the db_ddladmin role
ALTER ROLE db_ddladmin ADD MEMBER [[email protected]];
duplicates, get rid of
delete from
dbo.ProblemTable
where ID in
(SELECT MAX(ID) AS ID
FROM dbo.ProblemTable
GROUP BY Email
HAVING (COUNT(Email) > 1))
elapsed days - (DATEDIFF (d,
getDate (), EST_MAILING_DATE)) > 6
elapsed time in days, hours, minutes, seconds
select
top 10 HowMany
=
count(id),
oldest=min(dateadded),
newest=max(dateadded),
timeBetweenFirstAndLast =
case cast(datepart(day,
max(dateadded)
-
min(dateadded))
- 1 as varchar(10)) when 0 then ''
else cast(datepart(day,
max(dateadded)
- min(dateadded)) - 1 as
varchar(10)) + ' days '
end +
case
cast(datepart(hour,
max(dateadded)
-
min(dateadded)) as
varchar(10))
when 0 then''
else cast(r:gray'>(datepart(hour,
max(dateadded)
- min(dateadded)) as
varchar(10)) +
' hours '
end +
case cast(datepart(minute,
max(dateadded)
- min(dateadded)) as
varchar(10)) when 0 then
else cast(r:gray'>(datepart(minute,
max(dateadded)
- min(dateadded)) as
varchar(10)) +
' minutes ' end
+
cast(datepart(second, max(dateadded) -
min(dateadded))
as
varchar(10))
+
' seconds'
, host
from LogException
where
message='xxx'
and
datecreated>='2023-06-01 16:14'
and
eventType
not
in
('INFO',
'DEBUG')
group by
host
having
count(id) > 1
order by
count(id) desc
email, split out email part (as opposed to domain part) and then split out first, last name if separated by periods. Argh! What a mess!
SELECT email, LEFT(email, CHARINDEX('@'
, email)
- 1) AS EmailPart,
CASE WHEN
(CHARINDEX('.', LEFT(email, CHARINDEX('@', email)
- 1)) > 0)
THEN
LEFT(LEFT(email,
CHARINDEX('@', email) - 1), CHARINDEX('.', LEFT(email, CHARINDEX('@', email) - 1)) - 1)
ELSE
''
END AS FirstName,
CASE WHEN
(CHARINDEX('.', LEFT(email,
CHARINDEX('@', email) - 1)) > 0)
THEN
SUBSTRING(LEFT(email, CHARINDEX('@', email) - 1),
CHARINDEX('.', LEFT(email,
CHARINDEX('@', email) - 1)) + 1,
LEN(LEFT(email, CHARINDEX('@', email) - 1)) - LEN(CASE WHEN (CHARINDEX('.',
LEFT(email, CHARINDEX('@', email)
- 1)) > 0)
THEN LEFT(LEFT(email, CHARINDEX('@', email) - 1), CHARINDEX('.', LEFT(email, CHARINDEX('@', email)
- 1))
- 1)
ELSE ''
END) - 1) ELSE '' END AS LastName
FROM
dbo.ListTwo
email when job fails
make sure you add an operator (under SQL Server Agent)
also check
Select top 50 *
from msdb.dbo.sysmail_mailitems
order by mailitem_id desc
Select top 50 *
from msdb.dbo.sysjobhistory
order by instance_id desc
EXECUTE msdb.dbo.sp_notify_operator @name=N'PortalAdmin',@body=N'Test Message'
empty string, test for in ntext datatype field –
DataLength() = 0
SELECT
SERVERPROPERTY('ErrorLogFileName')
AS
'Error log file location'
error log files, number retained
read current value. If this returns 0, it means unlimited. If the command says it can’t find it, then either it’s never been created (which means the same as if it were 0 or unlimited) or the registry path is wrong and you need to check registry using regedit
EXEC
xp_instance_rpan>
xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft SQL Server\MSSQLServer',
N'NumErrorLogs'
write new value
EXEC
xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\Microsoft
SQL Serverr\MSSQLServer',
N'NumErrorLogs',
REG_DWORD , 6
Use this command rather than updating the registry itself - especially if your SQL is clustered. This command makes sure all servers part of the cluster get updated correctly.
error log files, maximum size each
write new value
EXEC
xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'ErrorLogSizeInKb',
REG_DWORD, 50000
Use this command rather than updating the registry itself - especially if your SQL is clustered. This command makes sure all servers part of the cluster get updated correctly.
error log files, what percentage disk space taking up - see disk space consumed by a bunch of similarly-named files, percentage of disk space
In this instance: How much space are our SQL server error logs consuming on the F drive?(PowerShell)
Excel, import
SQL Server Import and Export Wizard
Connect to SQL Server with the .NET Framework Data Provider for SQL Server
export - right click database, tasks, export data. When I tried this, I got errors:
find location of a small string in a bigger string - CHARINDEX -
SELECT SYSTEM_USER
AS 'Login Name', CHARINDEX( '\', SYSTEM_USER) AS
backslash_position,
field, drop -
if
exists(select
*
from
sys.columns
where
Name
=
N'nameFull'
and
Object_ID
=
Object_ID(N'GCP_FMExportContact'))
ALTER
TABLE
GCP_FMExportContact
DROP
COLUMN
nameFull
field counts for tables – see also table fields, list, tables with a certain number of records
example with more than 10 fields
SELECT
TABLE_NAME,
COUNT(COLUMN_NAME)
AS
ColumnCount
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY
TABLE_NAME
HAVING
COUNT(COLUMN_NAME)
>
10
ORDER BY
ColumnCount
DESC;
field length, maximum -
select
max(len(extension)) from Person
field lengths of a table - see also table fields, list for a query that has this and more info on fields, tables with a certain number of records
SELECT TableName = OBJECT_NAME(c.OBJECT_ID), ColumnName = c.name, DataType = t.name, MaxLength = c.max_length
FROM sys.columns AS c
JOIN sys.types AS t
ON c.user_type_id=t.user_type_id
WHERE OBJECT_NAME(c.OBJECT_ID) = 'MyTable'
fields, combine like – see coalesce
fields, compare – see also maximum of two fields in a table
compare two fields (created and last_login) from DuoUsers
to one field (last_seen) in possibly many records in DuoPhones
to find the most recent of all these fields for each DuoUser.
SELECT
duo.username,
(
SELECT
MAX(val)
FROM
(
SELECT
MAX(ph.last_seen)
-- move ph.last_seen
logic into a separate subquery to ensure only the most recent value is
considered for each duo.username.
FROM DuoPhones ph
WHERE
ph.username
=
duo.username
UNION
ALL
-- employ UNION ALL to
combine the maximum ph.last_seen with duo.created and duo.last_login,
effectively finding the overall maximum.
SELECT
duo.created
UNION
ALL
SELECT
duo.last_login
)
val_tbl(val)
)
AS
mostRecentActivity
FROM DuoUsers duo
fields in a database, list all
Using OBJECT CATALOG VIEWS
SELECT
T.NAME AS [TABLE NAME],
C.NAME
AS [COLUMN NAME],
P.NAME
AS
[DATA TYPE],
P.MAX_LENGTH
AS[SIZE],
CAST(P.PRECISION
AS
VARCHAR)
+'/'+
CAST(P.SCALE
AS
VARCHAR)
AS [PRECISION/SCALE]
FROM
SYS.OBJECTS AS T
JOIN
SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN
SYS.TYPES
AS P
ON
C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE
T.TYPE_DESC='USER_TABLE';
or Using INFORMATION SCHEMA VIEWS
SELECT
TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
COLUMN_DEFAULT,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM
INFORMATION_SCHEMA.COLUMNS
fields in a table, list - see table fields, list, field counts for tables, tables with a certain number of records
first day of last month – see begin last month
first day of month a year ago – see month, first day of a year ago
first word – return the first word of a multi-worded expression. The CASE statement is required so as not to bomb out on expressions that contain only one word. The expression below will get rid of middle initials, nicknames in parenthesis, etc.
CASE CHARINDEX(' ', dbo.Person.FirstName) WHEN 0 THEN FirstName ELSE SUBSTRING(dbo.Person.FirstName, 1, CHARINDEX(' ', dbo.Person.FirstName) - 1) END AS [First Name]
first name, untangling when extra initials, nicknames, etc. mixed in
Select
FirstName,
CASE
WHEN FirstName IS
NULL
or
len
(FirstName) = 1
THEN 'Agent'
ELSE
-- Even though FirstName is supposed to be one
monolithic name, often they’ll
-- throw in middle or first initials or a nick name.
-- If we’re going to do a mail merge where we're
greeting them by first name,
-- that’ll look dumb.
(CASE
CHARINDEX(' ', FirstName)
WHEN 0
THEN FirstName
-- no spaces? We’re good to go with no further work.
ELSE
-- Sometimes you have first initial followed by name, other times
-- name followed by middle initial. We want the main part of the
-- name without the initial either way.
case
when
len(FirstName) <
CHARINDEX(' ', FirstName)
* 2
-- First, see whether the first part takes up at least half
-- the whole string. If it does, the last part is probably
-- a middle initial and we can just use the first part and
-- discard the middle initial
then
-- Sometimes all you have is two initials separated by a space,
-- like "B J". In this case, it’d look kinda dumb to just
-- show "B". So for such short instances, display both initials.
case when
len(FirstName) < 4
then FirstName
else
SUBSTRING(FirstName, 1,
CHARINDEX(' ', FirstName))
end
else
-- Just because the first part is smaller than the last part,
-- we still don’t just automatically use the last part.
-- Sometimes you get two fully formed names, with the second
-- being the middle name. In that case we’d want the first part.
-- So look to see if the first part looks like
-- 1. an initial all by itself
-- (first place we’d see a space would be 2)
-- 2. initial followed by a period or two-letter title (like 'Dr')
-- (first place we'd see a space would be 3)
-- 3. two-letter title (like ‘Dr.’) followed by a period
-- (first place we’d see a space would be 4)
-- If the first place we see a space is <= 3, then that first part
-- is probably an initial and we’ll use the 2nd part. Otherwise
-- stick with the first part.
case when
CHARINDEX(' ', FirstName) <= 4
then
substring(FirstName,charindex(' ',FirstName)+1,len(FirstName))
else
SUBSTRING(FirstName, 1, CHARINDEX(' ', FirstName))
end
end
END)
END AS First
-- This doesn’t
properly display things like ‘Gary & Jane’ or catch the nickname a human
-- would use like choosing ‘Hank’ in ‘Henry "Hank"’ or deal well with Chinese names
-- like ‘Hu Ling Fu’ where which is right is anybody’s guess
From Person
float field, try to convert to int - get a TABLOCK error - this only happened when I was in Access linked to the SQL. When I did in SQL itself, went away.
foreign key, allow to be null – set the “default value or binding” for the parent table’s key field to (NULL)
foreign key constraint, remove all and then add them all back again
/* Disable_Enable_ForeignKeys.sql
-- Generate SQL statements to disable or enable all foreign key
constraints in the current database */
/* Show foreign keys that are disabled or untrusted
in the current database */
select
* from
sys.foreign_keys where
is_disabled = 1
select
*
from
sys.foreign_keys
where
is_not_trusted = 1
/* Generate sql statements to disable all
foreign key constraints in the current database */
set
nocount
on
SELECT
'ALTER TABLE ['
+
s.name + '].['
+ o.name +
'] NOCHECK CONSTRAINT ['
+
i.name +
']'
FROM
sys.foreign_keys i
INNER
JOIN
sys.objects o ON
i.parent_object_id =
o.object_id
INNER
JOIN
sys.schemas s ON
o.schema_id
= s.schema_id
WHERE
1=1
AND
i.is_not_for_replication
= 0
AND
i.is_disabled
= 0
/* Generate sql statements to re-enable
disabled or untrusted foreign key constraints
in the current database */
set
nocount on
SELECT
'ALTER TABLE [' +
s.name +
'].[' +
o.name +
'] WITH CHECK CHECK CONSTRAINT ['
+ i.name
+
']'
FROM
sys.foreign_keys i
INNER
JOIN
sys.objects o ON
i.parent_object_id =
o.object_id
INNER
JOIN
sys.schemas s ON
o.schema_id
= s.schema_id
WHERE
1=1
AND
i.is_not_for_replication
= 0
AND
(i.is_disabled
= 1 OR
i.is_not_trusted =
1)
full outer join – see outer join, full
fuzzy lookup – see here
Open Business Intelligence Development Studio.
Create a new Integration Services Project, add a new package – sounds simple, right? Well, go here for instructions. In short, On the File menu, point to New, and click Project to create a new Integration Services project. In the New Project dialog box, select Integration Services Project in the Templates pane. If you don’t see “Integration Services Project” as one of the choices here, you’re in trouble and you need to fix this problem before you go any further! Also, as a hint, make sure you select the “Business Intelligence Projects” folder in the left pane and NOT the “Visual Studio Solutions” folder in the “Other Project Types”
click the Data Flow tab, and then accept the add a data flow item option.
From the Control Flow Items section in the Toolbox, drag a Data Flow Task onto the control Flow surface. Double-click the new Data Flow Task or select the Data Flow tab.
On the Data Flow surface, drag the OLE DB Source adapter from the Data Flow Sources section of the Toolbox. Drag a Fuzzy Lookup Transformation from the Data Flow Transformations section of the Toolbox and an OLE DB Destination adapter from the Data Flow Destinations section. Select the Source and drag the green arrow to the Fuzzy Lookup to create a path between the two. Also create a path between Fuzzy Lookup and the Destination by selecting Fuzzy Lookup and dragging the green arrow to the Destination.
Double-click the OLE DB Source transform and configure it to point at your new data by selecting a connection and the input table that contains reference data that incoming records will be matched to.
Double-click Fuzzy Lookup to open the custom user interface (UI). From the Reference table name drop-down menu, select the connection and table to which you want the transform, to your already warehoused reference data, to point.
On the Columns tab, drag items you want to compare from Available Input Columns (from the OLE DB source) to Available Lookup Columns& (from the reference table). For example, you might want to compare StreetAddress in the input with Address in the reference table.
Select the check boxes for all items in Available Lookup Columns, and then click OK.
Point the OLE DB Destination to a connection for which you can write a new table, and then clickNew. Accept the default creation statement, and you are now ready to run Fuzzy Lookup.
To run the package you just created, right-click its name in the Solution Explorer window, and then select Execute.
generate script to copy user permissions – See permissions, generate script to copy user permissions
gap, find in contiguous records,– see contiguous records, find gap
grandparent-parent-child 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
group and include an ntext (memo) field
Let’s say SalesNote below is an ntext (memo) field. But we want the max of EST_MAILING_DATE and MAILED. How do we group when we get errors when trying to work with the ntext field? Coalesce.
SELECT p.FirstName, p.LastName, p.eMail, cn.SalesNote, pr.maxEMD AS RecentEMD, pr.maxMailed AS RecentMailed
FROM dbo.Customer
AS c INNER JOIN
dbo.Person AS p ON p.CustomerFK = c.ID INNER
JOIN
dbo.CustomerNote AS cn
ON c.ID
= cn.CustomerFK LEFT
OUTER
JOIN
(SELECT
CustomerFK, MAX(EST_MAILING_DATE) AS maxEMD, MAX(MAILED) AS maxMailed
FROM
dbo.Project
GROUP BY
CustomerFK) AS
pr ON c.ID = pr.CustomerFK
WHERE
(cn.SalesNote
LIKE
'%'
+
'sent'
+
'%')
The thing in parens is a derived table. Logically it is a temp table within the table, but it never materialized.
GRANT SELECT ON OBJECT::ExportCompany TO
FMExport
GRANT INSERT ON OBJECT::ExportCompany TO
FMExport
GRANT UPDATE
ON OBJECT::ExportCompany TO
FMExport
group by bands or ranges of numbers
SELECT '1K - 3500' AS
NumMailedRange, COUNT(ID) AS HowMany
FROM
dbo.Project AS p
WHERE (YEAR(MAILED) = 2011) AND (NUM_ MAILED BETWEEN 1000 AND 3500)
UNION
SELECT '3501 - 5500' AS
NumMailedRange, COUNT(ID) AS HowMany
FROM
dbo.Project AS
p
WHERE (YEAR(MAILED) = 2011) AND (NUM_ MAILED BETWEEN 3501 AND 5500)
select
which10MinuteSegment
=
STUFF(CONVERT(VARCHAR(5), dateadded, 10), 3, 1,
'-')
+
' '
+
cast(RIGHT(REPLICATE('0', 2)
+
CONVERT(varchar(2),DATEPART(HOUR,
dateadded)),2)
as
varchar(4))
+
':'
+
cast(DATEPART(minute, dateadded)/10 as
varchar(2))
+
'0',
howMany=count(id)
from
someDB..someTable
where
dateadded
between
DATEADD(hh,
- 6,
GETDATE())
and
getdate()
group
by
STUFF(CONVERT(VARCHAR(5), dateadded, 10), 3, 1,
'-')
+
' '
+
cast(RIGHT(REPLICATE('0', 2)
+
CONVERT(varchar(2),DATEPART(HOUR,
dateadded)),2)
as
varchar(4)) +
':' +
cast(DATEPART(minute, dateadded)/10
as
varchar(2)) + '0'
order
by
STUFF(CONVERT(VARCHAR(5), dateadded, 10), 3, 1,
'-')
+
' '
+
cast(RIGHT(REPLICATE('0', 2)
+ CONVERT(varchar(2),DATEPART(HOUR,
dateadded)),2) as
varchar(4))
+ ':'
+
cast(DATEPART(minute,
dateadded)/10 as
varchar(2))
+ '0'
select
count(id)
as total,
dateadd(DAY,0,
datediff(day,
0,
dateadded))
as added
from
dbo.someTable
where dateadded
>
'10/24/23'
group by
dateadd(DAY,
0,
datediff(day,
0,
dateadded))
order by added
to
- format to get month/day
- day of week and
- then sort
select
count(user_id)
as
total,
format(deleted, 'M/d dddd')
as
added
from
logDuoUsersDelete
group by
format(deleted,
'M/d dddd'),
dateadd(DAY,0,
datediff(day,
0, deleted))
the
“dateadd(hour,
datediff(hour,
0,
min(DateTime307))”
below truncates the minimum time’s minutes to get the beginning hour.
SELECT top 5
dateadd(hour,
datediff(hour,
0,
min(DateTime307)),
0)
date,
server,
sum(TotalPages)
TotalPages,
COUNT(*) TotalJobs
FROM jobs
where
DATEPART(Year,
DateTime307)
= 2023
and
DATEPART(Month,
DateTime307)
= 5
and
(DATEPART(Day,
DateTime307)
>
18)
GROUP BY
DATEPART(Year,
DateTime307),
DATEPART(Month,
DateTime307),
DATEPART(Day,
DateTime307),
DATEPART(Hour,
DateTime307),
server
ORDER BY
DATEPART(Year,
DateTime307),
DATEPART(Month,
DateTime307),
DATEPART(Day,
DateTime307),
DATEPART(Hour,
DateTime307),
server
select
DATEPART(MINUTE, datecreated), message,
UserName,
count(message) as
count
from
logexception
where
datecreated between '7/2/23 23:00' and '7/3/23 0:00'
and
eventType
=
'ERROR'<
group
by
EventType
,
message,
UserName,
DATEPART(MINUTE,
datecreated)
order
by
DATEPART(MINUTE,
datecreated),
count(message)
desc;
SELECT
month,
COUNT(*)
AS HowMany
FROM
(SELECT CONVERT(char(6), MAILED, 112) AS
month
FROM
Project)
AS a
GROUP
BY
month
or
use
printers
SELECT
COUNT(ID)
AS
total,
DATEADD(month,
DATEDIFF(month,
0, DateTime307),
0)
AS
month
FROM jobs
GROUP BY
DATEADD(month,
DATEDIFF(month,
0, DateTime307),
0)
ORDER BY
DATEADD(month,
DATEDIFF(month,
0, DateTime307),
0)
SELECT
COUNT(*)
AS
total,
YEAR(last_login)
AS
year
FROM users
GROUP
BY
YEAR(last_login)
ORDER
BY
year
desc
group by week – GROUP
BY DATEPART(WEEK,t1.timeStamp).
For an example of a stored
procedure emulating a cross-tab query.
group, trim number in each category of the group proportionally – see NTILE to trim each category proportionally
hello world – see display value
DECLARE @mostRecentPrintTime
AS DATETIME
DECLARE @oneHourAfterMostRecentPrintTime
AS DATETIME
select @mostRecentPrintTime
=
max(date)
from PrintHistory
select @oneHourAfterMostRecentPrintTime
=
DATEADD(hour,1,@mostRecentPrintTime)
whatever hour it is right now
SELECT DATEPART(HOUR, GETDATE());
whatever hour it is for an arbitrary time you have in a variable
hours, group by – see group by hours
hours, truncate time to most recent – see time, truncate to most recent hour, group by hours
hours, truncate minutes from a TimeDate to leave only hours – see truncate minutes from DateTime field to leave only hours, group by hours for an example of how this is useful, group by minutes time, truncate to most recent hour
how many years ago –
DATEDIFF(year
,
c.DATE_RECORD_ENTERED,
GETDATE()) AS NumberOfYears
ID, reset to start at 1 –
DBCC CHECKIDENT
('minions',
RESEED,
0)
This works if the table has alreadh had all its records deleted. If not, see How To Reset Identity Column Values In SQL
ID of SQL Server instance doesn’t match ID of database – this is common if you restore a database from another server. (You might also want to look at user already exists in current database (error 15023)) or especially local SQL server id, migrate to different server to avoid getting into this situation to begin with
run
exec sp_help_revlogin
from the master database of the server you which has the “good” ID you want to match in your new, destination db. SQL Server installs don’t automatically come with this “sp_help_revlogin” stored procedure out of the box. You can find it at Transfer logins and passwords between instances of SQL Server. Running the script will add “sp_help_revlogin” to the master database. Run this stored procedure on the server with the “good” ID. It should produce a bunch of entries that look something like this:
CREATE
LOGIN [userthatneedsfix]
WITH
PASSWORD = 0x0200C40E41234D4A35B33B5533FF3F7D075ED0D94433BDC1DB4E7A805467F578626401EE5E8E44B68B42DF5A23766AEC3B1FBDAFB24AFC52A5A5AD17C33E82F8D9D3FE07F1D2
HASHED,
SID= 0x79DA24905409248AA28A44982051A41F, DEFAULT_DATABASE
=
[master], CHECK_POLICY
=
OFF,
CHECK_EXPIRATION
= OFF
along with a whole bunch of other IDs. Copy this code to use on your destination database. Before you actually run this code, first delete the “bad” ID on your destination database first.
You might get a complaint if this particular user happens to own the database. This will especially be a problem if this user is the only owner of the database. In which case, run
ALTER AUTHORIZATION on database::yourdb to someotheruser
iif – change to CASE or if…else
Change MS Access
Select iif(field>10,"large", "small") as Size from Table
To SQL Server CASE command
SELECT
Size
=
CASE field
WHEN field > 10
THEN "large"
ELSE "small"
END
from Table
or change
Select IIf([PROOF_APPROVED]
Is Not Null
And [NAMES_RECEIVED] Is
Not
Null,1,0) AS Production
From Project
to
SELECT CASE
WHEN PROOF_APPROVED
IS
NOT
NULL
AND NAMES_RECEIVED IS
NOT
NULL
THEN 1
ELSE 0
END
AS Production
FROM
dbo.Project
if…else – note that there is no "then" – here
example:
if
exists(select * from Northwind.dbo.Customers
where CustomerId = 'ALFKI')
Print
'Need to update Customer Record ALFKI'
else
Print
'Need to add Customer Record ALFKI'
increment a field autmatically starting at the highest version already in that field (for select into)
select (SELECT MAX(ID)
FROM
ws_retailer)
+
ROW_NUMBER() OVER
(order
by
),
[Business Name]ess Name],
Address,
City,
State,
Zip,
Country,
Website
from
test123
where
id is
null;
index creation times out using GUI - make a script by right-clicking in white area, "Generate change script", just select the "Alter table … add constraint" part and run that.
This trick is from is from here
index, test to see whether it exists before dropping dropping
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_yourTable_companyCode') DROP INDEX IX_yourTable_companyCode ON yourTable
…and then to rebuild it:
CREATE NONCLUSTERED INDEX IX_yourTable_companyCode ON yourTable (companyCode)
indices - this seems to find only primary keys - and only deals with constraints
select t.TABLE_SCHEMA, t.TABLE_NAME,
case
objectproperty(object_id(t.TABLE_NAME), 'TableHasClustIndex')
when 1 then
'Has clustered index'
when 0
then
'Does not have clustered index'
end,
tc.CONSTRAINT_NAME
as
'PK Name',
kcu.COLUMN_NAME as
'PK column',
kcu.ORDINAL_POSITION
from
INFORMATION_SCHEMA.TABLES
as t
left outer join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
as tc
on (t.TABLE_SCHEMA = tc.TABLE_SCHEMA and
t.TABLE_NAME =
tc.TABLE_NAME and
tc.CONSTRAINT_TYPE
=
left outer join
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
as kcu
on
(tc.CONSTRAINT_NAME =
kcu.CONSTRAINT_NAME)
where
t.TABLE_TYPE
= 'BASE TABLE'
order by t.TABLE_SCHEMA, t.TABLE_NAME, kcu.ORDINAL_POSITION
inner join, delete
delete FirstTable
FROM SecondTable INNER
JOIN FirstTable
ON SecondTable.MaxID = FirstTable.ID
or (with a "where" clause)
DELETE
table1
from
table1
t
INNER
JOIN
table2
r
ON
r.RecordNumber =
t.testPkgId
and
(r.[New Name]
= N'bob')
insert into existing table
insert into Leads
(FirstName,Phone,eMail)
values
('Bob Smith','123456789','[email protected]')
insert into a new table
SELECT DISTINCT
ID, street,
apartment, city, state1, zip, zip4
INTO
RMCAddrOnly
FROM dbo.goodAddresses
Instr equivalent in SQL Server: CHARINDEX
change
SELECT Left([email],InStr([email],"@")-1) AS EmailPart
FROM SalesArt_Combined
to
SELECT LEFT(email, CHARINDEX('@', email) - 1) AS EmailPart
FROM SalesArt_Combined
integers only display in view even though you want some decimal values – see decimal, view doesn’t display after a calculation
invalid column name in SSMS when looking at a query (usually indicated by red line under column name) – press Ctrl+Shift+R to refresh intellisense
IP, on which IP is SQL running?
this gets port and IP
select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null
is Null equivalent - ISNULL(fieldA,0)
job alerts
Create a Database Mail profile - I
SELECT job_id, name,
enabled,
description, date_created, date_modified
FROM
msdb.dbo.sysjobs_view
select
s.name,l.name
from
msdb..sysjobs s
left
join
master.sys.syslogins l
on
s.owner_sid
=
l.sid
order by
l.name
jobs scheduled, list – see scheduled jobs, list
jobs, where located in SSMS – SQL Server Agent at the bottom of the main list for the server (at the same level as Databases, Security, etc.) → Jobs
if the icon has a red “X” in it and says “SQL Server Agent (Agent XPs disabled)” instead of having a green arrow and reading “SQL Server Agent”, then you won’t be able to expand that level to see the jobs.
You can check to make sure this is really the case. If you can’t see advanced options, show
EXEC sp_configure 'show advanced options', 1;
then verify
EXEC SP_CONFIGURE 'Agent XPs';
should return something like this
| name | minimum | maximum | config_value | run_value |
| Agent XPs | 0 | 1 | 1 | 1 |
If data above looks OK, try disconnect/reconnect database in SSMS
join, inner, delete
delete FirstTable
FROM
SecondTable INNER JOIN FirstTable
ON SecondTable.MaxID FirstTable.ID
join, outer full – see outer join, full
join tables from 2 different databases – both the databases have to be on the same server for this to work:
SELECT
new.doNotEMail,
old.doNotEmail
as
oldDoNotEmail,
new.WhyNotEmail,
old.whynotemail as
oldWhyNotEmail,
new.WhenDecidedNotEmail,
old.WhenDecidedNotEmail
as oldWhenDecidedNotEmail
FROM
dbo.Person as
new inner join
SC012412.dbo.Person
as old
on
new.ID
=
old.ID
where
new.doNotEMail
<>
old.doNotEMail
or
(new.doNotEMail
is null and
old.doNotEMail
is not null)
or
(new.doNotEMail
is not null
and
old.doNotEMail
is null)
here’s how to update:
UPDATE Person
SET WhyNotEmail
=
old.WhyNotEmail, doNotEMail
=
old.doNotEMail,
WhenDecidedNotEmail =
old.WhenDecidedNotEmail
FROM
SCold.dbo.Person old
INNER
JOIN Person new
ON
old.ID
=
new.ID
where
(new.doNotEMail
is null and
old.doNotEMail
is not null)
and
old.WhyNotEmail
=
'Blocked'
kill a process – see also activity monitor in SSMS, database processes, user processes, sp_who2, sp_whoisactive
find the spid of the process you need to kill. Let’s say it’s spid = 18. Then:
kill 18
kill connections to database, how to find which spids – see database processes, activity monitor in SSMS, sp_who2, sp_whoisactive, kill a process
kill connections by user, how to find which spids – see user processes, activity monitor in SSMS, sp_who2, sp_whoisactive, kill a process
key, restart numbering for (identity) -
dbcc checkident("YourTable",reseed,1)
last month’s stuff – see also month, last day of previous month, month, last day of current month, begin last month
SELECT CASE WHEN
Customer.FMO
=
'Bob'
THEN
'Bob'
ELSE
'not Bob'
EN AS
FMO,
COUNT(dbo.Project.ID)
AS ProjCount
FROM
dbo.Project INNER
JOIN
dbo.Customer
ON
dbo.Project.CustomerFK = dbo.Customer.ID
WHERE (dbo.Project.ACCOUNT_MANAGER
=
N'Sam')
AND (dbo.Project.MAILED between
dateadd(mm, -1, convert(datetime, convert(char(7), getdate(), 120) + '-01'))– the first day of last month
and
convert(datetime,
convert(char(7),
getdate(),
120) +
'-01'))
– first day of this month
GROUP
BY
CASE
WHEN Customer.FMO =
'Bob'
THEN
'Bob'
ELSE
'not Bob'
END
length of ntext – DataLength()
length of varchar – len()
length of a field, maximum -
select max(len(extension)) from Person
line numbers
Tools → Options → TextEditor → All Languages → Line Numbers check box available under Display section.
linked database, copy table to -
Here’s a few things that do NOT seem to work
If you DON'T have a table already on the remote server, when you try:
insert
into
[db0.global-id.colo].gidcrm_test.dbo.GCP_Temp_20130613b_FMExport
select
*
from
GCP_FMExport
you get:
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider SQLNCLI11 for linked server db0.something.colo does not contain the table gidcrm_test.dbo.GCP_Temp_20130613b_FMExport. The table either does not exist or the current user does not have permissions on that table.
list all views - SelectTABLE_NAMEFrom Information_Schema.Tables
where TABLE_TYPE =
'VIEW'
local server, cannot connect to
from here, from command line
dcomcnfg
From the window that pops up, services, computers, my computer, DCOM config, find the MsDtsServer100, right click to get “Properties, click the “Security” tab, click “Edit” in the “Launch” and Activate Permissions. Add the local SQL Agent User group and enable local launch and Local activation. I couldn’t get this to work because I couldn’t add “SQLAgent” or “SQL Agent”
Also tried futzing around in the SQL Server Configuration Manager. To no avail.
local SQL server id, migrate to different server
If you plan to migrate a database from one server to another, it’s best to re-create that ID on the server before you migrate to avoid
- log in of SQL Server instance doesn’t match log in of database
- match login of SQL Server instance to ID of database
- ID of SQL Server instance doesn’t match ID of database
list all the login IDs on the server pertaining to one database:
use starfleet
SELECT
name,
status,
issqluser,
isntgroup,
isntuser,
hasdbaccess
FROM
sysusers
where (status
in
(4,12)
and
name
<>
'dbo')
or
(status
= 0
and
hasdbaccess = 1
and
issqluser =
1)
order by status,
name
but all you need now are just the local server IDs:
use starfleet
SELECT
name
FROM
sysusers
where
issqluser = 1
and
hasdbaccess = 1
and
status
= 0
In SSMS, navigate to the Security section of the server as a whole (do not look for this the Security section of the database) → Users → right-click on the User → Script user as → CREATE to → Clipboard. Then paste into a query to get something like:
CREATE LOGIN [James-T-Kirk] WITH PASSWORD=N'xmnF0gexEWwEg/pNJSivK76Pcc3UcobQM+Pth6hI31M=', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
It’ll just make up a random password; you have to know the right password and type it in. Now if you migrate as normal, the user in the migrated database ought to match up with this new login and everything will be just fine!
log file location – see database, properties pertinant to migration
log file size – see database, properties pertinant to migration
error log
SELECT
SERVERPROPERTY('ErrorLogFileName')
AS
'Error log file location'
logged in, who am I logged in as?
SELECT SYSTEM_USER AS 'Login Name'
or
SELECT substring(SYSTEM_USER,CHARINDEX( '\', SYSTEM_USER)+1,len(SYSTEM_USER)) as log2
Configure Login Auditing (SQL Server Management Studio)
login, create Windows logins on a database server from text list
the text list should be an array. But apparently T-SQL doesn’t – have arrays. So, we’ll populate a temporary table and loop through that.
-- createServerWindowsLoginsFromList.sql
-- creates server-wide logins to allow users access to various databases
use
master
DECLARE
@counter INT
=
1,
@max
INT
=
0,
@thisGroupvarchar(
100)
=
null,
@cmd varchar(200)
-- Declare a variable of type TABLE. It will be used as a temporary table.
DECLARE
@tab TABLE
(
[Id] int
identity,
[groups]nvarchar(100)
)
-- Insert your required data in the variable of type TABLE.
Only the 2nd nvarchar field is populated because 1st int identity auto-filled
insert
into
@tab values('yourdomain\user-DBO'),
('yourdomain\user-PowerBI'),
('yourdomain\user-Web')
--
Initialize the @max variable. We’ll use thie variable in the next WHILE loop.
SELECT @max
=
COUNT(
ID)
FROM @tab
-- Loop
WHILE @counter
<= @max>
BEGIN
-- create each login
from value stored each row in your table variable filtering by the Id column
SELECT @thisGroup
= groups
FROM @tab
WHERE Id
= @counter
set cmd
=
'CREATE LOGIN ['+@thisGroup+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
EXEC
(@cmd)
SET @counter
=@counter
+ 1
END
log in of SQL Server instance doesn’t match log in of database – see
- ID of SQL Server instance doesn’t match ID of database.
- But you might also want to look at user already exists in current database (error 15023)
- or especially local SQL server id, migrate to different server to avoid getting into this situation to begin with
longest string in a column, find
SELECT
top
1 FMO,len(FMO) as len
FROM Customer
ORDER
BY
len DESC
loop through array – see also array, declare
below usually works
DECLARE
@currentid INT
=
1,
@max
INT
=
0,
@thisGroup varchar(100)
= null,
@cmd varchar(200)
-- Declare a variable of type TABLE.
It will be used as a temporary table.
DECLARE @tab
TABLE (
[Id] int identity,
[groups]
nvarchar(100)
)
insert into @tab
values('one'), ('two'), ('three')
-- Initialize the @max variable.
We’ll use this variable in the next WHILE loop.
SELECT @max
=
COUNT(ID)
FROM @tab
-- Loop
WHILE @currentid
<= @max
BEGIN
-- create each login from value stored
each row in your table variable filtering by the Id column
SELECT
@thisGroup = groups
FROM @tab
WHERE Id
= @currentid
-- EXECUTE THE SQL HERE
PRINT
'value #'
+
CONVERT(VARCHAR(4),
@currentid)
+
' = '
+
CONVERT(VARCHAR(4),
@currentid)
SET @currentid
= @currentid
+ 1
END
although occasionally code above has this error:
An explicit value for the identity column in table '@tab' can only be specified when a column list is used and IDENTITY_INSERT is ON.
when attempting to add array members as rows to a table whose first field is an identity which auto-increments.
If that occurs instead, below works without having to declare a temp table with an identity which auto-increment which then tries (and fails) to add rows
SET NOCOUNT ON
DECLARE @dict
TABLE
(id
INT
IDENTITY(1,1),
-- a unique identity column for reference later
value
VARCHAR(50),
-- your parameter
value to be passed into the procedure
executed BIT
)
-- BIT to mark a record as being executed later
-- INSERT YOUR VALUES INTO @dict HERE
-- Set executed to 0 (so that the execution process will
pick it up later)
-- This may be a SELECT statement into another table in
your database to load the values into @dict
INSERT @dict
SELECT
'one', 0
UNION
ALL
SELECT
'two', 0
UNION
ALL
select
'three', 0
DECLARE @currentid INT
DECLARE
@currentvalue
VARCHAR(50)
WHILE
EXISTS(SELECT
*
FROM @dict
WHERE executed
=
0)
BEGIN
-- Get the next record
to execute
SELECT
TOP
1 @currentid
=id
FROM
@dict
WHERE
executed = 0
-- Get the parameter value
SELECT
@currentvalue =
value
FROM @dict
WHERE
id = @currentid
-- EXECUTE THE SQL HERE
PRINT
'value #'
+
CONVERT(VARCHAR(4),
@currentid)
+
' = '
+ @currentvalue
-- Mark record as having been executed
UPDATE d
SET
executed= 1
FROM @dict d
WHERE
id = @currentid
END
loop through T-SQL Result Set – without using a cursor
Declare @au_id
Varchar(20)
Select @au_id
= Min(au_id)
from authors
While @au_id
IS NOT
NULL
Begin
Select au_id,
au_lname, au_fname from
authors Where au_id =
@au_id
Select @au_id
=
min(au_id) from authors where au_id >
@au_id
End
maintenance – see also tools
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance
The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, and SQL Server 2022. The solution is based on stored procedures. The solution has been designed for the most mission-critical environments, and it is used in many organizations around the world. The SQL Server Maintenance Solution is free.
SQL Server Backup script of SQL Server Maintenance Solution
match login of SQL Server instance to ID of database – see ID of SQL Server instance doesn’t match ID of database or perhaps user already exists in current database (error 15023) or especially local SQL server id, migrate to different server to avoid getting into this situation to begin with
matching records between tables – see unmatched
maximum length of a field - select
max(len(len(extension))
from Person
maximum of two fields in a table – see also fields, compare
SELECT TOP
(100) PERCENT CustomerFK, MAX(date) AS MaxDate
FROM (SELECT CustomerFK, MAILED AS date
FROM Project
UNION ALL
SELECT CustomerFK,
EST_MAILING_DATE AS date
FROM dbo.Project AS p2) AS CombProjDates
GROUP BY CustomerFK
ORDER BY CustomerFK
memo (ntext) field, group on – see group on an ntext (memo) field
memory, maximum amount SQL Server can use
SELECT
name,
value, value_in_use
FROM
sys.configurations
WHERE
name
=
'max server memory (MB)';
or
Select
*
From sys.configurations
Where
configuration_id
IN
(1543,1544)
If your server has 4 GB and you want to limit SQL Server to only use 80% of that to leave some left over for the OS
DECLARE
@MaxMemoryMB
INT;
SET
@MaxMemoryMB
=
4
*
.8
*
1024;
-- 3276 MB is 80% of 4 GB physical memory
select
@MaxMemoryMB
EXEC
sp_configure
'max server memory (MB)',
@MaxMemoryMB;
RECONFIGURE;
meta-data (description, fields and their data types), extract
SELECT
u.name
+
'.'
+
t.name
AS
[table],
td.value
AS [table_desc],
c.name
AS[column],
cd.value
AS [column_desc]
FROM
sysobjects t
INNER JOIN
sysusers u
ON
u.uid
=
t.uid
LEFT OUTER JOIN
sys.extended_properties td
ON
td.major_id
=
t.id
AND
td.minor_id
= 0
AND
td.name
=
'MS_Description'
INNER JOIN
syscolumns c
ON
c.id
=
t.id
LEFT OUTER JOIN
sys.extended_properties cd
ON
cd.major_id
=
c. id
AND
cd.minor_id
=
c.colid
AND
cd.name
=
'MS_Description'
WHERE
t.type
=
'u'
ORDER BY
t.name,
c.colorder
Mid (in Access) equivalent: SUBSTRING(expression, startposition, length)
migration, database properties pertinant – see database, properties pertinant to migration
minute, group by – see also group by minute, group by every 10 minutes
minutes, group by every 10 – see group by every 10 minutes
minutes, truncate to leave only hours – see truncate minutes from DateTime field to leave only hours, group by hours for an example of how this is useful
month, first day of current month –
SELECT
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),101)
AS Date_Value,
'First Day of Current Month'
AS Date_Type
month, first day of a year ago
select DATEADD(yy, - 1, CONVERT(datetime, CONVERT(char(7), GETDATE(), 120) + -01)) as 'First day of the month a year ago'
month, first day of next month –
SELECT
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))-1),DATEADD(mm,1,getdate())),101),
'First Day of Next Month'
month, group by – see also group by month
SELECT
month, COUNT(*) AS HowMany
FROM (SELECT CONVERT(char(6), MAILED, 112) AS month
FROM
Project)
AS a
GROUP BY month
month, last month’s stuff – see last month’s stuff
month, last day of current month –
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))),DATEADD(mm,1,getdate())),101),
'Last Day of Current Month'
– see also month, last day of previous month,
last month’s stuff,
begin last month
month, last day of previous month –
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())),getdate()),101), –
'Last Day of Previous Month'
– see also month, last day of current month,
last month’s stuff,
begin last month
But this actually gets midnight (the very beginning) of the last day of last month. If you’re trying to get right up to the end of the month (midnight of the first day of this month), might want to try the the somewhat convoluted:
selectDATEADD(dd, DATEDIFF(dd, 0,DATEADD(MONTH, DATEDIFF(MONTH, - 1, GETDATE()) - 1, - 1)), 1)
I’m sure there’s a more straightforward way to accomplish the same thing by not finding the last day of last month and then adding a day but instead just finding midnight of first day of this month…
month, subtract 3 months from today’s date -
DATEADD(m,
-
3,
GETDATE())
month crosstab – see Rozenshtein Method
most recent record for each person – see also maximum of two fields in a table
SELECT ID,
employeeFK,
statusDate, description
FROM statusReport AS T
WHERE
(statusDate
=
(SELECT
MAX(statusDate) AS MostRecent
FROM
dbo.statusReport
WHERE
(employeeFK
=
T.employeeFK)))
multi-user mode – see also single user mode
verify whether database is in single user or multi-user mode
SELECT
DATABASEPROPERTYEX('starfleet','UserAccess')
as whetherSingleOrMulti
set to multi-user mode
ALTER DATABASE starfleet
SET MULTI_USER
multiple databases, run same command across – see sp_MSforeachdb
MUST_CHANGE is on
USE
Master
GO
ALTER
LOGIN
UserName
WITH PASSWORD
=
'password'
GO
ALTER LOGIN
UserName
WITH
CHECK_POLICY
=
OFF,
CHECK_EXPIRATION
=
OFF;
network-related or instance-specific error occurred while establishing a connection to SQL Server - see error 10061
no records match between tables – see unmatched
now() – GETDATE()
ntext (memo) field, group on – see coalesce – a way to group on an ntext(memo) field
NTILE to trim each category proportionally
We have a list of 9708 people spread out across 32 zip codes. We want to shrink that down to 8000 people spread out across the same 32 zip codes, keeping roughly the same proportion of people in each zip code. There are 1425 people in the zip code with the greatest number of people and only one person in the zip code with the smallest number of people. So start with a view
WITH SlicedData AS
(
SELECTZIP,
INDIVIDUALNAME,
NTILE(36)
OVER
(PARTITION
BY Zip
ORDER BY
COUNT(INDIVIDUALNAME)
DESC
) AS
'Ntile'
FROM List
GROUP BY
ZIP, INDIVIDUALNAME)
SELECT ZIP, INDIVIDUALNAME
FROM SlicedData
WHERE
Ntile > 6
I had to fiddle with the numbers by trial and error ‘til I found the magic “35” as total number of slices and “6” as the number of slices we’re discarding that worked reasonably well in this case. In other words, we’re lopping off the top 6/35th records from each zip code. This final choice of “35” and “6” yields 8003 records over 29 zip codes – pretty darn close to our 8000 target.
I started out with “20” and “3” but changing the “3” around gave increments that were too big. For instance, “3” gave me 8204 records but “4” gave me 7709. This swing of several hundred results for each increment of the “Ntile” was too great.
Notice we lost 3 zip codes. The ones lost were those where there were only one or two people in that zip code. It probably lops off any zip codes with count of 6 or fewer addresses in this case (6/35 is about 1/6th). I also wasn’t able to get exactly 8000 records. I might have been able to get a number closer to 8000 by using a greater number of slices. But I suspect that would have “penalized” (got rid of entirely) more zip codes with numbers of addresses smaller than the number of slices. Or maybe not. But 8003 records over 29 zip codes was close enough for me so I stopped fiddling.
It seems if you save
this view (as “ListTop8000” in
this case), get out, and then re-open, it gets rid of the “WITH SlicedData AS” and changes it into something that
won’t run anymore:
SELECT ZIP, INDIVIDUALNAME,
NTILE(35)
OVER(PARTITION
BY Zip
ORDER BY
COUNT(INDIVIDUALNAME)
DESC)
AS
'NTile'
FROM List
GROUP BY
ZIP, INDIVIDUALNAME)
SELECT ZIP, INDIVIDUALNAME
FROM SlicedData
WHERE
NTile > 6
which, when run, complains:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'.
Strangely, though, when I included this saved query into
a join statement (‘cause I wanted more than just
“INDIVIDUALNAME”
but didn’t want to mess with grouping on all the other fields), it seemed to
work just fine:
SELECT
b.PREFIXTTL,
b.INDIVIDUALNAME,
b.FIRSTNAME,
b.MIDDLENAME,
b.LASTNAME,
b.ADDRESS,
b.ADDRESS2LINE,
b.CITY,
b.STATE,
b.ZIP,
RIGHT('0000'
+ RTRIM(b.ZIP4),
4)
AS zip4
FROM dbo.List
AS b INNER
JOIN
dbo.ListTop8000
AS
s ON
s.INDIVIDUALNAME
=
b.INDIVIDUALNAME
even though it complained when run on its own. So go figure.
What if we have 9-digit zip, but only want to sort on 5-digit zip?
WITH SlicedData
AS
(
SELECT SUBSTRING(ZIP, 1, 5)
AS zip2, VARHDR7,
NTILE(25)
OVER (PARTITION
BY
SUBSTRING(ZIP, 1, 5)
ORDER BY
COUNT(VARHDR7) DESC
)
AS
'Ntile'
FROM [032312BradsList]
GROUP
BY
SUBSTRING(ZIP,
1,
5), VARHDR7)
SELECT zip2, VARHDR7
FROM
SlicedData
WHERE
Ntile > 2
Null, is Null equivalent - ISNULL(fieldA,0).
Using a CASE statement:
Select
FirstName,
CASE
WHEN FirstName IS
NULL
THEN 'Agent'
ELSE FirstName
END
AS First
From Person
number rows – see rank
nz equivalent - ISNULL
offline, take database offline (and why it might seem to be taking forever)–
EXEC sp_dboption N'mydb', N'offline', N'true'
or
ALTER DATABASE [mydb]
SET OFFLINE
WITH
ROLLBACK AFTER 30 SECONDS
or
ALTER DATABASE [mydb]
SET OFFLINE
WITH
ROLLBACK IMMEDIATE
Using the alter database statement (SQL Server 2k and beyond) is the preferred method. The rollback after statement will force currently executing statements to rollback after N seconds. The default is to wait for all currently running transactions to complete and for the sessions to be terminated. Use the rollback immediate clause to rollback transactions immediately.
If, instead, you went through the front end to try to take it and you’re wondering why it’s taking forever, it’s because someone’s still logged on:
USE master
SELECT
* FROM
sys.sysprocesses
WHERE dbid
=
DB_ID('MyDB')
Find which spid is using the database:
EXEC sp_who2
Kill whichever spid it was. In this case, 72.
kill 72
discussions: ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions.
In short:
ORDER BY column1, colum2 asc OFFSET 0 ROWS
seems to work. Default sort appears to be desc
OU, extract from DistinguishedName using SQL – also see common name, extract from DistinguishedName
select
REPLACE(SUBSTRING(DistinguishedName,
CHARINDEX('OU=',
DistinguishedName)
+
3,
LEN(DistinguishedName)
-
CHARINDEX('OU=', DistinguishedName)
-
LEN(',DC=healthcare,DC=uiowa,DC=edu') -2), ',OU=',
'\')
as
OU
from
computers
Unfortunately, this still leaves OUs in reverse order. See delimited string, scalar function to reverse order of to reverse this order.
SQL Server can handle a full outer join (ANSI 92 standard syntax):
SELECT *
FROM employee
FULL OUTER JOIN
department
ON
employee.DepartmentID = department.DepartmentID
+-----------+--------------+----------------+--------------+
| LastName | DepartmentID | DepartmentName |
DepartmentID |
+-----------+--------------+----------------+--------------+
| Smith
| 34 |
Clerical
| 34 |
| Jones
| 33 |
Engineering
| 33 |
| Robinson
| 34 |
Clerical
| 34 |
| Jasper
| 36 |
NULL
| NULL |
| Steinberg
| 33 |
Engineering |
33 |
| Rafferty
| 31 |
Sales
| 31 |
| NULL
| NULL |
Marketing
| 35 |
+-----------+--------------+----------------+--------------+
If you’re using something like Access which can’t handle full outer joins, fudge it with a union of a right and left join:
SELECT
employee.LastName,
employee.DepartmentID,
department.DepartmentName,
department.DepartmentID
FROM employee
LEFT JOIN
department
ON
employee.DepartmentID = department.DepartmentID
UNION
SELECT
employee.LastName,
employee.DepartmentID,
department.DepartmentName,
department.DepartmentID
FROM employee
RIGHT JOIN
department
ON
employee.DepartmentID = department.DepartmentID
WHERE
employee.DepartmentID IS NULL
output from stored procedures, three common ways to return data from stored procedures: OUTPUTing variables, temp tables and the RETURN statement. Return:
declare @ReturnValue
char(20)
exec @ReturnValue
= todayTomorrowOrOther '9/17/23'
Select ReturnValue=@ReturnValue
owner of a database, change – see also database owners, list
USE pubs
EXEC sp_changedbowner
'John'
or
ALTER AUTHORIZATION ON DATABASE::starfleet TO sa
pad with zeros – here’s how to pad a zero as necessary to the left of a 1-digit day to get it to sort right
SELECT
right(replicate('0',2)+ convert(varchar(2),DATENAME(Day,theDate)),2) as theDay
FROM [Management-PMMileStones]
where DATENAME(MONTH,theDate)+ ' ' + DATENAME(YEAR,theDate) is not null
and DATENAME(YEAR,theDate) = '2014'
and DATENAME(MONTH,theDate) = 'February'
order by theDay
parent-child relationship fields inside table, reveal relationship among separate records in one row – sometimes within one table there can be designated child and parent ID fields
In this case, the Parent ID is ParentGroupID
and the Child ID is ChildGroupID.
If there are a series of rows which are all related by parent-child relationships, I want to reveal that relationship all on one display record. The example below shows this going up to 4 levels deep.
This is a real-life example from Microsoft’ WSUS SUSDB database.
SELECT
e.Name as greatGreatGrandParent
,d.Name as greatGrandParent
,c.Name as GrandParent
,b.Name as Parent
,a.Name as Child>
,ct.FullDomainName
,ct.IPAddress
FROM tbTargetGroup a
LEFT JOIN
tbTargetGroup b ON
a.ParentGroupID
=
b.TargetGroupID
-- parent
LEFT JOIN tbTargetGroup c
ON
b.ParentGroupID
=
c>.TargetGroupID
-- grandparent
LEFT JOIN tbTargetGroup d
ON
c.ParentGroupID
=
d.TargetGroupID
-- great-grandparent
LEFT JOIN tbTargetGroup e
ON
d.ParentGroupID
=
e.TargetGroupID
-- great-great-grandparent
join tbTargetInTargetGroup tintg
on
a.TargetGroupID
=
tintg. TargetGroupID
join
tbComputerTarget ct on
ct
order by
ct.FullDomainName,
e.TargetGroupID,
d.TargetGroupID,
c.TargetGroupID,
b.TargetGroupID,
a.TargetGroupID
performance monitoring
performance dashboard right-click server instance (up top) → Reports → Standard Reports → Performance DAshboard
permissions custom roles – see permissions assigned to custom roles
permissions, grant - see also grant permissions
permissions, grant to a group –
GRANT CREATE PROCEDURE TO [Domain\Group]
permissions, generate script to copy user permissions
- In SSMS, right Click on Database → Click Tasks → Generate Scripts… to pop window up.
- go to Choose Objects selection in left pane → select Select specific database objects radio button and check Users check box in right pane → Next button
- from the Set Scripting Options selection in left pane, click Advanced button and look for Script Object-Level Permissions under the first General section in the right pane and change that to True.
permissions, view – see also roles, view
What permissions does a user have on a database?
use SysAdmin
SELECT
dbprn.name
[User],
dbperm.state_desc
+
' '
+
dbperm.permission_name Permission
FROM
sys.database_permissions dbperm
JOIN
sys.database_principals
dbprn ON
dbperm.grantee_principal_id
=
dbprn.principal_id
WHERE
dbperm.class
= 0
ORDER BY User, Permission
If you’re not already logged in as this user and want to see what perms he has on a table:
EXECUTE AS USER
=
'someUser';
SELECT
*
FROM
fn_my_permissions('dbo.someTable',
'OBJECT')
ORDER BY
subentity_name,
permission_name;
for what the user logged in can do to all objects
completely useless, so far
select
sys.schemas.name
'Schema'
,
sys.objects.name Object
,
sys.database_principals.name username
,
sys.database_permissions.type permissions_type
,
sys.database_permissions.permission_name
,
sys.database_permissions.state permission_state
, sys.database_permissions.state_desc
,
state_desc
+
' '
+
permission_name
+
' on ['+
sys.schemas.name
+
'].['
+
sys.objects.name
+
'] to ['
+
sys.database_principals.name
+
']'
COLLATE
LATIN1_General_CI_AS
from
sys.database_permissions
join
sys.objects
on
sys.database_permissions.major_id
=
sys.objects.object_id
join
sys.schemas
on
sys.objects.schema_id
=
sys.schemas.schema_id
join
sys.database_principals on
sys.database_permissions.grantee_principal_id
=
sys.database_principals.principal_id
WHERE
sys.database_principals.name
=
'someUser'
order by 1, 2, 3, 5
port, on which port does SQL communicate? Default is 1433
this gets port and IP
select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null
processes used by database – see database processes, activity monitor in SSMS, sp_who2, sp_whoisactive, kill a process
processes used by user – see user processes, activity monitor in SSMS, sp_who2, sp_whoisactive, kill a process
D:\x64\DefaultSetup.ini
proportionally trim each category within a group – see NTILE to trim each category proportionally
queries, show all - Select TABLE_NAME
From Information_Schema.Tables
where TABLE_TYPE =
'VIEW'
ALTER DATABASE yourDB
SET QUERY_STORE
=
ON
(OPERATION_MODE
=
READ_WRITE,
MAX_STORAGE_SIZE_MB
=
1024,
QUERY_CAPTURE_MODE
=
AUTO)
query store, get options and status
SELECT
current_storage_size_mb,
max_storage_size_mb,
actual_state_desc,
readonly_reason,
stale_query_threshold_days,
size_based_cleanup_mode_desc
FROM
sys.database_query_store_options
quotes, single, enclose inside of single quotes – use two of ‘em:
SELECT COMPANY_NAME FROM Customer WHERE
Affiliation = N'Sam''s
Club'
random selection of rows from a table – more here
SELECT TOP 10 * FROM Testimonials ORDER By NEWID()
rank – see also dense rank
SELECT TOP
(50)
COUNT(p1.CustomerFK)
AS
NumMailings, c1.COMPANY_NAME,
MAX(p1.MAILED)
AS
MostRecentMailed,
MAX(p1.EST_MAILING_DATE)
AS
MostRecentEMD,
p1.CustomerFK,
f.Restricted,
Row_Number()
OVER
(ORDER BY
COUNT(p1.CustomerFK)
DESC)
AS RunningCount
FROM
dbo.Customer
AS
c1 INNER
JOIN
dbo.Project AS p1 ON
c1.ID
= p1.CustomerFK
LEFT OUTER JOIN
dbo.FMO AS fON
c1.FMO
= f.FMO
GROUP BY
c1.COMPANY_NAME,
p1.CustomerFK,
f.Restricted
HAVING
(f.Restricted
IS
NULL
OR f.Restricted
=
0)
ORDER BY NumMailings
DESC
What if you want a bunch of categories, each ordered?
SELECT ROW_NUMBER()
OVER
(PARTITION
BY AcctMgr
ORDER BY
HowMany DESC)
AS row, AcctMgr, Newest, HowMany, COMPANY_NAME
FROM CUSTOMER
Below, we list all Active Directory users with Duo subscriptions,
if any (hence the left join), and then list any phones associated with the
Duo subscription, if any (again, hence the left join), and then find the most
recent activity (“last_seen”).
But we also want to get the phone number and activation status for whatever
phone record was last seen. So, we go over ROW_NUMBER()
partitioned by samAccountName ordered by “last_seen” descending.
WITH RankedPhones
AS
(
SELECT
OU,
sAMAccountName,
DisplayName,
telephoneNumber,
EmailAddress,
usrUIC.Created as
ADcreated,
Enabled,
lastLogonDate,
duopr.is_enrolled,
duopr.status,
duopr.created,
duopr.last_login,
phpr.last_seen,
duopr.phoneCount,
phpr.number,
phpr.activated,
ROW_NUMBER()
OVER
(PARTITION BY
usrUIC.sAMAccountName
ORDER BY
phpr.last_seen
DESC)
AS rn_phpr>
FROM
usersADUIHealthServices usrUIC
LEFT JOIN
DuoUsers_prod duopr
ON
duopr.username
=
usrUIC.sAMAccountName
LEFT JOIN (
span
style=color:blue>SELECT
username,
number,
activated,
last_seen
FROM
DuoPhones_prod
)
AS
phpr ON
duopr.username
=
phpr.username
)
SELECT
OU,
sAMAccountName,
DisplayName,
telephoneNumber,
EmailAddress,
ADcreated,
Enabled,
lastLogonDate,
is_enrolled,
status,
created,
last_login,
last_seen,
phoneCount,
number,
activated
FROM RankedPhones
WHERE rn_phpr
= 1
ORDER BY
OU,
DisplayName;
This is how we get the phone number and activation status associated with the most recent last_seen for each user who has a phone in the Duo subscription.
records, default maximum returned – see also Access records, default maximum returned
records with no matches in related table – see unmatched
recovery model of database – see database recovery model, show
rename database - from here
EXEC master..sp_renamedb 'CoreDB','ProductsDB'
Once the above T-SQL has executed successfully the database name will change however the Logical Name and File Name will not change. You can verify this by executing the T-SQL below:
USE master
GO
/* Identify Database File Names */
SELECT
name
AS
[Logical Name],
physical_name
AS
[DB File Path],
type_desc
AS
[File Type],
state_desc
AS
[State]
FROM
sys.master_files
WHERE
database_id = DB_ID(N'ProductsDB')
GO
follow the below steps which will not only rename the database, but at the same time will also rename the Logical Name and File Name of the database. This first set of commands put the database in single user mode and also modifies the logical names.
/* Set Database as a Single User */
ALTER DATABASE CoreDB
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
/* Change Logical File Name */
ALTER DATABASE
[CoreDB]
MODIFY FILE
(NAME=N'CoreDB',
NEWNAME=N'ProductsDB')
GO
ALTER DATABASE [CoreDB]
MODIFY FILE
(NAME=N'CoreDB_log',
NEWNAME=N'ProductsDB_log')
Now we need to detach the database, so we can rename the physical files. If the database files are open you will not be able to rename the files.
/* Detach Current Database */
USE [master]
GO
EXEC
master.dbo.sp_detach_db
@dbname =
N'CoreDB'
GO
Once xp_cmdshell is enabled you can use the below script to rename the physical files of the database.
/* Rename Physical Files */
USE
[master]
GO
EXEC
xp_cmdshell
'RENAME "C:\Program Files\Microsoft SQL
ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB.mdf", "ProductsDB.mdf"'
GO
EXEC
xp_cmdshell
'RENAME "C:\Program Files\Microsoft SQL
ServerMSSQL10.SQL2008\MSSQL\DATA\CoreDB_log.ldf",
"ProductsDB_log.ldf"'
GO
Once the above step has successfully executed then the next step will be to attach the database, this can be done by executing the T-SQL below:
/* Attach Renamed ProductsDB Database Online */
USE
[master]
GO
CREATE
DATABASE
ProductsDB
ON
(FILENAME=
N'C:\Program Files\Microsoft SQL
Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB.mdf'
),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\ProductsDB_log.ldf'
)
FOR ATTACH
GO
restore database
Step 1: Retrieve the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK =
'D:\BackUpYourDB.bak'
GO
Step 2: Use the values in the LogicalName Column in following Step.
----Change Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER
WITH
ROLLBACK IMMEDIATE
RESTORE DATABASE YourDB
FROM DISK =
'D:\BackUpYourDB.bak'
WITH MOVE
'YourMDFLogicalName'
TO
'D:\DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName'
TO
'D:\DataYourLDFFile.mdf'
/*If there is no error in statement
before database will be in multiuser mode.
If error occurs please execute following
command it will convert
database in multi user.*/
ALTER DATABASE YourDB
SET MULTI_USER
GO
restoring, database stuck in this state
RESTORE DATABASE YourDB WITH RECOVERY
restore directory, default - Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup
reverse order of an array, see – delimited string, scalar function to reverse order of to reverse the order of a delimited string.
roles, permissions assigned to custom roles – see permissions assigned to custom roles
roles, view – see also permissions, view
use SysAdmin
select
dp.Name
userID,
dp2.Name
role
from
sys.database_principals dp
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
round time to most recent minute – see time, truncate to most recent minute, group by minutes
round time down to most recent hour – see time, truncate to most recent hour, group by hours
row numbers – see rank
check if default Windows Integrated Security Only or
SQL Server and Windows Authentication both allowed.
(need both)
SELECT
CASE
SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN
1
THEN
'Windows Authentication Only'
WHEN
0
THEN
'SQL Server and Windows Authentication'
ELSE
'Unknown'
END
AS
AuthenticationMode;
check if sa is disabled
SELECT
name,
is_disabled,
LOGINPROPERTY(name,
'IsLocked')
AS IsLocked,
LOGINPROPERTY(name,
'LockoutTime')
AS
LockoutTime
FROM
sys.sql_logins
WHERE
name
=
'sa';
Saving changes is not permitted. The changes that you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.
To change the Prevent saving changes that require the table re-creation option, follow these steps:
- Open SQL Server Management Studio (SSMS).
- On the Tools menu, click Options.
- In the navigation pane of the Options window, click Designers and then Table and Database Designers.
- Select or clear the Prevent saving changes that require the table re-creation check box, and then click OK.
Note If you disable this option, you are not warned when you save the table that the changes that you made have changed the metadata structure of the table. In this case, data loss may occur when you save the table.
To create:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER
GO
create FUNCTION
[dbo].[todayTomorrowOrOther]
(@dteDateInQuestion datetime)
RETURNS char(20)
AS
BEGIN
DECLARE @ReturnValue
char
(20)
set @ReturnValue
=
CASE
WHEN
(datediff(d,
getDate(),
@dteDateInQuestion))
< 0
THEN
' late'
WHEN
(datediff(d,
getDate(),
@dteDateInQuestion))
= 0 THEN
'0 - today'
WHEN
(datediff(d,
getDate(),
@dteDateInQuestion))
= 1
THEN
'1 - tomorrow'
WHEN
(datediff(d,
getDate(),
@dteDateInQuestion))
= 2
THEN
'2 days'
WHEN
(datediff(d,
getDate(),
@dteDateInQuestion))
between 2
AND 7
THEN
'3-6 days'
WHEN
(datediff(d,
getDate(),
@dteDateInQuestion))
> 6
THEN
'week or more'
ELSE
'undetermined'
END
RETURN @ReturnValue
END
To use
SELECT dbo.todayTomorrowOrOther(EST_MAILING_DATE)
AS EMD
dbo.Project
SELECT
jobs.name
AS
JobName, schedules.name
AS
ScheduleName, schedules.freq_type, schedules.freq_interval, schedules.active_start_time
FROM
msdb.dbo.sysjobs
AS
jobs
INNER JOIN
msdb.dbo.sysjobschedules
AS
jobschedules
ON
jobs.job_id
=
jobschedules.job_id
INNER JOIN
msdb.dbo.sysschedules
AS
schedules
ON
jobschedules.schedule_id
=
schedules.schedule_id
USE starfleet
SELECT
schema_name, schema_owner
FROM
information_schema.schemata
append this to filter out defaults
where schema_name not in ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter')
schemas, what tables belong to them
SELECT
TABLE_SCHEMA, TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE
IN ('BASE TABLE',
'VIEW')
or this for same results:
SELECT
S.name AS
SchemaName,
O.name
AS
ObjectName,
CASE
O.type WHEN
'U'
THEN
'TABLE'
ELSE
'VIEW'
END AS ObjectType
FROM
sys.objects O
INNER JOIN
sys.schemas
S ON
O.schema_id
=
S.schema_id
WHERE
O.type
IN
('U',
'V')
columns in tables:
select.name
as
column_name,
t.name
as
table_name ,
s.name
as
schema_name,
USER_NAME(s.principal_id)
AS Schema_Owner
from
sys.columns c
inner join
sys.tables
t on
c.object_id=t.object_id
inner join
sys.schemas
s on
t.[schema_id]
=
s.[schema_id]
select, grant permissions to a group\user to a table –
GRANT SELECT ON
SysComments to [group\user]
server, from where SQL runs – see computer, from where SQL runs
server, instances of SQL – see computer, instances of SQL
show all views - Select TABLE_NAME
From
Information_Schema.Tables
where TABLE_TYPE =
'VIEW'
shrink each category within a group proportionally – see NTILE to trim each category proportionally
siblings, find (see also update inner join with sub-select) – for this example we find siblings where one sibling has one field (State, in this case) filled out all the other siblings which have that same field missing
select distinct
ProjectFK, state
from RestaurSeminar as r1
where r1.ProjectFK
in
(select r2.ProjectFK
from RestaurSeminar as r2
where ((select count(distinct r3.state) from RestaurSeminar
as r3 -- 1. at least
one record has state
where r2.ProjectFK
= r3.ProjectFK
-- filled in. Can be more, but state
and r3.state is not null and r3.Restaurant is not null) =1) -- must be the same
and ((select count(*) from RestaurSeminar as r4
where r2.ProjectFK
= r4.ProjectFK
-- 2. at least one other record
and r4.state is null and r4.Restaurant is not null) >0)
-- with state field missing
and ((select count(distinct r5.Restaurant) from RestaurSeminar
as r5
where r2.ProjectFK
= r5.ProjectFK) =1))
-- 3. at least 1 restaurant field filled
and r1.state is not null
show size of all tables - see tables, show size of all, disk space used by each object in a database
single quotes, enclose inside of single quotes – use two of ‘em:
SELECT COMPANY_NAME FROM Customer WHERE Affiliation = N'Sam''s Club'
single user mode – see also multi-user mode
verify whether database is in single user or multi-user mode
SELECT
DATABASEPROPERTYEX('starfleet','UserAccess')
as whetherSingleOrMulti
set to single-user mode
ALTER DATABASE IdAM_Mobility
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE IdAM_Mobility
SET SINGLE_USER WITH ROLLBACK AFTER 30
ALTER DATABASE IdAM_Mobility
SET
SINGLE_USER WITH NO_WAIT
size of database – see database, properties pertinant to migration
sp_MSforeachdb to run same command on all SQL Server databases without cursors – see Run same command on all SQL Server databases without cursors
see sp_ineachdb alternative
sp_who2 – list all process IDs (spids) used by users to connect to databases
somewhat tedious to scroll through looking for users or databases. That’s why – see also activity monitor in SSMS, database processes, sp_whoisactive, user processes, kill a process
spids used by database – see database processes, activity monitor in SSMS, sp_who2, sp_whoisactive, kill a process
spids used by user – see user processes, activity monitor in SSMS, sp_who2, sp_whoisactive, kill a process
space, trim – all SQL server seems to have are LTRIM() and RTRIM(). Pretty worthless, eh? Can’t get rid of tabs, carriage returns, etc. So, create a custom function
create function
dbo.UDF_Trim(@input varchar(8000)) returns varchar(8000) as
begin
declare @charstotrim varchar(100)
select @charstotrim ='’'+
'‘'+char(9)+char(10)+char(13)
while charindex(left(reverse(@input),1),@charstotrim) >0
select @input=left(@input,datalength(@input)-1)
while
charindex(left(@input,1),@charstotrim) >0
select @input=substring(@input,2,datalength(@input)-1)
return @input
end
special characters, list
DECLARE @i int
SET @i =0
WHILE @i < 255
BEGIN
PRINT CONVERT(varchar, @i) + ' - >' + CHAR(@i) + '<'
SET @i = @i + 1
END
special characters, strip out. Things like CRLF, for instance.
UPDATE Leads
SET eMail = REPLACE(eMail, SUBSTRING(eMail, PATINDEX('%[^a-zA-Z0-9@. '''''']%', eMail), 1), '')
WHERE PATINDEX('%[^a-zA-Z0-9@. '''''']%', eMail) <> 0
Will take out special characters from email except single quote, @ sign. Not sure this takes out all unprintable characters.
split a string based on the last instance of a character –
This is helpful if you want to get the file name at the end of a list of files that also have the path included. In this case, you want everything after the "\" symbol.
SELECT
REVERSE(SUBSTRING(REVERSE(fullName),0,CHARINDEX('\',REVERSE(fullName))))
as fileName from
UploadFiles -- gets Name
SELECTSUBSTRING(fullName,0,len(fullName)
-
CHARINDEX('\',REVERSE(fullName))+1)
as path from
UploadFiles -- gets Path
split a string based on a space –
substring(project_name,charindex(' ',project_name)+1,len(project_name))
gets last half of string separated by space
substring(project_name,1,charindex(' ',project_name)-1)
gets 1st half of string separated by space, but problems if no space present at all
(CASE
CHARINDEX(' ', FirstName)
WHEN 0
THEN
FirstName
ELSE
SUBSTRING(FirstName, 1,
CHARINDEX(' ', FirstName))
END)
as FirstPart
gets beginning of string before a space if there is a space or just the string itself if there is no space
SQL Server Agent, permissions to see
use msdb
EXECUTE sp_addrolemember
@rolename
=
'SQLAgentReaderRole',
@membername
=
'domain\user'
(need to use msdb) or
exec sp_addrolemember 'SQLAgentUserRole', 'domain\user'
may first need to
exec sp_adduser 'domain\user', 'domain\user', 'public'
SQL Server Express Utility – SSEUtil is a tool that lets you easily interact with SQL Server. Download.
Briefly, type in sqlcmd at the command prompt.
This will give you a line number like 1>. Type in how many ever lines you want.
For example, type, select @@servername followed by return.
Type go at the next line. Exit by typing exit followed by return.
navigate to
C:\Program Files (x86)\Microsoft SQL Server Management Studio 19\Common7\IDE\ssms.pkgundef,
search for “// Remove Dark theme”, comment out line below which might look something like
“[$RootKey$\Themes\{1ded0138-47ce-435e-84ef-9ec1f439b749}]”
by putting “//” in front of it. Restart SSMS to take effect. Then from menu, go to
Tools → Options → Environment → General → Color theme. Before, that pick list did not
include “Dark” but now it should.
stop processing – return command
stored procedure, call from within select statement – you can’t, but you can create a scalar function and that’ll probably do what you need anyway
stored procedure, can’t edit because you get a Syntax error in TextHeader of StoredProcedure error
sp_helptext N'spYourProcName'
so you can at least see the dang thing
stored procedure, find a string among all stored procedures
SELECT
OBJECT_NAME(id)
FROM SYSCOMMENTS
WHERE [text]
LIKE '%someString%'
AND
OBJECTPROPERTY(id, 'IsProcedure')
= 1
GROUP BY
OBJECT_NAME(id)
stored procedure, grant permissions to create to a group - GRANT CREATE PROCEDURE TO
[Domain\Group]
stored procedure output – see output from stored procedures
stored procedure results, feed into another stored procedure – from here:
So long as the stored procedure produces only a single result, the technique for using the output of one stored procedure in another is pretty straightforward. The technique is to use a temporary table to hold the results of the stored procedure and an INSERT EXEC statement to execute the stored procedure and save the results. Once the results are in the temporary table they can be used like any other table data.
Here’s an example procedure that we might like to reuse:
CREATE PROC
usp_Demo_AllAuthors as
select
*
from pubs..authors
GO
Now here’s a stored procedure that uses the results of usp_Demo_AllAuthors:
CREATE proc
usp_Demo_SPUser as
CREATE TABLE #Authors (
au_id varchar(11) NOT
NULL PRIMARY
KEY CLUSTERED,
au_lname varchar
(40)
NOT
NULL,
au_fname varchar
(20) NOT
NULL,
phone char
(12)
NOT
NULL,
address varchar
(40) NULL,
city varchar
(20) NULL,
state char
(2) NULL,
zip char (5)
NULL,
contract bit
NOT NULL
)
-- Execute usp_Demo_AllAuthors storing the
-- results in #Authors
insert into #Authors
exec usp_Demo_AllAuthors
-- Here we use the #Authors table. This example only
-- only selects from the temp table but you could do much
-- more such as use a cursor on the table or join with
-- other data.
SELECT au_fName +
' '
+ au_lname
as [name]
, address+', '+city+', '+state+' '+zip [Addr]
from #Authors
DROP TABLE #Authors
GO
stored procedure, schedule – in SSMS → right-click on SQL Server Agent → New Job
string, find location of in a bigger string - CHARINDEX - SELECT SYSTEM_USER
AS
'Login Name',
CHARINDEX(
'\',
SYSTEM_USER)
AS
backslash_position
strip blanks – ltrim(rtrim(@TempValue))
SUBSTRING(expression, start, length)
string, find longest in a column – see longest string in a column, find
table changes 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.
SELECT DISTINCT
RestaurSeminar.Restaurant, RestaurSeminar.City, RestaurSeminar.State
INTO Restaurant
FROM RestaurSeminar;
table date created – see tables, when created?
table fields, list – see also field counts for tables, tables with a certain number of records
SELECT
c.name
'Column Name',
t.Name
'Data type', c.max_length 'Max Length',
c.precision,
c.scale,
c.is_nullable,
ISNULL(i.is_primary_key, 0)
'Primary Key'
FROM
sys.columns
c
INNER
JOIN
sys.types t
ON
c.system_type_id = t.system_type_id
LEFT OUTER JOIN
sys.index_columns ic
ON
ic.object_id
=
c.object_id
AND
ic.column_id
=
c.column_id
LEFT OUTER JOIN
sys.indexes
i
ON
ic.object_id
=
i.object_id AND
ic.index_id
= i.index_id
WHERE
c.object_id
=
OBJECT_ID('invoice')
table, grant permissions to
– GRANT
SELECT
ON SysComments
to
[group\user]
table info, display – Select
*
From
Information_Schema.Columns
Where Table_Name =
'Customer'
tables from 2 different databases, join – see join tables from 2 different databases
table records with no matches in related table – see unmatched
tables with a certain number of records – see also field counts for tables, table fields, list
example for 1 or 2 records
DECLARE
@TableName
NVARCHAR(255)
DECLARE
@SQL
NVARCHAR(MAX)
-- Create a temporary table to store the results
CREATE TABLE
#TableCounts ([TableName]
NVARCHAR(255), [RowCount]
INT)
-- Cursor to iterate through all user tables
DECLARE
TableCursor
CURSOR
FOR
SELECT
TABLE_NAME
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE
=
BASE TABLE
OPEN
TableCursor
FETCH NEXT FROM
TableCursor
INTO
@TableName
WHILE
@@FETCH_STATUS
=
0
BEGIN
-- Construct the SQL to count rows in each table
SET
@SQL
=
INSERT INTO #TableCounts ([TableName], [RowCount]) SELECT '''
+
''' @TableName +
, COUNT(*) FROM
+
@TableName
EXEC
sp_executesql @SQL
FETCH NEXT FROM
TableCursor
INTO
@TableName
END
CLOSE
TableCursor
DEALLOCATE
TableCursor
-- Select tables with 1 or 2 rows
SELECT
[TableName], [RowCount]
FROM
#TableCounts
WHERE
[RowCount]
<=
2
and
[RowCount]
>
0
order by
[RowCount]
desc, TableName
-- Clean up
DROP
TABLE #TableCounts
tables, show all –
Select TABLE_NAME
From
Information_Schema.Tables
where
TABLE_TYPE =
'BASE TABLE'
order by
TABLE_NAME asc
Or
SELECT
[TableName] = so.name, [RowCount] =
MAX(si.rows)
FROM sysobjects so, sysindexes si
WHERE
so.xtype
=
'U'
AND si.id
=
OBJECT_ID(so.name)
AND
si.rows
> 0
GROUP BY so.name
ORDER BY 2 ASC
tables, show size of all in a database – see also disk space used by each object in a database
or in SSMS highlight the table and View → Object Explorer Details (F7) → Double-click Tables → right-click on an empty area up top where the headings are → show “Data Space Used (KB)” and “Index Space Used (KB)”
use starfleet
SELECT
t.
NAME
AS
TableName,
s.Name
AS
SchemaName,
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
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
t.Name
SELECT
[name],
create_date, modify_date
FROM
sys.tables
older than 4 days ago
SELECT name FROM
sys.tables
WHERE name
LIKE
'usersAD%'
AND create_date
<
DATEADD(day,
-4,
GETDATE());
take database offline is taking too long - try the following code from a command prompt instead
ALTER DATABASE yourdb SET OFFLINE WITH ROLLBACK IMMEDIATE
You might get an error message complaining that you don’t have exclusive use of the database: ALTER DATABASE failed because a lock could not be placed on database to solve that, see ALTER DATABASE failed because a lock could not be placed on database
temporary databases, how many?
SELECT
file_id,
type_desc,
name, physical_name,
size, state_desc
FROM
tempdb.sys.database_files
WHERE
type_desc
=
ROWS
The number in the size column represents the size in 8 KB pages. So, if you see a value of 1024, it means:
1024 pages x 8 KB/page=8192 KB
If you already have two and want to add a third:
ALTER
DATABASE
[tempdb]
ADD
FILE
(NAME
=
Ntemp3,
FILENAME
=
NF:\MSSQL16.MSSQLSERVER\MSSQL\DATA\tempdb3.ndf,
SIZE
=
8192KB,
FILEGROWTH
=
8192KB);
to delete:
DBCC SHRINKFILE (N'temp3', EMPTYFILE);
ALTER
DATABASE
[tempdb]
REMOVE
FILE
[temp3];
time tables were created – see tables, when created?
time, truncate off datetime to get just date – see truncate datetime to date,
time, truncate to most recent hour, group by minutes
time, truncate to most recent hour – see also group by hours, group by minutes
declare
@dt datetime
set
@dt =
'09-22-2023 15:07:38.850'
dateadd(hour,
datediff(hour,
0,
@dt),
0)
time, truncate to most recent minute – see also group by minutes, group by hours
declare
@dt datetime
set
@dt =
'09-22-2023 15:07:38.850'
select
dateadd(mi,
datediff(mi,
0,
@dt),
0)
today, beginning of today –
DATEADD(dd,
DATEDIFF(dd, 0,
GETDATE()), 0)
tools – see also maintenance
SQLFacts - A powerful suite of FREE tools for SQL Server database professionals.
tomorrow, beginning of tomorrow –
DATEADD(dd,
DATEDIFF(dd, 0, GETDATE()), 1)
transfer logins and passwords between instances of SQL Server - sp_help_revlogin
transpose –
use the PIVOT command
RAC - The Relational Application Companion 3rd party tool
How to rotate a table in SQL Server
trim each category within a group proportionally – see NTILE to trim each category proportionally
trim space– see space, trim
select CAST(GETDATE()as Date)
truncate time to most recent hour – see time, truncate to most recent hour, group by hours
truncate time to most recent minute – see time, truncate to most recent minute
truncate minutes from DateTime field to leave only hours –
select
dateadd(hour,
datediff(hour, 0,
'05/25/23 7:45 pm'), 0)
see group by hours for an example of how this is useful
see also time, truncate to most recent hour
union
SELECT
au_lname FROM authors
UNION ALL
SELECTlname
FROM Employee
ORDER
BY au_lname
This will return all the records in the AUTHORS table and then all the records in the EMPLOLYEE table in one result set. The UNION will remove duplicates from the result set by default. You can use the UNION ALL to keep the duplicates in the result set.
The ORDER BY clause applies to the entire result. You can only have one ORDER BY clause in a UNION query and it must be part of the last SELECT statement. The first SELECT statement defines the column names. I usually try to alias all my columns to the same name for easier reading.
Sometimes we want all records which do not match.
In this example, we have Duo MFA user records which ought to match at least
one record in either of two Active Directory domains whose Duo
username field should match samAccountName field In
either of domains AD1 or AD2.
Find Duo records with no matches in either domain.
here is the “traditional” way which is slower:
-- slower
SELECT username
FROM DuoUsers_prod duo
LEFT JOIN usersAD1 AD1
ON
duo.username
=
AD1.sAMAccountName
LEFT JOIN usersAD2 AD2
ON duo.username
= AD2.sAMAccountName
where
AD1.sAMAccountName
is
null
and
AD.samaccountName
is
null
this is faster:
-- faster
SELECT username
FROM
(
-- begin subselect...
SELECT
username,
CASE
WHEN
usersAD1.sAMAccountName
=
duo.username
OR
usersAD2.sAMAccountName
=
duo.username>
THEN 1
ELSE 0
END AS matches_either_domain
-- ...to include this field...
FROM DuoUsers duo
LEFT JOIN
usersAD1 AD1 ON
duo.username
=
AD1.sAMAccountName
LEFT JOIN
usersAD2 AD2
ON
duo.username
=
AD2.sAMAccountName
)
AS subquery
-- ...inside the subquery...
WHERE
matches_either_domain = 0
--- so we can filter
on it; if we don’t put this field inside the subquery before filtering, error:
Invalid column name 'matches_either_domain'.
update example –
update Customer
set MagTitle
=
'Rolling Stone'
where
MagTitle =
'RS'
or
update
table1
set
a=t2.a, b=t2.b
from
table2 t2
where
table1.id=table2.id
This works in MS Access but not in SQL Server:
update TableOne
inner join
TableTwo on
TableOne.commonID
=
TableTwo.commonID
set TableOne.field1 = TableTwo.field2
or this also works in MS Access but not in SQL Server (better for when you have extra “and” restrictions you need to add to the “where”):
update
TableOne, TableTwo
set
TableOne.field1
=
TableTwo.field2
where
TableOne.commonID
=
TableTwo.commonID
this works in SQL Server but not in MS Access
UPDATE
source
SET
NewCustID = u.NewCustID
FROM
UniqueNewCustomers u
INNER JOIN source
s
ON u.MinOfID
= s.ID
or on two tables from 2 different databases where “SCOld” is the “foreign” database:
UPDATE
Person
SET
WhyNotEmail
= old.WhyNotEmail, doNotEMail=old.doNotEMail,
WhenDecidedNotEmail=old.WhenDecidedNotEmail
FROM
SCold.dbo.Person old INNER JOIN Person new
ON old.ID
=
new.ID
where
(new.doNotEMail
is null and
old.doNotEMail is
not
null)
and
old.WhyNotEmail
=
'Blocked'
I had heard somewhere this supposedly works in SQL Server. But it didn’t work for me.
update tableOne<
set
tableOne.field1=tableTwo.field2
from
table One, tableTwo
where tableOne.commonID=tableTwo.commonID
update inner join with sub-select. Here’s an example where we find siblings where one sibling has one field (State, in this case) filled out and we want to assign that same value to all its siblings which have that same field missing
UPDATE r
SET
r.State
=
r0.State
from RestaurSeminar
as
r,
(
select distinct
ProjectFK,
state
from RestaurSeminar
as r1
where ProjectFK
in
(select
r2.ProjectFK
from RestaurSeminar as
r2
where
((select
count(distinct r3.state)
from RestaurSeminar
as r3 -- 1. at least
one record has state
where
r2.ProjectFK
=
r3.ProjectFK
-- filled in. Can be more, but state
and
r3.state
is not null and
r3.Restaurant
is not null) =1)
-- must be the same
and ((select
count(*)
from RestaurSeminar as
r4
where
r2.ProjectFK
=
r4.ProjectFK
-- 2. at least one other record
and
r4.state
is null andr4.Restaurant
is
not
null)
>0)
-- with state field missing
and
((select
count(distinct
r5.Restaurant)
from RestaurSeminar
as r5
where
r2.ProjectFK
=
r5.ProjectFK) =1))
-- 3. at least 1 restaurant field filled
and
state
is not null
)
as r0
where
r.ProjectFK
=
r0.ProjectFK
update query, test – use
begin
transaction and
rollback transaction:
begin transaction
UPDATE FillTheRoom
SET
FillTheRoom.ProjectFK
=
[Project].[ID]
from FillTheRoom,
Person, Project,
Customer
WHERE
(dbo.FillTheRoom.ProjectFK IS NULL)
and
SUBSTRING(dbo.FillTheRoom.AgentName, CHARINDEX(' ', dbo.FillTheRoom.AgentName)
+
1,
LEN(dbo.FillTheRoom.AgentName))
= dbo.Person.LastName
AND
SUBSTRING(dbo.FillTheRoom.AgentName,
1,
CHARINDEX(' ', dbo.FillTheRoom.AgentName) - 1)
= dbo.Person.FirstName
and
dbo.Customer.ID = dbo.Person.CustomerFK
and dbo.Customer.ID
=
dbo.Project.CustomerFK
AND dbo.FillTheRoom.MailingDate = dbo.Project.EST_MAILING_DATE
rollback transaction
user already exists in current database (error 15023) What you might really want instead is:
- ID of SQL Server instance doesn’t match ID of database. (see also here where there are 3 stored procedures to deal with orphan users in a bulk fashion)
- or especially local SQL server id, migrate to different server to avoid getting into this situation to begin with
First of all run following T-SQL Query in Query Analyzer. This will return all the existing users in database in result pan (whatever the heck a pan is).
USE YourDB
EXEC sp_change_users_login 'Report'
Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Auto_Fix’ attribute will create the user in SQL Server instance if it does not exist. In following example ‘someuser’ is UserName, ‘pwd’ is Password. Auto-Fix links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins.
EXEC sp_change_users_login 'Auto_Fix' ,'someuser', NULL,'pwd'
Run following T-SQL Query in Query Analyzer to associate login with the username. ‘Update_One’ links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified.
EXEC sp_change_users_login 'update_one', 'someuser', 'someuser'
Delete the old version of the user
EXEC sp_dropuser 'someuser'
Create the same user again in the database without any error.
user connections – see user processes, activity monitor in SSMS, database processes, sp_who2, sp_whoisactive, kill a process
In order to get a SQL user on any particular database, need to start with master database
CREATE LOGIN
Pluto WITH PASSWORD
=
'GoofysDog';
CREATE USER
Pluto FOR LOGIN
Pluto WITH DEFAULT_SCHEMA=[dbo]
Once you’ve created SQL user on master database now can proceed to apply to any particular database by running exact same command as above, except this time in the target database.
CREATE LOGIN Pluto WITH PASSWORD = 'GoofysDog';
Now that the user exists in the target database, usually give them at least connect, select permissions
GRANT CONNECT TO
Pluto;
GRANT SELECT TO
Pluto;
user permissions, generate script to copy – See permissions, generate script to copy user permissions
user processes – see also activity monitor in SSMS, database processes, sp_who2, sp_whoisactive, kill a process
SELECT
spid ,
sp.[status],
loginame [Login],
hostname,
blocked BlkBy,
sd.name
DBName,
cmd Command,
cpu CPUTime,
physical_io DiskIO,
last_batch LastBatch,
[program_name] ProgramName
FROM master.dbo.sysprocesses sp
JOIN
master.dbo.sysdatabases
sd ON
sp.dbid
=
sd.dbid
-- where sd.name like 'Meta%' -- DBName
where loginame
like
ad%
-- user
ORDER BY spid
or using Dynamic Management View:
select
[session_id] spid,
login_name,
[host_name],
[database_id]
fromsys.dm_exec_sessions
where
original_login_name like
ad%
utilities – see maintenance, tools
value, write to console – display value
version – Select
@@version
view doesn’t display decimal values – see decimal, view doesn’t display after a calculation
view, grant permission to delete/drop – DROP is not a grantable permission. see drop, grant permission to delete/drop
views, show all - Select TABLE_NAME
From Information_Schema.Tables
where TABLE_TYPE =
'VIEW'
week, first day of last week –
select
dateadd(wk,
datediff(wk, 0, getdate()) - 1, 0)
week, first day of this week –
select
dateadd(wk,
datediff(wk, 0, getdate()), 0)
week, group by – see group by week
week, last day of last week –
select
dateadd(day,
-1
-
(datepart(dw, getdate())
+
@@datefirst
- 2)
% 7,
getdate())
AS
LastSunday
week, last day of this week –
select
dateadd(wk,
datediff(wk, 0,
getdate()), 0)
+ 6
AS
NextSunday
week, most recent sunday at least two weeks ago
SELECT DATEADD(wk, -2, DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)) as 'the Sunday at least two weeks ago'
Monday is 0, Sunday is 6
when were tables created? – see tables, when created?
white space, trim – see space, trim
who’s doing what? – sp_who2
– see also sp_whoisactive at
github,
documentation,
activity monitor in SSMS,
user processes,
sp_who2,
kill a process
who am I logged in as? SELECT
SYSTEM_USER
AS
'Login Name'
with statement – Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement.
word first – see first word
write value to console – see display value
year ago, first day of month – see month, first day of a year ago
year, group by – see group by year
yesterday – select
dateadd(day,-1,getdate())
zeros, pad with– see pad with zeros
10061 - "A network-related or instance-specific error occurred while establishing a connection to SQL. Server The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. No connection could be made because the target machine actively refused it."
Sounds scary, eh? I sometimes get this when connecting to my local instance. All I’ve ever had to do was simply start the SQL Server service on my local machine. Even though it’s supposed to start automatically, sometimes it doesn’t seem to
15023 User already exists in current database - see user already exists in current database or especially local SQL server id, migrate to different server to avoid getting into this situation to begin with
22022 – see backup failed with error # 22022