Genius bit of code for find MS SQL table sizes

http://www.databasejournal.com/img/BigTables.sql

/*****************************************************
*  BigTables.sql
*  Bill Graziano (SQLTeam.com)
*  graz@sqlteam.com
*  v1.1
*
*****************************************************/

declare @id	int
declare @type	character(2)
declare	@pages	int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage	dec(15,0)
declare @pagesperMB		dec(15,0)

create table #spt_space
(
	objid		int null,
	rows		int null,
	reserved	dec(15) null,
	data		dec(15) null,
	indexp		dec(15) null,
	unused		dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select	id
from	sysobjects
where	xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

	/* Code from sp_spaceused */
	insert into #spt_space (objid, reserved)
		select objid = @id, sum(reserved)
			from sysindexes
				where indid in (0, 1, 255)
					and id = @id

	select @pages = sum(dpages)
			from sysindexes
				where indid < 2
					and id = @id
	select @pages = @pages + isnull(sum(used), 0)
		from sysindexes
			where indid = 255
				and id = @id
	update #spt_space
		set data = @pages
	where objid = @id

	/* index: sum(used) where indid in (0, 1, 255) - data */
	update #spt_space
		set indexp = (select sum(used)
				from sysindexes
				where indid in (0, 1, 255)
				and id = @id)
			    - data
		where objid = @id

	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
	update #spt_space
		set unused = reserved
				- (select sum(used)
					from sysindexes
						where indid in (0, 1, 255)
						and id = @id)
		where objid = @id

	update #spt_space
		set rows = i.rows
			from sysindexes i
				where i.indid < 2
				and i.id = @id
				and objid = @id

	fetch next from c_tables
	into @id
end

select top 25
	Table_Name = (select left(name,25) from sysobjects where id = objid),
	rows = convert(char(11), rows),
	reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
	data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
	index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
	unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

from 	#spt_space, master.dbo.spt_values d
where 	d.number = 1
and 	d.type = 'E'
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

String Manipulation in MS SQL Server

http://www.informit.com/articles/article.asp?p=25040&rl=1

MS Access SQL statements

Create Table with Incremental Primary Key

Create Table [Courses] (
	[course_id] Counter NOT NULL,
	[course_title] text(100) NOT NULL,
	[course_date] datetime NOT NULL,
	[course_description] memo NOT NULL,
	[course_places] int NOT NULL,
	[course_active] yesno not null,
	[course_created] timestamp NOT NULL,
	[course_location_id] int NOT NULL,
	foreign key (course_location_id) references locations(location_id),
	Primary Key (course_id)
)

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