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 [2013/02/24 21:40] – [Table Variables] 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>
  
 +===== 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>
 +IF COL_LENGTH('Table_Name','Column_Name') IS NULL
 +BEGIN
 + ALTER TABLE Table_Name
 + ADD Column_Name INT NULL;
 +END
 +</code>
 +
 +==== Rename a column ====
 +
 +<code sql>
 +EXEC sp_rename 'Categories.Active', 'Active_Old', 'COLUMN'
 +</code>
 +
 +===== Create or alter a stored procedure =====
 +
 +If you want to preserve permissions:
 +
 +<code sql>
 +IF OBJECT_ID('StoredProcedure_Name') IS NULL
 + EXEC ('CREATE PROCEDURE StoredProcedure_Name AS SELECT 1')
 +GO
 +ALTER PROCEDURE StoredProcedure_Name
 +(
 +...
 +</code>
 +
 +If you are happy to blow away permissions:
 +
 +<code sql>
 +IF OBJECT_ID('StoredProcedure_Name', 'P') IS NOT NULL
 + DROP PROC StoredProcedure_Name
 +
 +GO
 +CREATE PROCEDURE StoredProcedure_Name
 +(
 +...
 +</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 56: Line 392:
 INNER JOIN INNER JOIN
  History MostRecentHistory  History MostRecentHistory
- ON Foo.Id = MostRecentHistory.Id+ ON MostRecentHistory.FooId = Foo.Id
 LEFT JOIN LEFT JOIN
  History AllHistory  History AllHistory
- ON MostRecentHistory.Id = AllHistory.Id + ON AllHistory.FooId = MostRecentHistory.FooId 
- AND MostRecentHistory.LastUpdateOn < AllHistory.LastUpdateOn+ AND AllHistory.LastUpdateOn > MostRecentHistory.LastUpdateOn
 WHERE WHERE
  AllHistory.Id IS NULL  AllHistory.Id IS NULL
Line 70: Line 406:
 LEFT JOIN LEFT JOIN
  History MostRecentHistory  History MostRecentHistory
- ON Foo.Id = MostRecentHistory.Id+ ON MostRecentHistory.FooId = Foo.Id
 LEFT JOIN LEFT JOIN
  History AllHistory  History AllHistory
- ON MostRecentHistory.Id = AllHistory.Id + ON AllHistory.FooId = MostRecentHistory.FooId 
- AND MostRecentHistory.LastUpdateOn < AllHistory.LastUpdateOn+ AND AllHistory.LastUpdateOn > MostRecentHistory.LastUpdateOn
 WHERE WHERE
  AllHistory.Id IS NULL  AllHistory.Id IS NULL
 </code> </code>
- 
- 
  
 ===== Table Variables ===== ===== Table Variables =====
 +
 +**Use a Nested SELECT Statement or a Common Table Expression instead!**
  
 <code sql> <code sql>
Line 91: Line 427:
  
 ===== Stored Procedure into Table Variable ===== ===== Stored Procedure into Table Variable =====
 +
 +**Use a Nested SELECT Statement or a Common Table Expression instead!**
  
 <code sql> <code sql>
Line 104: 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 110: 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 116: Line 660:
 <code sql> <code sql>
 -- Replace username with the actual username. -- Replace username with the actual username.
-USE database+USE DATABASE
 EXEC sp_change_users_login 'report' EXEC sp_change_users_login 'report'
-EXEC sp_change_users_login 'auto_fix', 'username' -- This definitely works.+EXEC sp_change_users_login 'auto_fix', 'CIPLogin' -- This definitely works.
 -- Or try: -- Or try:
 EXEC sp_change_users_login @Action = 'auto_fix', @UserNamePattern = 'User', @Password = 'Password' EXEC sp_change_users_login @Action = 'auto_fix', @UserNamePattern = 'User', @Password = 'Password'
Line 125: Line 669:
 USE QuantateAdministration USE QuantateAdministration
 EXEC sp_change_users_login 'update_one', 'QuantateSysAdmin', 'Quantate_Login' EXEC sp_change_users_login 'update_one', 'QuantateSysAdmin', 'Quantate_Login'
 +-- CIP
 +USE CIP
 +EXEC sp_change_users_login 'update_one', 'CIPUser', 'CIPLogin'
 +-- NGA Provisioning
 +USE NextGenProvDB
 +EXEC sp_change_users_login 'update_one', 'NextGenUser', 'NextGenLogin'
 +-- General. 
 +EXEC sp_change_users_login 'update_one', 'DBUser', 'ServerUser'
 +-- Server -> Security -> Logins -> *ServerUser* -> Right click -> Properties
 +-- User Mapping -> Find correct database, value in User col = *DBUser*
 </code> </code>
  
Line 136: 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.1361742005.txt.gz · Last modified: 2017/01/01 19:52 (external edit)
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki