MS SQL Samples

Rename a table

Exec sp_rename [CurrentTableName], [NewTableName]

Rename column

EXEC sp_rename '[tablename].[CurrentColumnName]', 'NewColumnName', 'COLUMN'

Add a primary key to a table

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
 ALTER TABLE [dbo].[TableName] WITH NOCHECK ADD
CONSTRAINT [PK_TableName] PRIMARY KEY CLUSTERED
(
    [column_pk]
)  ON [PRIMARY]
GO COMMIT

Add a foreign key

ALTER TABLE [dbo].[TableName] ADD
CONSTRAINT [FK_TableName_column_fk] FOREIGN KEY
(
    [column_fk]
) REFERENCES [dbo].[LinkedTable] (
    [LinkedField]
)
GO

Check if a field exists

if exists (select * from syscolumns where name = 'location_active')
        alter table [location] drop column 'location_active'
    GO

Looping in an stored procedure

DECLARE oCursor CURSOR FOR
    select advert_id, advert_code
    from [advertising]

OPEN oCursor

Declare @id as int
Declare @code as nvarchar(100)

FETCH NEXT FROM oCursor INTO @id, @code
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@Fetch_Status<>0 BREAK
    update [_orders] set
    advert_id = @id
    where sourcecode = @code

FETCH NEXT FROM oCursor INTO @id, @code
END

CLOSE oCursor
DEALLOCATE oCursor
GO

Create Table with incrementing primary key

CREATE TABLE [dbo].[Tutors] (
    [tutor_id] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
    [tutor_fname] [nvarchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
    [tutor_lname] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
    [tutor_phone] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [tutor_mobile] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
    [tutor_email] [nvarchar] (150) COLLATE Latin1_General_CI_AS NULL ,
    [tutor_address] [nvarchar] (200) COLLATE Latin1_General_CI_AS NULL ,
    [tutor_active] [bit] NOT NULL ,
    [tutor_created] [smalldatetime] NOT NULL,
    Primary Key (tutor_id)
) ON [PRIMARY]
GO

Return new primary key for Insert

declare @adminid int

insert into administrators
    (admin_username, admin_password, admin_created, admin_active, admin_fname, admin_lname)
    values
    ('unknown', 'unknown', '19000101 00:00:00', 0, 'unknown', 'unknown')
    set @AdminId = @@identity

Create a Foreign Key Constraint

ALTER TABLE Books
    ADD CONSTRAINT fk_author
    FOREIGN KEY (AuthorID)
    REFERENCES Authors (AuthorID) ON DELETE CASCADE
    GO

Kill ’sleeping’ users

declare oCursor Cursor for
    select spid, loginame
    from master..sysprocesses
    where status = 'sleeping'
        and loginame <> 'sa'

open oCursor

Declare @spid as int
Declare @loginame as nvarchar(50)
Declare @sql as nvarchar(50)

FETCH NEXT FROM oCursor INTO @spid, @loginame
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@Fetch_Status<>0 BREAK
        print @loginame + ' = ' + Convert(nvarchar(20), @spid)
        --set @sql = 'kill ' + Convert(char, @spid)
        --exec sp_executesql @sql

FETCH NEXT FROM oCursor INTO @spid, @loginame
END

CLOSE oCursor
DEALLOCATE oCursor
GO

Delimited Data cleansing with CharIndex

update [filestore] set
file_name = Left(file_name, CharIndex(';', file_name) - 1)
where  file_name IS NOT NULL
and CharIndex(';', file_name) > 0)

msdn.microsoft.com SQL Server Reference