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 [2019/01/31 01:26] – [Common Table Expression (CTE)] 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>
  
-===== List 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> <code sql>
 SELECT SELECT
-   ReferentialConstraint.CONSTRAINT_NAME        ForeignKey_Name +   ForeignKey.[name]    ForeignKey 
- , ForeignKey.TABLE_SCHEMA                      ForeignKey_Schema + , PrimaryTable.[name]  PrimaryTable 
- , ForeignKey.TABLE_NAME                        ForeignKey_Table + , PrimaryColumn.[name] PrimaryColumn 
- , ForeignKey.COLUMN_NAME                       ForeignKey_Column + , ForeignTable.[name]  ForeignTable 
- , ReferentialConstraint.UNIQUE_CONSTRAINT_NAME PrimaryKey_Name + , ForeignColumn.[name] ForeignColumn
- , PrimaryKey.TABLE_SCHEMA                      PrimaryKey_Schema +
- , PrimaryKey.TABLE_NAME                        PrimaryKey_Table +
- , PrimaryKey.COLUMN_NAME                       PrimaryKey_Column +
- , ReferentialConstraint.MATCH_OPTION           MatchOption +
- , ReferentialConstraint.UPDATE_RULE            UpdateRule +
- , ReferentialConstraint.DELETE_RULE            DeleteRule+
 FROM FROM
- INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ReferentialConstraint+ sys.foreign_key_columns ForeignKeyColumn
 JOIN JOIN
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE ForeignKey + sys.foreign_keys ForeignKey 
- ON ForeignKey.CONSTRAINT_NAME = ReferentialConstraint.CONSTRAINT_NAME+ ON ForeignKey.object_id = ForeignKeyColumn.constraint_object_id
 JOIN JOIN
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE PrimaryKey + sys.columns PrimaryColumn 
- ON PrimaryKey.CONSTRAINT_NAME = ReferentialConstraint.UNIQUE_CONSTRAINT_NAME+ 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> </code>
  
Line 339: 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'';
  
Line 382: Line 567:
 EXEC (@sql)</code> 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 ===== ===== Randomness =====
  
Line 388: Line 621:
  
 SELECT LEFT(CAST(NEWID() AS VARCHAR(100)), 6) AS RandomString 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> </code>
  
Line 451: Line 690:
 </code> </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 ===== ===== Creating a Login or User =====
Line 514: Line 740:
 WHERE  sys.objects.TYPE = 'U' WHERE  sys.objects.TYPE = 'U'
 ORDER BY SortOrder 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> </code>
  
sql_cheat_sheet.1548898012.txt.gz · Last modified: 2019/02/01 02:26 (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki