Retrieve a list of tables

SELECT name FROM sysobjects WHERE xType = 'U' ORDER BY name

Table and Index Sizes

Use the following undocumented stored procedure to determine the number of rows in a table, and the amount of space used by the tables data and indexes.

sp_MStablespace 'object'

Return data in XML format

Queries to return data in XML format (SQL Server 2000+)

SELECT * FROM tablename ORDER BY fieldname FOR XML AUTO, ELEMENTS

Back up a database

backup database MyDb to disk "C:\backup.dmp"

List

SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
ORDER BY [name];

--  FN    SQL Scalar Function 
--  IF    Inline Table Valued Function 
--  P     SQL Stored Procedure 
--  PC    CLR Stored Procedure 
--  TF    SQL Table Valued Function 
--  V     View 
--  X     Extended Stored Procedure 

-- List only custom stored procedures:

SELECT * FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
AND is_ms_shipped = 0
ORDER BY [name];

Changing NOT NULL columns to allow NULL values

ALTER TABLE MyTable ALTER COLUMN MyColumn varchar(100) NULL

SELECT statement within a CASE statement

SELECT t1.col1, 
        t1.col2, 
        t1.col3,        
        CASE
          WHEN t1.col4 = 1 THEN
            (SELECT t2.description FROM table2 t2 WHERE  t2.x = t1.y)
          ELSE
            'Other'
        END AS details,
        t1.col12
 FROM table1 t1
 WHERE  t1.col5 = 27

Move objects to a new schema

How to transfer an object from one schema to another

alter schema NewSchema transfer OldSchema.ObjectName