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 [2015/06/09 00:14] – [Randomness] 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 83: Line 106:
  BestEmployee_CTE  BestEmployee_CTE
 </code> </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
 + 1=2;
 +</code>
 +
 ===== Alter a Table ===== ===== Alter a Table =====
  
Line 131: Line 274:
 ( (
 ... ...
 +</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> </code>
  
Line 138: Line 298:
 BACKUP DATABASE SourceDatabase BACKUP DATABASE SourceDatabase
 TO DISK = 'E:\Backups\20150609 SourceDatabase.bak' TO DISK = 'E:\Backups\20150609 SourceDatabase.bak'
-WITH COPY_ONLY+WITH COPY_ONLY, INIT
  
 CREATE DATABASE NewDatabase CREATE DATABASE NewDatabase
Line 159: Line 319:
 RESTORE DATABASE NewDatabase RESTORE DATABASE NewDatabase
 FROM DISK = 'E:\Backups\20150609 SourceDatabase.bak' FROM DISK = 'E:\Backups\20150609 SourceDatabase.bak'
-WITH REPLACE, +WITH REPLACE 
-MOVE 'SourceDatabase' TO N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\NewDatabase.mdf', + , 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'+ , 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> </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 ===== ===== Most Recent =====
  
Line 222: 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> </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 ===== ===== Randomness =====
  
Line 253: 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 259: Line 633:
 <code sql> <code sql>
 EXEC sys.xp_fixeddrives 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> </code>
  
Line 295: 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 358: 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.1433808858.txt.gz · Last modified: 2017/01/01 19:52 (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki