Cacher is the code snippet organizer for pro developers

-- Rename table, stored procedure - first enter old name, then comma, then new name. You'll get a warning abut changing the name, but it will still go ahead and change it.
sp_rename 'chitemp3', 'chitempnew'

-- Rename column, stored procedure - first enter tablename.columnname, then comma, then new name for column, then the word 'COLUMN', all in single quotes. You'll get a warning abut changing the name, but it will still go ahead and change it.
sp_rename 'chitemp2.TMIN', 'TMinimum', 'COLUMN'

-- Add column to table. 
ALTER TABLE orders13
ADD COLUMN Nickname varchar(20) NOT NULL;

-- Add column to table, using DEFAULT value to be added from here on out. If column is nullable, DEFAULT value will NOT be written to each row already in the table, but it will be written to all new rows.
ALTER TABLE orders13
ADD Nickname varchar(20) DEFAULT 'this is the default';

-- Add column to table with DEFAULT value that is written to all existing rows, plus will be written to all future rows. If column is NOT NULL, DEFAULT value will be written to all existing rows, and will be added to new rows. DEFAULT can also be used if you're just wanting to create a NOT NULL column, but you want to fill in all pre-existing rows with something so they're NOT NULL. Create the column as NOT NULL with a DEFAULT to fill in all existing rows, then drop the DEFAULT constraint added when you created the column - the column will not longer have a DEFAULT, therefore making the user enter a value each time a new row is added.  
ALTER TABLE orders13
ADD COLUMN Nickname varchar(20) NOT NULL DEFAULT 'this is the default';

-- Delete column from table
ALTER TABLE orders13
DROP COLUMN Nickname;

-- Change data type of column, use ALTER COLUMN. Note that you're required to restate data type and NULL/NOT NULL each time you ALTER COLUMN. You can't add or change a DEFAULT value using ALTER COLUMN. Rather, you have to delete-then-add the DEFAULT constraint with the new value - go to the Object Manager and the Constraints folder to find name and then alter that constraint name.
ALTER TABLE chitemp2
ALTER COLUMN Nickname varchar(20) NOT NULL;

-- Change order of columns
Cannot be done using a query. Go to Object Explorer, right click table, click Design, and then reorder by dragging the column (apparently you have to turn off 'Prevent saving changes that require table re-creation' - this is in Tools > Options > Designers)

-- Add Primary Key constraint to table, ID column will become Primary Key in chitemp5 table. Table must have contents that allow it to become Primary Key - for instance, if there are duplicate values in the column or if it allows NULLs, it won't let you assign the Primary Key. If you try to add a Primary Key to a column that already has duplicate values, you can instead add a composite Primary Key to multiple columns that combined are unique. See example below.
ALTER TABLE chitemp5 
ADD PRIMARY KEY (ID);

-- Add composite Primary Key constraint to table, ID and Station columns will together become a Primary Key (for instance, if you want to add Primary Key to ID but ID is already populated with duplicate values, you can instead choose ID and another column that together create a unique combination of values for each row). Both Primary Key columns must be NOT NULL.
ALTER TABLE chitemp5 
ADD PRIMARY KEY (ID, Station);

xxx
-- Add CONSTRAINT, Foreign Key to a column 
ALTER TABLE orders13 ADD CONSTRAINT fk_ProductID FOREIGN KEY (ProductID) 
REFERENCES products1 (ProductID) ON UPDATE CASCADE ON DELETE CASCADE;

xxx
-- Add a constraint, such as a column DEFAULT value. This can also be used to add CHECKs. After the FOR, you place the column name you'd like to add the constraint to.
ALTER TABLE chitemp2
ADD CONSTRAINT df_chitemp2_MainNickname DEFAULT 'default nickname' FOR MainNickname2;

-- Change an existing constraint, such as a column DEFAULT value. Best to drop the existing constraint, and then add a new one with the updated value.
ALTER TABLE chitemp2
DROP CONSTRAINT df_chitemp2_MainNickname;

ALTER TABLE chitemp2
ADD CONSTRAINT df_chitemp2_MainNickname DEFAULT 'updated nickname' FOR MainNickname2;

-- Rename table
ALTER TABLE orders13 RENAME TO orders14;

-- Add column to table. If you're adding NOT NULL column to table that already exists, it's apparently best to add the column as NULL, add values to all the rows that already exist in the table, and then MODIFY the column to NOT NULL.  
ALTER TABLE orders13
ADD COLUMN Nickname varchar(20) NOT NULL DEFAULT 'none entered';

-- Delete column from table
ALTER TABLE orders13
DROP COLUMN Nickname;

-- Change data type of column (apparently if there's already data in the column that doesn't match the new data type that you're changing the column to, it might change the values to fit the new data type, which might make this old data useless).
ALTER TABLE orders13
MODIFY RefundCode INT;

-- Change order of columns using AFTER, it will place the column to the right of column you tell it to go AFTER. Apparently when using MODIFY you need to enter the data type and any default you have, even if you're just reaffirming what's already there. If you don't enter data type, it won't work, and if you don't enter DEFAULT, it apparently erases the DEFAULT that's already there.
ALTER TABLE orders13
MODIFY PromoPoints varchar(20) DEFAULT 'AAAA' AFTER RefundCode;

-- Change DEFAULT value of column, use MODIFY, apparently you still have to define the data type for the column you're moving (even if you're just reaffirming the data type that's already there). You can change a data type and move the column in the same statement, as seen below.
ALTER TABLE orders13
MODIFY PromoPoints varchar(20) DEFAULT 'AAAA';

-- Add CONSTRAINT, Foreign Key to a column
ALTER TABLE orders13 ADD CONSTRAINT fk_ProductID FOREIGN KEY (ProductID) 
REFERENCES products1 (ProductID) ON UPDATE CASCADE ON DELETE CASCADE;