Cheat Sheet - MSSQL

Iterate over all tables in a db and count its rows

declare @name varchar(100)
declare @sql nvarchar(300)

declare cur cursor for
    select name from sys.tables where type = 'U' and schema_id = 1
open cur

fetch next from cur into @name

while @@FETCH_STATUS = 0
	begin
		set @sql = 'select count(*) as ' + @name + ' from ' + @name      
		exec Sp_executesql @sql
		fetch next from cur into @name
	end
close cur
deallocate cur

Do an identity insert

SET IDENTITY_INSERT tblFoo ON 

INSERT INTO tblFoo (id, col2, col3, ...) 
VALUES (idValue, ...)

SET IDENTITY_INSERT tblFoo OFF

Create login and user

-- switch to master database
create login unclesam with password='foobar!';

-- switch to target database
create user unclesam from login unclesam;

-- give db_owner role 
EXEC sp_addrolemember 'db_owner', 'unclesam';

-- remember to login with unclesam@sql-server 
-- and select target database on login

List open transactions

-- For all databases
SELECT * FROM sys.sysprocesses WHERE open_tran = 1

-- For the current database
DBCC OPENTRAN

Get a list of supported collations

select name, description 
from sys.fn_helpcollations()
where name like ...;

Copy from one table to another

INSERT INTO newTable
SELECT * FROM oldTable

Or if only specific colums should be copied

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable

Move table to another schema

ALTER SCHEMA <targetSchema> TRANSFER sourceSchema.Table

Delete all tables in an Azure SQL database

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    WHILE @constraint is not null
    BEGIN
        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'
        EXEC (@SQL)
        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name
        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
    END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
    EXEC (@SQL)
    PRINT 'Dropped Table: ' + @name
    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Rename column

EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'

Add primary key

alter table <table_name> add primary key (<column>);