<< A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #

–A–

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, declare

array, reverse see – delimited string, scalar function to reverse order of to reverse the order of a delimited string.

–B–

back up database

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

–C–

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

coalesce

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

computer, from where SQL runs

SELECT @@SERVERNAME

computer, instances of SQL

$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

concatenate

  1. when everything is varchar, very simple: +
  2. concatenate an integer to varchar:

Note = Note + ', #' +  cast(@CustomerGoingAway as varchar(10))

  1. concatenate date time to varchar:

declare @now datetime
set @now = getdate()
print 'now: ' + cast(@now as varchar(20))

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

contiguous records, find

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

custom roles

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

custom role, create

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

–D–

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

database owner, change

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

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;

database recovery model, show

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

How to debug SQL code

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

dense rank

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)}))

disk space used by database

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

display value

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 & powershellnormalCrLf
 
rs.MoveFirst
Do While Not rs.EOF
   strText = strText & """" & rs(0) & """" & ","
   strText = strText & """" & rs(1) & """" & ","
   strText = strText & """" & rs(2) & """"
   strFileText = strFileText & strText & powershellnormalCrLf
   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:

to grant the db_ddladmin role

ALTER ROLE db_ddladmin ADD MEMBER [someUser@yourDomain.com];

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))

–E–

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

Enable Database Mail

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

error log files location

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:

–F–

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.

–G–

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 permissions

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)

group by every 10 minutes

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'

group by day

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

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))

group by hours

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

group by minute

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;

group by month

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)

group by year

SELECT COUNT(*) AS total, YEAR(last_login) AS year
FROM users
GROUP BY YEAR(last_login)
ORDER BY year desc

group by weekGROUP 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

–H–

hello world – see display value

hour, add one to

DECLARE @mostRecentPrintTime AS DATETIME
DECLARE @oneHourAfterMostRecentPrintTime AS DATETIME
select @mostRecentPrintTime = max(date)
from PrintHistory
select @oneHourAfterMostRecentPrintTime = DATEADD(hour,1,@mostRecentPrintTime)

hour of the day

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

–I–

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','bob@smith.com')

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)

–J–

job alerts

Create a Database Mail profile - I

Enable Database Mail

jobs, list

SELECT job_id, name, enabled, description, date_created, date_modified
FROM msdb.dbo.sysjobs_view

jobs, list owners

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

–K–

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)

–L–

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

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

log location

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

login audit

View a SQL Server Audit Log

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

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

–M–

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 monthSELECT 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 monthSELECT 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;

–N–

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

–O–

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

order by

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.

outer join, full

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

output from stored procedures, include in query

Normally, must specify each row when declaring a temp table. But use dynamic SQL instead to create the temp table.

When using dynamic SQL to create the table, it exists only within the scope of that dynamic SQL execution. To resolve this, use a global temporary table (prefixed with ##)

-- Step 1: Get the metadata of the stored procedure's result set
DECLARE @sql NVARCHAR(MAX);
DECLARE @columns NVARCHAR(MAX);

SELECT @columns = STRING_AGG(QUOTENAME(name) + ' ' + system_type_name, ', ')
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('dbo.EventID2889groupByIPUserBindTypeComptrName'), 0);

-- Step 2: Create a global temporary table with the retrieved columns
SET @sql = 'CREATE TABLE ##TempResults (' + @columns + ')';
EXEC sp_executesql @sql;

-- Step 3: Insert the results of the stored procedure into the global temporary table
SET @sql = 'INSERT INTO ##TempResults EXEC dbo.EventID2889groupByIPUserBindTypeComptrName';
EXEC sp_executesql @sql;

-- Step 4: Query the global temporary table
SELECT * FROM ##TempResults
WHERE (usr LIKE 'srv-%' OR securityUser LIKE 'srv-%')
and (usr <> 'MontyPython' and securityUser <> 'MontyPython');

--Clean up
DROP TABLE ##TempResults;

owner of a database, change – see also database owners, list

USE pubs
EXEC sp_changedbowner 'John'

or

ALTER AUTHORIZATION ON DATABASE::starfleet TO sa

–P–

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) = '2024'
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.TargetID = tintg.TargetID
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

  1. In SSMS, right Click on Database → Click TasksGenerate Scripts… to pop window up.
  2. 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
  3. 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

product key

D:\x64\DefaultSetup.ini

proportionally trim each category within a group – see NTILE to trim each category proportionally

–Q–

queries, show all - Select TABLE_NAME From Information_Schema.Tables where TABLE_TYPE = 'VIEW'

query, include results from stored procedure – see output from stored procedures, include in query

query store, enable

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'

–R–

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:#569CD6>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

–S–

sa can't log in

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:

  1. Open SQL Server Management Studio (SSMS).
  2. On the Tools menu, click Options.
  3. In the navigation pane of the Options window, click Designers and then Table and Database Designers.
  4. 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.

scalar function

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

scheduled jobs, list

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

schemas, list with owners

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

undocumented nature of

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.

sqlcmd

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.

SSMS dark mode

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, output from stored procedures, include in query

stored procedure results, feed into another stored procedure – see also output from stored procedures, include in query – 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

–T–

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.

table, create from a query

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 ViewObject 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

tables, when created?

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

truncate datetime to date

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 hoursselect 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

–U–

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.

unmatched

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

update inner join

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:

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

user, create

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

–V–

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'

Visual Studio Code SQL Server Extension (mssql)

Go to the Extensions view by clicking the Extensions icon in the Activity Bar on the side of the window or by pressing Ctrl+Shift+X.

In the search bar, type mssql.

Click Install on the SQL Server (mssql) extension.

Once you have it installed, highlight SQL you want to Run and press Ctrl+Shift+E. If you haven’t already established a connection, it will ask for host (server name), database (optional), name (optional).

–W–

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

undocumented nature of

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

–X–

–Y–

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())

–Z–

zeros, pad with– see pad with zeros

–No's–

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