Keyboard shortcuts

Press or to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

T-SQL

Get all tables in a database.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM <database_name>.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME

Get all views in a database.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM <database_name>.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY TABLE_SCHEMA, TABLE_NAME

Get all stored procedures in a database.

SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME
FROM <database_name>.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME

Get the current identity id:

SELECT IDENT_CURRENT('dbo.CredentialingStatus')

Temporarily allow identity inserting (insert a new record with a particular Id):

SET IDENTITY_INSERT dbo.CredentialingStatus ON
INSERT INTO dbo.CredentialingStatus (Id, Name, CreatedDate, CreatedBy) VALUES (9, 'Test', CURRENT_TIMESTAMP, 'jskemp')
SET IDENTITY_INSERT dbo.CredentialingStatus OFF

Table altering, in T-SQL

Change table schema

Change the schema on an existing table.

ALTER SCHEMA projectManagement
TRANSFER dbo.RequestPriority

Change column

Alter a column in an existing table.

ALTER TABLE [projectManagement].[Task]
ALTER COLUMN Name varchar(250) not null

Add identity and primary key

Add a new identity column to an existing table.

-- See http://stackoverflow.com/a/3698824/11912
ALTER TABLE JobSupplies add Id INT IDENTITY
ALTER TABLE JobSupplies add constraint PK_JobSupplies primary KEY(Id)