Stephen's Wiki

User Tools

Site Tools

Trace: • reading_list
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 [2014/02/07 01:53] – [Add a column if it doesn't exist] 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 11: Line 13:
  )  )
 </code> </code>
- 
  
 ===== INSERT ===== ===== INSERT =====
Line 29: 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 48: Line 56:
 </code> </code>
  
-===== Add a column if it doesn't exist =====+===== 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 
 + 1=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> <code sql>
Line 56: Line 243:
  ADD Column_Name INT NULL;  ADD Column_Name INT NULL;
 END END
 +</code>
 +
 +==== Rename a column ====
 +
 +<code sql>
 +EXEC sp_rename 'Categories.Active', 'Active_Old', 'COLUMN'
 </code> </code>
  
Line 83: Line 276:
 </code> </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 ===== ===== Most Recent =====
  
Line 115: Line 414:
  AllHistory.Id IS NULL  AllHistory.Id IS NULL
 </code> </code>
- 
- 
  
 ===== Table Variables ===== ===== Table Variables =====
  
-**Use a Common Table Expression instead!**+**Use a Nested SELECT Statement or a Common Table Expression instead!**
  
 <code sql> <code sql>
Line 131: Line 428:
 ===== Stored Procedure into Table Variable ===== ===== Stored Procedure into Table Variable =====
  
-**Use a Common Table Expression instead!**+**Use a Nested SELECT Statement or a Common Table Expression instead!**
  
 <code sql> <code sql>
Line 145: Line 442:
 </code> </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 ===== ===== Randomness =====
  
Line 151: 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>
 +
 +===== 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> </code>
  
Line 187: 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:+===== Creating a Login or User ===== 
 + 
 +This is particularly useful when the database is on Azure.
  
 <code sql> <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> </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> <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> </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 ===== ===== Joins =====
sql_cheat_sheet.1391738026.txt.gz · Last modified: 2017/01/01 19:52 (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki