Differences

This shows you the differences between two versions of the page.

--- sql_cheat_sheet [2013/01/22 22:09] – [Accessing A Linked Server] stephen
+++ sql_cheat_sheet [2025/01/13 22:44] (current) – stephen
@@ Line 1: / Line 1: @@
 ====== SQL Cheat Sheet ======
+For Postgres SQL, see [[postgres_sql_cheat_sheet|Postgres SQL Cheat Sheet]]
 ===== CREATE =====
@@ Line 11: / Line 13: @@
 	)
 </code>
 ===== INSERT =====
@@ Line 29: / Line 30: @@
 FROM
 	Table2
+-- INSERTing multiple rows.
+INSERT INTO Table1
+	  (Col1,  Col2,  Col3)
+VALUES
+	  (Data1, Data2, Data3)
+	, (Data1, Data2, Data3)
 </code>
@@ Line 48: / Line 56: @@
 </code>
+===== DELETE =====
+<code sql>
+-- Combining an UPDATE with a JOIN.
+DELETE
+	Table1
+FROM
+	Table1
+INNER JOIN
+	Table2
+ON
+	Table2.Id = Table1.Id
+</code>
+===== Nested SELECT Statement =====
+<code sql>
+SELECT
+	BestEmployee.Name
+FROM (
+	SELECT TOP 100
+		*
+	FROM
+		Employee
+	ORDER BY
+		Employee.Sales DESC
+) AS BestEmployee
+ORDER BY
+	BestEmployee.Name
+</code>
+===== Common Table Expression (CTE) =====
+<code sql>
+-- Untested code.
+WITH BestEmployee_CTE
+AS
+(
+	SELECT TOP 100
+		*
+	FROM
+		Employee
+	ORDER BY
+		Employee.Sales DESC
+)
+SELECT
+	BestEmployee.Name
+FROM
+	BestEmployee_CTE
+</code>
+===== Table Variable =====
+<code sql>
+-- Use *only* if a nested SELECT or CTE won't do the job.
+DECLARE @SpecialEmployees TABLE
+(
+	EmployeeID INT
+)
+INSERT
+	@SpecialEmployees (EmployeeID)
+SELECT
+	Employee.EmployeeID
+FROM
+	Employee
+</code>
+===== Cascading Delete (work in progress) =====
+<code sql>
+DECLARE @TargetTable VARCHAR(MAX) = 'Sale'
+DECLARE @maxExpansions INT = 600
+DECLARE @FamilyTree TABLE (
+	  ObjectId   INT          NOT NULL
+	, TableName  VARCHAR(max) NOT NULL
+	, IsExpanded BIT          NOT NULL
+	, [Path]     VARCHAR(max) NOT NULL
+)
+INSERT
+	@FamilyTree (ObjectId, TableName, IsExpanded, [Path])
+SELECT
+	tables.object_id
+	, tables.[name]
+	, 0
+	, tables.[name]
+FROM
+	sys.tables
+WHERE
+	sys.tables.[name] = @TargetTable
+DECLARE @currentObjectId INT
+DECLARE @currentExpansion INT = 0
+SELECT TOP 1
+	@currentObjectId = [@FamilyTree].ObjectId
+FROM
+	@FamilyTree
+WHERE
+	[@FamilyTree].IsExpanded = 0
+WHILE @currentObjectId IS NOT NULL AND @currentExpansion < @maxExpansions
+BEGIN
+	INSERT
+		@FamilyTree (ObjectId, TableName, IsExpanded, [Path])
+	SELECT DISTINCT
+		  ChildTable.object_id
+		, ChildTable.[name]
+		, 0
+		, CONCAT ([@FamilyTree].[Path], ' > ', ChildTable.[name])
+	FROM
+		@FamilyTree
+	JOIN
+		sys.foreign_keys ForeignKey
+		ON ForeignKey.parent_object_id = [@FamilyTree].ObjectId
+	JOIN
+		sys.tables ChildTable
+		ON ChildTable.object_id = ForeignKey.referenced_object_id
+	WHERE
+		[@FamilyTree].ObjectId = @currentObjectId
+	UPDATE
+		@FamilyTree
+	SET
+		[@FamilyTree].IsExpanded = 1
+	WHERE
+		[@FamilyTree].ObjectId = @currentObjectId
+	SELECT TOP 1
+		@currentObjectId = [@FamilyTree].ObjectId
+	FROM
+		@FamilyTree
+	WHERE
+		[@FamilyTree].IsExpanded = 0
+	SELECT @currentExpansion = @currentExpansion + 1
+END
+SELECT DISTINCT
+	*
+	, CONCAT(
+		'DELETE ['
+		, [@FamilyTree].TableName
+		, '] -- '
+		, [@FamilyTree].[Path]
+	)
+FROM
+	@FamilyTree
+ORDER BY
+	[@FamilyTree].[Path] DESC
+</code>
+===== Copy a Table =====
+<code sql>
+-- Doesn't copy keys or constraints.
+CREATE TABLE
+	NewTable
+AS
+SELECT
+	*
+FROM
+	SourceTable
+-- Omit, if data is also to be copied.
+WHERE
+=2;
+</code>
+===== Alter a Table =====
+==== Add a column ====
+<code sql>
+ALTER TABLE Table_Name
+	ADD Column_Name INT NULL;
+</code>
+==== Add a column if it doesn't exist ====
+<code sql>
+IF COL_LENGTH('Table_Name','Column_Name') IS NULL
+BEGIN
+	ALTER TABLE Table_Name
+		ADD Column_Name INT NULL;
+END
+</code>
+==== Rename a column ====
+<code sql>
+EXEC sp_rename 'Categories.Active', 'Active_Old', 'COLUMN'
+</code>
+===== Create or alter a stored procedure =====
+If you want to preserve permissions:
+<code sql>
+IF OBJECT_ID('StoredProcedure_Name') IS NULL
+	EXEC ('CREATE PROCEDURE StoredProcedure_Name AS SELECT 1')
+GO
+ALTER PROCEDURE StoredProcedure_Name
+(
+...
+</code>
+If you are happy to blow away permissions:
+<code sql>
+IF OBJECT_ID('StoredProcedure_Name', 'P') IS NOT NULL
+	DROP PROC StoredProcedure_Name
+GO
+CREATE PROCEDURE StoredProcedure_Name
+(
+...
+</code>
+===== Determine the Data Types in Results of a Query =====
+<code sql>
+SELECT * FROM sys.dm_exec_describe_first_result_set(
+   N'EXEC SomeStoredProcedure 1234', NULL, 0);
+</code>
+===== Sort out Collation =====
+<code sql>
+-- Determine the default collation for the server.
+SELECT serverproperty('collation')
+-- Convert a varchar to a specific collation.
+SELECT CAST('abc' AS VARCHAR(5)) COLLATE Latin1_General_CI_AS
+</code>
+===== Duplicate (copy) a Database =====
+<code sql>
+BACKUP DATABASE SourceDatabase
+TO DISK = 'E:\Backups\20150609 SourceDatabase.bak'
+WITH COPY_ONLY, INIT
+CREATE DATABASE NewDatabase
+-- Use the LogicalName column to populate the MOVE clause of the RESTORE DATABASE command.
+RESTORE FILELISTONLY FROM DISK = 'E:\Backups\20150609 SourceDatabase.bak' WITH FILE = 1
+-- Use the physical_name column to populate the TO clause of the RESTORE DATABASE command.
+SELECT
+	master_files.physical_name
+FROM
+	master.sys.databases
+JOIN
+	master.sys.master_files
+ON
+	master_files.database_id = databases.database_id
+WHERE
+	databases.name = 'NewDatabase'
+RESTORE DATABASE NewDatabase
+FROM DISK = 'E:\Backups\20150609 SourceDatabase.bak'
+WITH REPLACE
+	, MOVE 'SourceDatabase' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NewDatabase.mdf'
+	, MOVE 'SourceDatabase_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NewDatabase_log.ldf'
+-- Compare the databases. No results = no differences.
+SELECT
+    *
+FROM
+    SourceDatabase.sys.objects SourceObjects
+FULL JOIN
+    NewDatabase.sys.objects NewObjects
+    ON NewObjects.object_id = SourceObjects.object_id
+WHERE
+    SourceObjects.object_id IS NULL OR NewObjects.object_id IS NULL
+</code>
+===== Export a .bacpac By Command Line =====
+Install SqlPackage:
+<code>
+dotnet tool install -g microsoft.sqlpackage
+</code>
+Export .backpac. Only add the ''Encrypt=False'' for local exports.
+<code>
+SqlPackage /Action:Export /TargetFile:"D:\Dev\BucketsOfFundsSQL\$((Get-Date).ToString("yyyyMMdd")) Buckets.bacpac" /SourceConnectionString:"Integrated Security=SSPI;Persist Security Info=False;Encrypt=False;Initial Catalog=Buckets;Data Source=."
+</code>
+===== Close all database connections =====
+<code sql>
+ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
+GO
+-- Do this straight away, because it sucks if a database is left in single user mode.
+ALTER DATABASE DatabaseName SET MULTI_USER
+GO
+</code>
+===== Find stuff in all Databases =====
+<code sql>
+-- Find a table in all databases.
+sp_msforeachdb @command1='USE ?;SELECT DB_NAME() AS [Database], * FROM sys.Tables WHERE name LIKE ''TableName'''
+</code>
+<code sql>
+-- Find a table column in all databases.
+sp_msforeachdb @command1='USE ?;SELECT DB_NAME() AS [Database], t.name AS [Table], * FROM sys.Columns c JOIN sys.Tables t ON t.object_id = c.object_id WHERE c.name LIKE ''ColumnName'''
+</code>
+===== Compare two Databases =====
+<code sql>
+-- No results = no differences. This is only suitable for databases that have been duplicated *exactly*.
+SELECT
+    *
+FROM
+    SourceDatabase.sys.objects SourceObjects
+FULL JOIN
+    TargetDatabase.sys.objects TargetDatabase
+    ON TargetDatabase.object_id = SourceObjects.object_id
+WHERE
+    SourceObjects.object_id IS NULL OR TargetDatabase.object_id IS NULL
+</code>
 ===== Most Recent =====
@@ Line 56: / Line 392: @@
 INNER JOIN
 	History MostRecentHistory
-	ON Foo.Id = MostRecentHistory.Id
+	ON MostRecentHistory.FooId = Foo.Id
 LEFT JOIN
 	History AllHistory
-	ON MostRecentHistory.Id = AllHistory.Id
+	ON AllHistory.FooId = MostRecentHistory.FooId
-	AND MostRecentHistory.LastUpdateOn < AllHistory.LastUpdateOn
+	AND AllHistory.LastUpdateOn > MostRecentHistory.LastUpdateOn
 WHERE
 	AllHistory.Id IS NULL
@@ Line 70: / Line 406: @@
 LEFT JOIN
 	History MostRecentHistory
-	ON Foo.Id = MostRecentHistory.Id
+	ON MostRecentHistory.FooId = Foo.Id
 LEFT JOIN
 	History AllHistory
-	ON MostRecentHistory.Id = AllHistory.Id
+	ON AllHistory.FooId = MostRecentHistory.FooId
-	AND MostRecentHistory.LastUpdateOn < AllHistory.LastUpdateOn
+	AND AllHistory.LastUpdateOn > MostRecentHistory.LastUpdateOn
 WHERE
 	AllHistory.Id IS NULL
 </code>
+===== Table Variables =====
+**Use a Nested SELECT Statement or a Common Table Expression instead!**
-===== Table Variables =====
+<code sql>
+DECLARE @Results TABLE (
+	  BananaId   INT
+	, BananaName VARCHAR(MAX)
+)
+</code>
+===== Stored Procedure into Table Variable =====
+**Use a Nested SELECT Statement or a Common Table Expression instead!**
 <code sql>
-DECLARE @ProductTotals TABLE
+DECLARE @Results TABLE (
-(
+	  BananaId   int
-  ProductID int,
+	, BananaName varchar(max)
-  Revenue money
 )
+INSERT INTO
+	@Results
+EXEC
+	GetRipeBananas
 </code>
+===== Foreign Keys =====
+==== Display Foreign Key Relationships for a Table ====
+Can do this, but it will only show which tables reference the specified table, not what tables the specified table references:
+<code sql>
+sp_fkeys TableName
+</code>
+Better to do this, which lists all foreign key relationships and lots of other information as well:
+<code sql>
+sp_help TableName
+</code>
+==== Display All Foreign Key Info ====
+<code sql>
+SELECT
+	  ForeignKey.[name]    ForeignKey
+	, PrimaryTable.[name]  PrimaryTable
+	, PrimaryColumn.[name] PrimaryColumn
+	, ForeignTable.[name]  ForeignTable
+	, ForeignColumn.[name] ForeignColumn
+FROM
+	sys.foreign_key_columns ForeignKeyColumn
+JOIN
+	sys.foreign_keys ForeignKey
+	ON ForeignKey.object_id = ForeignKeyColumn.constraint_object_id
+JOIN
+	sys.columns PrimaryColumn
+	ON PrimaryColumn.object_id = ForeignKeyColumn.referenced_object_id
+	AND PrimaryColumn.column_id = ForeignKeyColumn.referenced_column_id
+JOIN
+	sys.tables PrimaryTable
+	ON PrimaryTable.object_id = ForeignKeyColumn.referenced_object_id
+JOIN
+	sys.columns ForeignColumn
+	ON ForeignColumn.object_id = ForeignKeyColumn.parent_object_id
+	AND ForeignColumn.column_id = ForeignKeyColumn.parent_column_id
+JOIN
+	sys.tables ForeignTable
+	ON ForeignTable.object_id = ForeignKeyColumn.parent_object_id
+ORDER BY
+	  PrimaryTable.[name]
+	, PrimaryColumn.[name]
+	, ForeignTable.[name]
+	, ForeignColumn.[name]
+</code>
+===== Display All Table Column Info =====
+<code sql>
+SELECT
+	  [Schema].[name]      SchemaName
+	, [Table].[name]       TableName
+	, [Column].[name]      ColumnName
+	, [ColumnType].[name]  ColumnType
+	, [Column].max_length  [MaxLength]
+	, [Column].[precision] [Precision]
+	, [Column].is_nullable Nullable
+FROM
+	sys.tables [Table]
+JOIN
+	sys.schemas [Schema]
+	ON [Schema].schema_id = [Table].schema_id
+JOIN
+	sys.columns [Column]
+	ON [Column].object_id = [Table].object_id
+JOIN
+	sys.types [ColumnType]
+	ON [ColumnType].system_type_id = [Column].system_type_id
+ORDER BY
+	  [Schema].[name]
+	, [Table].[name]
+	, [Column].[name]
+</code>
+===== Search All Text in a Database =====
+<code sql>
+DECLARE @searchText NVARCHAR(20) = N'Foo';
+DECLARE @sql NVARCHAR(MAX) = N'';
+SELECT @sql = @sql +
+	CONCAT(
+		'SELECT '''
+		, [Table].[name]
+		, ''' AS [Table], '''
+		, [Column].[name]
+		, ''' AS [Column], ['
+		, [Table].[name]
+		, '].['
+		, [Column].[name]
+		, '] AS [Data] FROM '
+		, [Table].[name]
+		, ' WHERE ['
+		, [Table].[name]
+		, '].['
+		, [Column].[name]
+		, '] LIKE ''%'
+		, @searchText
+		, '%'''
+		, CHAR(13) + CHAR(10)
+		, 'UNION ALL'
+		, CHAR(13) + CHAR(10)
+	)
+FROM
+	sys.columns AS [Column]
+JOIN
+	sys.tables AS [Table]
+	ON [Table].object_id = [Column].object_id
+JOIN
+	sys.systypes AS ColumnType
+	ON ColumnType.xtype = [Column].system_type_id
+WHERE
+	[ColumnType].[name] IN ('nchar', 'ntext', 'nvarchar', 'text', 'varchar')
+ORDER BY
+	  [Table].[name]
+	, [Column].[name]
+SELECT @sql = SUBSTRING(@sql,1, LEN(@sql) - 11);
+EXEC (@sql)</code>
+==== Alternative ====
+  - Run the SQL below.
+  - Copy the results into the editor.
+  - Remove the last ''UNION ALL''.
+  - Append the bit at the beginning.
+<code sql>
+-- DECLARE @searchText NVARCHAR(100) = N'%foo%';
+SELECT
+	CONCAT(
+		  'SELECT '''
+		, [TABLE].[name]
+		, ''' AS [Table], '''
+		, [COLUMN].[name]
+		, ''' AS [Column], ['
+		, [TABLE].[name]
+		, '].['
+		, [COLUMN].[name]
+		, '] COLLATE '
+		, convert(NVARCHAR, serverproperty('collation'))
+		, ' AS [Data] FROM ['
+		, [TABLE].[name]
+		, '] WHERE ['
+		, [TABLE].[name]
+		, '].['
+		, [COLUMN].[name]
+		, '] LIKE @searchText UNION ALL'
+	)
+FROM
+	sys.columns AS [COLUMN]
+JOIN
+	sys.tables AS [TABLE]
+	ON [TABLE].object_id = [COLUMN].object_id
+JOIN
+	sys.systypes AS ColumnType
+	ON ColumnType.xtype = [COLUMN].system_type_id
+JOIN
+	sys.schemas AS [Schema]
+	ON [Schema].schema_id = [TABLE].schema_id
+WHERE
+	[ColumnType].[name] IN ('nchar', 'ntext', 'nvarchar', 'text', 'varchar')
+	AND [TABLE].is_ms_shipped = 0
+	AND [Schema].[name] = 'dbo'
+ORDER BY
+	  [TABLE].[name]
+	, [COLUMN].[name]
+</code>
 ===== Randomness =====
@@ Line 97: / Line 621: @@
 SELECT LEFT(CAST(NEWID() AS VARCHAR(100)), 6) AS RandomString
+-- Semi-random, good for randomish sorting, returns varbinary(8000)
+SELECT HashBytes('MD5', CONCAT('Seed', Table.TableId))
+-- Same thing, but returns varchar(32)
+SELECT CONVERT(VARCHAR(32), HashBytes('MD5', CONCAT('Seed', Table.TableId)), 2)
+</code>
+===== Determine Free Disk Space on SQL Server =====
+<code sql>
+EXEC sys.xp_fixeddrives
+</code>
+===== Does file exist on SQL Server =====
+<code sql>
+-- Just for general info
+EXEC master.dbo.xp_fileexist N'\\PVW-ENT-SQL32\Backups\SMS.bak'
+-- Part of a script that needs to make a decision.
+DECLARE @result INT
+EXEC master.dbo.xp_fileexist N'\\PVW-ENT-SQL32\Backups\SMS.bak', @result OUTPUT
+IF @result = 1
+	PRINT 'File exists.'
+ELSE
+	PRINT 'File does not exist.'
+</code>
+===== Determine Contents of a Directory on SQL Server =====
+<code sql>
+EXEC Master.dbo.xp_DirTree 'C:',1,1
 </code>
@@ Line 103: / Line 660: @@
 <code sql>
 -- Replace username with the actual username.
-USE database
+USE DATABASE
 EXEC sp_change_users_login 'report'
-EXEC sp_change_users_login 'auto_fix', 'username' -- This definitely works.
+EXEC sp_change_users_login 'auto_fix', 'CIPLogin' -- This definitely works.
 -- Or try:
 EXEC sp_change_users_login @Action = 'auto_fix', @UserNamePattern = 'User', @Password = 'Password'
 EXEC sp_change_users_login 'update_one', username, username
+-- Quantate
+USE QuantateAdministration
+EXEC sp_change_users_login 'update_one', 'QuantateSysAdmin', 'Quantate_Login'
+-- CIP
+USE CIP
+EXEC sp_change_users_login 'update_one', 'CIPUser', 'CIPLogin'
+-- NGA Provisioning
+USE NextGenProvDB
+EXEC sp_change_users_login 'update_one', 'NextGenUser', 'NextGenLogin'
+-- General.
+EXEC sp_change_users_login 'update_one', 'DBUser', 'ServerUser'
+-- Server -> Security -> Logins -> *ServerUser* -> Right click -> Properties
+-- User Mapping -> Find correct database, value in User col = *DBUser*
 </code>
@@ Line 120: / Line 690: @@
 </code>
-===== Determining Foreign Key Relationships =====
-Can do this, but it will only show which tables reference the specified table, not what tables the specified table references:
+===== Creating a Login or User =====
+This is particularly useful when the database is on Azure.
 <code sql>
-sp_fkeys TableName
+-- Create the Login.
+-- The CHECK_EXPIRATION and CHECK_POLICY options relate to the password. Switch them off for a service or application type login.
+CREATE LOGIN [OrchardUser] WITH PASSWORD=N'XXXXXXX', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
+GO
+-- Create the User.
+CREATE USER [OrchardUser] FOR LOGIN [OrchardUser] WITH DEFAULT_SCHEMA=[dbo]
+GO
+-- Grant a role.
+EXEC sp_addrolemember N'db_owner', N'OrchardUser'
 </code>
-Better to do this, which lists all foreign key relationships and lots of other information as well:
+===== Nuke the Database =====
+Generates SQL commands to drop everything in the database. Be careful you nuke the correct database...
 <code sql>
-sp_help TableName
+USE [Database]
+SELECT 'DROP PROCEDURE ' + [Schema].name + '.' + StoredProcedure.name AS SqlCommand
+       , 10                                                           AS SortOrder
+FROM   sys.objects StoredProcedure
+JOIN   sys.schemas [Schema]
+ON     [Schema].schema_id = StoredProcedure.schema_id
+WHERE  StoredProcedure.TYPE = 'P'
+UNION
+SELECT 'DROP VIEW ' + sys.objects.name
+       , 20
+FROM   sys.objects
+WHERE  sys.objects.TYPE = 'V'
+UNION
+SELECT 'ALTER TABLE ' + [ParentTable].Name + ' DROP CONSTRAINT [' + [ForeignKey].name + ']'
+       , 30
+FROM   sys.objects [ForeignKey]
+JOIN   sys.objects [ParentTable]
+ON     [ParentTable].object_id = [ForeignKey].parent_object_id
+WHERE  [ForeignKey].TYPE = 'F'
+UNION
+SELECT 'DROP TABLE ' + sys.objects.name
+       , 40
+FROM   sys.objects
+WHERE  sys.objects.TYPE = 'U'
+ORDER BY SortOrder
 </code>
+===== Get a database out of Single-User Mode =====
+<code sql>
+EXEC sp_who
+-- Find the spid of the connect that is connected to the database.
+KILL 56
+ALTER DATABASE [DatebaseName] SET MULTI_USER
+</code>
+===== Calling a Stored Procedure with ADO.NET =====
+If only one result set is returned:
+<code c#>
+using (SqlConnection connection = new SqlConnection("Data Source=...")) {
+	connection.Open();
+	using (SqlCommand command = new SqlCommand("StoredProcedureName", connection)) {
+		command.CommandType = CommandType.StoredProcedure;
+		command.CommandTimeout = 300;
+		using (SqlDataReader reader = command.ExecuteReader()) {
+			while (reader.Read()) {
+				for (int i = 0; i < reader.FieldCount; i++) {
+					string fieldName  = reader.GetName(i);
+					// Note that values must be accessed by the column index and can not be accessed directly by field name.
+					string fieldValue = reader.GetValue(i);
+					Console.WriteLine("{0}: {1}", fieldName, fieldValue);
+				}
+			}
+			reader.Close();
+		}
+	}
+}
+</code>
+If multiple result sets are returned:
+<code c#>
+using (SqlConnection connection = new SqlConnection("Data Source=...")) {
+	connection.Open();
+	using (SqlCommand command = new SqlCommand("StoredProcedureName", connection)) {
+		command.CommandType = CommandType.StoredProcedure;
+		command.CommandTimeout = 300;
+		using (SqlDataReader reader = command.ExecuteReader()) {
+			do {
+				while (reader.Read()) {
+					for (int i = 0; i < reader.FieldCount; i++) {
+						string fieldName  = reader.GetName(i);
+						// Note that values must be accessed by the column index and can not be accessed directly by field name.
+						string fieldValue = reader.GetValue(i);
+						Console.WriteLine("{0}: {1}", fieldName, fieldValue);
+					}
+				}
+			// Move to the next set of results.
+			} while (reader.NextResult());
+			reader.Close();
+		}
+	}
+}
+</code>
 ===== Joins =====