Differences

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

--- sql_cheat_sheet [2020/03/04 02:39] – [List All Foreign Keys] 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 28: / Line 30: @@
 FROM
 	Table2
+-- INSERTing multiple rows.
+INSERT INTO Table1
+	  (Col1,  Col2,  Col3)
+VALUES
+	  (Data1, Data2, Data3)
+	, (Data1, Data2, Data3)
 </code>
@@ Line 39: / Line 48: @@
 	  Table1.Col1 = Table2.Col1
 	, Table1.Col2 = Table2.Col2
+FROM
+	Table1
+INNER JOIN
+	Table2
+ON
+	Table2.Id = Table1.Id
+</code>
+===== DELETE =====
+<code sql>
+-- Combining an UPDATE with a JOIN.
+DELETE
+	Table1
 FROM
 	Table1
@@ Line 99: / Line 122: @@
 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>
@@ Line 167: / Line 276: @@
 </code>
-===== Determine the Data Types in Results of a Query=====
+===== 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>
@@ Line 214: / Line 333: @@
 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>
@@ Line 311: / Line 442: @@
 </code>
-===== Display All Foreign Keys =====
+===== 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>
@@ Line 377: / Line 524: @@
 <code sql>
-DECLARE @searchText NVARCHAR(MAX) = N'Foo';
+DECLARE @searchText NVARCHAR(20) = N'Foo';
 DECLARE @sql NVARCHAR(MAX) = N'';
@@ Line 543: / 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:
-<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>
 ===== Creating a Login or User =====