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