Stephen's Wiki

User Tools

Site Tools

Trace:
sql_cheat_sheet

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
sql_cheat_sheet [2020/03/04 02:41] – [Display All Foreign Keys] stephensql_cheat_sheet [2025/01/13 22:44] (current) – stephen
Line 1: Line 1:
 ====== SQL Cheat Sheet ====== ====== SQL Cheat Sheet ======
 +
 +For Postgres SQL, see [[postgres_sql_cheat_sheet|Postgres SQL Cheat Sheet]]
  
 ===== CREATE ===== ===== CREATE =====
Line 28: Line 30:
 FROM FROM
  Table2  Table2
 +
 +-- INSERTing multiple rows.
 +INSERT INTO Table1
 +   (Col1,  Col2,  Col3)
 +VALUES
 +   (Data1, Data2, Data3)
 + , (Data1, Data2, Data3)
 </code> </code>
  
Line 39: Line 48:
    Table1.Col1 = Table2.Col1    Table1.Col1 = Table2.Col1
  , Table1.Col2 = Table2.Col2  , 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 FROM
  Table1  Table1
Line 99: Line 122:
 FROM FROM
  Employee  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> </code>
  
Line 167: Line 276:
 </code> </code>
  
-===== Determine the Data Types in Results of a Query=====+===== Determine the Data Types in Results of a Query =====
  
 <code sql> <code sql>
 SELECT * FROM sys.dm_exec_describe_first_result_set( SELECT * FROM sys.dm_exec_describe_first_result_set(
    N'EXEC SomeStoredProcedure 1234', NULL, 0);    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> </code>
  
Line 214: Line 333:
 WHERE WHERE
     SourceObjects.object_id IS NULL OR NewObjects.object_id IS NULL     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> </code>
  
Line 311: Line 442:
 </code> </code>
  
-===== Display Foreign Key Relationships for a Table =====+===== 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: Can do this, but it will only show which tables reference the specified table, not what tables the specified table references:
Line 325: Line 458:
 </code> </code>
  
-===== Display All Foreign Key Info =====+==== Display All Foreign Key Info ====
  
 <code sql> <code sql>
Line 391: Line 524:
  
 <code sql> <code sql>
-DECLARE @searchText NVARCHAR(MAX) = N'Foo';+DECLARE @searchText NVARCHAR(20) = N'Foo';
 DECLARE @sql NVARCHAR(MAX) = N''; DECLARE @sql NVARCHAR(MAX) = N'';
  
sql_cheat_sheet.1583289660.txt.gz · Last modified: 2020/03/05 03:41 (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki