SQL Cheat Sheet

Basic SQL Statements

Select

Get data out of a table.

SELECT TOP (1000) [Id]
,[Name]
FROM [dbo].[Hello]

Insert

Insert new data to an existing table

INSERT INTO Users (username, mail)
VALUES ('username', 'mail');

Delete

Delete stuff from a table

DELETE FROM table_name WHERE condition;

Update

Update stuff within a table

UPDATE Cities  
SET Location.X = 23.5  
WHERE Name = 'Anchorage';  

Special Functions

Insert Geometry

for spatial data in SQL Server

INSERT INTO "MyCoordinates" ("Geometry") VALUES ('POLYGON((0 0, 100 0, 100 100, 0 100, 0 0))',);

Rename Specific text in table

• To rename a specific string for each row in a given table in Microsoft SQL Database, you can use the REPLACE function in a SQL update statement. Here’s an example SQL script:

  UPDATE my_table
  SET my_column = REPLACE(my_column, 'old_string', 'new_string')
  WHERE my_column LIKE '%old_string%';

• In this script, you would replace my_table with the name of the table you want to update, and my_column with the name of the column containing the string you want to replace. You would also replace 'old_string' with the string you want to replace, and 'new_string' with the new string you want to use.
• The WHERE clause limits the update to only those rows that contain the old string, to avoid changing any data that doesn’t need to be updated.
• Note that this script only updates a single column in the table. If you want to update multiple columns with different strings, you would need to write a separate UPDATE statement for each column.

Get last Updated Columns

  select
      object_name(object_id) as OBJ_NAME, *
  from
      sys.dm_db_index_usage_stats
  where
      database_id = db_id(db_name())
  Order by
      dm_db_index_usage_stats.last_user_update desc

Get Last Table schema updates

  SELECT name [Table],
         Create_date [CreateDate],
         modify_date [LastUpdate]
  FROM sys.all_objects
  WHERE type = 'U'
  ORDER BY modify_date DESC;

Replace String

  UPDATE Maps
  SET Uri = REPLACE(Uri, 'uri1', 'uri2')

Azure SQL related stuff

Add Read User to Azure SQL DB

1. Add new Login to master DB

CREATE LOGIN yourusername WITH password='yourpw';

2. Add user to your specific db

CREATE USER [yourusername] FROM LOGIN yourusername;

3. Add User to role of your choosing

ALTER ROLE [db_datareader] ADD MEMBER [yourusername];

Rename Azure DB

  USE master;
  ALTER DATABASE MyTestDatabase MODIFY NAME = MyTestDatabaseCopy;