- Contents
Custom ODBC Contact Directories Technical Reference
SQL Server Contact Schema Installation Script
/***********************************************
* Desc: Create the table Contacts
* $Id: //depot/systest/eic/main/products/eic/src/sql/sqlserver/08table/Contacts.TAB#5
$
* $Author: Jared.Alford $
* $Date: 2003/05/07 $
* $Change: 27477 $
***********************************************/
/**************************************/
/****** Object: Table Contacts ******/
/**************************************/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[Contacts]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table Contacts (
[ID] nvarchar
(25) NOT NULL,
Owner nvarchar
(25) NULL ,
Access int
NULL ,
[Name] nvarchar
(64) NULL ,
LastName nvarchar
(30) NULL ,
FirstName nvarchar
(30) NULL ,
Company nvarchar
(64) NULL ,
Title nvarchar
(32) NULL ,
Department nvarchar
(64) NULL ,
BusinessAddress nvarchar (128) NULL
,
BusinessCity nvarchar
(30) NULL ,
BusinessState nvarchar
(6) NULL ,
BusinessZip nvarchar
(15) NULL ,
BusinessCountry nvarchar (30) NULL
,
BusinessEMail nvarchar
(128) NULL ,
Assistant nvarchar
(64) NULL ,
HomeAddress nvarchar
(128) NULL ,
HomeCity nvarchar
(30) NULL ,
HomeState nvarchar
(6) NULL ,
HomeZip nvarchar
(15) NULL ,
HomeCountry nvarchar
(30) NULL ,
HomeEMail nvarchar
(128) NULL ,
BusinessPhone nvarchar
(80) NULL ,
BusinessPhone2 nvarchar (80)
NULL ,
HomePhone nvarchar
(80) NULL ,
HomePhone2 nvarchar
(80) NULL ,
Mobile nvarchar
(80) NULL ,
Fax nvarchar
(80) NULL ,
Pager nvarchar
(80) NULL ,
AssistantPhone nvarchar (80)
NULL ,
URL nvarchar
(255) NULL ,
PrimaryNumber nvarchar
(2) NULL ,
Notes nvarchar
(2000) NULL ,
Password nvarchar
(12) NULL
) on [PRIMARY]
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO
--
IF OBJECTPROPERTY (object_id(N'[dbo].[Contacts]') , 'TableHasPrimaryKey'
) = 0
ALTER TABLE [dbo].[Contacts]
ADD CONSTRAINT [PK_Contacts] PRIMARY
KEY CLUSTERED ([ID])
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO
--
/***********************************************
* Desc: Create the table SpeedDialList
* $Id: //depot/systest/eic/main/products/eic/src/sql/sqlserver/08table/SpeedDialList.TAB#5
$
* $Author: Jared.Alford $
* $Date: 2003/05/07 $
* $Change: 27477 $
***********************************************/
/*********************************************/
/****** Object: Table SpeedDialList ******/
/*********************************************/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SpeedDialList]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table SpeedDialList (
[ID] nvarchar
(25) NOT NULL ,
Owner nvarchar (25)
NULL ,
Access int NULL ,
ListName nvarchar (80) NULL
) on [PRIMARY]
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO
--
IF OBJECTPROPERTY (object_id(N'[dbo].[SpeedDialList]') , 'TableHasPrimaryKey'
) = 0
ALTER TABLE [dbo].[SpeedDialList]
ADD CONSTRAINT [PK_SpeedDialList] PRIMARY
KEY CLUSTERED ([ID])
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO
--
/***********************************************
* Desc: Create the table SpeedDial
* $Id: //depot/systest/eic/main/products/eic/src/sql/sqlserver/08table/SpeedDial.TAB#5
$
* $Author: Jared.Alford $
* $Date: 2003/05/07 $
* $Change: 27477 $
***********************************************/
/**************************************/
/****** Object: Table SpeedDial ******/
/**************************************/
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[SpeedDial]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table SpeedDial (
[ID] nvarchar
(25) NOT NULL ,
ListID nvarchar
(25) NOT NULL ,
ContactSource nvarchar
(64) NOT NULL ,
ContactID nvarchar
(255) NOT NULL ,
SpeedDialNumber nvarchar (2)
NULL ,
AdditionalData nvarchar
(128) NULL
) on [PRIMARY]
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO
--
IF OBJECTPROPERTY (object_id(N'[dbo].[SpeedDial]') , 'TableHasPrimaryKey'
) = 0
ALTER TABLE [dbo].[SpeedDial]
ADD CONSTRAINT [PK_SpeedDial] PRIMARY
KEY CLUSTERED ([ID])
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO
--
---- remove any system named foreign key on ListID -----
DECLARE @FKname VARCHAR(255),
@SQLstring VARCHAR(255)
SELECT @FKname = so.name
FROM sysobjects so
INNER JOIN syscolumns sc ON sc.id = so.parent_obj
INNER JOIN sysforeignkeys sfk ON so.id = sfk.constid
AND sc.colid = sfk.fkey
INNER JOIN syscolumns scref ON scref.id = sfk.rkeyid
WHERE so.xtype = 'F'
AND so.parent_obj = object_id('SpeedDial')
AND sc.name = 'ListID'
AND sfk.rkeyid = object_id('SpeedDialList')
AND scref.name = 'ID'
SELECT @SQLstring = 'ALTER TABLE [dbo].[SpeedDial] DROP CONSTRAINT ' +
ISNULL(@FKname,'')
IF @FKname IS NOT NULL
EXEC(@SQLstring)
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO
--
---- use explicit foreign key names from now on!!! -----
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FK_SpeedDial_ListID]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SpeedDial]
DROP CONSTRAINT [FK_SpeedDial_ListID]
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO
--
IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[FK_SpeedDial_ListID]')
and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SpeedDial] WITH
NOCHECK
ADD CONSTRAINT [FK_SpeedDial_ListID]
FOREIGN KEY ([ListID])
REFERENCES [dbo].[SpeedDialList] ([ID])
IF (@@ERROR = 0) and EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'[dbo].[spinin_update_cksum]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
EXEC spinin_update_cksum 'IC', 'Running'
GO