Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mnarewec
Starting Member
10 Posts |
 Posted - 2010-10-12 : 21:48:53
|
| Hi Team,I have SQL Script which creates database and its objects. It suppose to execute statements to create objects if a particular table does not exist in the database. If that table exist in the database then prints a line that says "Database Objects Already Exists"My Script below works for a database that does not exist. But Does not works for a database that exist. Please help++++++++++++++++++++++++++++++++++++++++++++++++++++ /*Create database*/ if not exists (select * from sysdatabases where name = N'psfixedassets') create database psfixedassets GO /****** Object: Login NT AUTHORITY\SYSTEM Script Date: 22/07/2010 09:46:55 ******/ if not exists (select * from master.dbo.syslogins where loginname = N'NT AUTHORITY\SYSTEM') exec sp_grantlogin N'NT AUTHORITY\SYSTEM' exec sp_defaultdb N'NT AUTHORITY\SYSTEM', N'master' exec sp_defaultlanguage N'NT AUTHORITY\SYSTEM', N'us_english' GO /****** Object: Login pacsoftuser Script Date: 22/07/2010 09:46:55 ******/ if not exists (select * from master.dbo.syslogins where loginname = N'pacsoftuser') BEGIN declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english' if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb) select @logindb = N'master' if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english') select @loginlang = @@language exec sp_addlogin N'pacsoftuser', 'pacs0ftuser', @logindb, @loginlang END GO /****** Object: Login sa Script Date: 22/07/2010 09:46:55 ******/ if not exists (select * from master.dbo.syslogins where loginname = N'sa') BEGIN declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english' if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb) select @logindb = N'master' if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english') select @loginlang = @@language exec sp_addlogin N'sa', null, @logindb, @loginlang END GO /****** Object: Login BUILTIN\Administrators Script Date: 22/07/2010 09:46:55 ******/ if not exists (select * from master.dbo.syslogins where loginname = N'BUILTIN\Administrators') exec sp_grantlogin N'BUILTIN\Administrators' exec sp_defaultdb N'BUILTIN\Administrators', N'master' exec sp_defaultlanguage N'BUILTIN\Administrators', N'us_english' GO /****** Object: Login BUILTIN\Users Script Date: 22/07/2010 09:46:55 ******/ if not exists (select * from master.dbo.syslogins where loginname = N'BUILTIN\Users') exec sp_grantlogin N'BUILTIN\Users' exec sp_defaultdb N'BUILTIN\Users', N'master' exec sp_defaultlanguage N'BUILTIN\Users', N'us_english' GO exec sp_addsrvrolemember N'pacsoftuser', sysadmin GO /****** Object: User pacsoftuser Script Date: 22/07/2010 09:46:56 ******/ if not exists (select * from dbo.sysusers where name = N'pacsoftuser') EXEC sp_grantdbaccess N'pacsoftuser' GO use [psfixedassets] GO /****** Object: Table [dbo].[AbkContacts] Script Date: 22/07/2010 09:46:56 ******/ If EXISTS (SELECT id FROM dbo.sysobjects where id = object_id(N'[dbo].[AbkContacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN PRINT 'DATABASE ALREADY EXIST SO OBJECTS NOT CREATED' ENDELSE BEGIN---START THE IS EXIST CREATE TABLE [dbo].[AbkContacts] ( [ContactId] [nchar] (10) NOT NULL , [ContactName] [nvarchar] (50) NOT NULL , [ContactType] [nchar] (10) NULL , [PostalAddress] [nvarchar] (50) NULL , [PhysicalAddress] [nvarchar] (50) NULL , [Telephone] [nchar] (20) NULL , [Fax] [nchar] (20) NULL , [Email] [nvarchar] (50) NULL , [ContactPerson] [nvarchar] (50) NULL , CONSTRAINT [PK_AbkContacts] PRIMARY KEY CLUSTERED ( [ContactId] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[AppProduct] Script Date: 22/07/2010 09:46:58 ******/ ----if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AppProduct]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[AppProduct] ( [OrganisationName] [nvarchar] (50) NOT NULL , [LicenceType] [nchar] (10) NULL , [SerialNumber] [nvarchar] (50) NULL , [ActivationCode] [nvarchar] (50) NULL , [DatabaseVersion] [nchar] (20) NULL , [InstallationDate] [datetime] NULL , CONSTRAINT [PK_AppProduct] PRIMARY KEY CLUSTERED ( [OrganisationName] ) ON [PRIMARY] ) ON [PRIMARY] END GO ----if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AssetAudit]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[AssetAudit] ( [AssetNumber] [nchar] (20) NOT NULL , [AuditId] [bigint] IDENTITY (1, 1) NOT NULL , [AuditDate] [datetime] NULL , [Auditor] [nvarchar] (30) NULL , [Report] [nvarchar] (400) NULL , CONSTRAINT [PK_AssetAudit] PRIMARY KEY CLUSTERED ( [AssetNumber], [AuditId] ) ON [PRIMARY] ) ON [PRIMARY] END GO SET IDENTITY_INSERT [dbo].[AssetAudit] ON SET IDENTITY_INSERT [dbo].[AssetAudit] OFF /****** Object: Table [dbo].[AssetMaintenance] Script Date: 22/07/2010 09:46:59 ******/ BEGIN CREATE TABLE [dbo].[AssetMaintenance] ( [AssetNumber] [nchar] (20) NOT NULL , [MaintenanceId] [bigint] IDENTITY (1, 1) NOT NULL , [MaintenanceDescription] [nvarchar] (400) NOT NULL , [MaintenanceDate] [datetime] NOT NULL , [MaintenanceProviderID] [nchar] (10) NULL , [MaintenanceCost] [money] NULL , [RevalueAmount] [money] NULL , CONSTRAINT [PK_AssetRepair] PRIMARY KEY CLUSTERED ( [AssetNumber], [MaintenanceId] ) ON [PRIMARY] ) ON [PRIMARY] END GO SET IDENTITY_INSERT [dbo].[AssetMaintenance] ON SET IDENTITY_INSERT [dbo].[AssetMaintenance] OFF /****** Object: Table [dbo].[AssetMaster] Script Date: 22/07/2010 09:46:59 ******/ BEGIN CREATE TABLE [dbo].[AssetMaster] ( [AssetNumber] [nchar] (20) NOT NULL , [Description] [nvarchar] (50) NOT NULL , [CostCentreID] [nchar] (10) NOT NULL , [AssetType] [nchar] (10) NOT NULL , [GroupMajorCode] [nchar] (10) NOT NULL , [GroupMinorCode] [nchar] (10) NOT NULL , [AcquisitionType] [nchar] (10) NULL , [AcquiredDate] [datetime] NULL , [AcquiredCost] [money] NULL , [CurrentBookValue] [money] NULL , [PONumber] [nchar] (20) NULL , [AccountSource] [nchar] (10) NULL , [GLAccount] [nchar] (10) NULL , [POReference] [nvarchar] (50) NULL , [ChqNumber] [nchar] (20) NULL , [SupplierID] [char] (10) NULL , [DisposalDate] [datetime] NULL , [DisposalMethod] [nchar] (10) NULL , [DisposalReason] [nvarchar] (50) NULL , [DisposalReference] [nvarchar] (50) NULL , [DisposalOwner] [nchar] (50) NULL , [DisposalAmount] [money] NULL , [DisposalPrice] [money] NULL , [ConditionCode] [nchar] (10) NULL , [WrittenDownValue] [money] NULL , [WrittenDownDate] [datetime] NULL , [RevaluedAmount] [money] NULL , [RevaluedDate] [datetime] NULL , [LastDepreciation] [datetime] NULL , [UsefulLifeSpan] [int] NULL , [DepreciationRate] [decimal](18, 4) NULL , [DepreciationMethod] [nchar] (10) NULL , [DepreciationAmount] [money] NULL , [DepreciationInterval] [nchar] (10) NULL , [StatusCode] [nchar] (10) NULL , [Picture1] [image] NULL , [Notes] [nvarchar] (100) NULL , [CreatedDate] [smalldatetime] NOT NULL , CONSTRAINT [PK_AssetMaster] PRIMARY KEY CLUSTERED ( [AssetNumber] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[AssetMovement] Script Date: 22/07/2010 09:46:59 ******/ BEGIN CREATE TABLE [dbo].[AssetMovement] ( [AssetNumber] [nchar] (20) NOT NULL , [LocationId] [nchar] (10) NULL , [StaffNumber] [nchar] (10) NULL , [AssignedStarts] [datetime] NULL , [AssignedEnds] [datetime] NULL , [MovementReference] [nvarchar] (200) NULL , [Status] [nchar] (10) NULL , CONSTRAINT [PK_AssetLocation] PRIMARY KEY CLUSTERED ( [AssetNumber] ) ON [PRIMARY] ) ON [PRIMARY] END ----GO /****** Object: Table [dbo].[AssetMovementHistory] Script Date: 22/07/2010 09:47:00 ******/ BEGIN CREATE TABLE [dbo].[AssetMovementHistory] ( [HistoryID] [bigint] IDENTITY (1, 1) NOT NULL , [LocationID] [nchar] (10) NOT NULL , [StaffNumber] [nchar] (10) NOT NULL , [AssetNumber] [nchar] (20) NOT NULL , [AssignedStarts] [datetime] NOT NULL , [AssignedEnds] [datetime] NOT NULL , [MovementReference] [nvarchar] (200) NULL , CONSTRAINT [PK_AssetAllocationHistory] PRIMARY KEY CLUSTERED ( [HistoryID] ) ON [PRIMARY] ) ON [PRIMARY] END GO SET IDENTITY_INSERT [dbo].[AssetMovementHistory] ON SET IDENTITY_INSERT [dbo].[AssetMovementHistory] OFF /****** Object: Table [dbo].[AssetTempReport] Script Date: 22/07/2010 09:47:00 ******/ BEGIN CREATE TABLE [dbo].[AssetTempReport] ( [TransNumber] [int] IDENTITY (1, 1) NOT NULL , [TransDate] [datetime] NOT NULL , [AssetNumber] [nchar] (20) NOT NULL , [Description] [nvarchar] (50) NULL , [CostCentreID] [nchar] (10) NULL , [AssetType] [nchar] (10) NULL , [GroupMajorCode] [nchar] (10) NULL , [GroupMinorCode] [nchar] (10) NULL , [TransactionAmount] [money] NULL , [Result] [nchar] (10) NULL , [Message] [nvarchar] (50) NULL , CONSTRAINT [PK_AssetTempReport] PRIMARY KEY CLUSTERED ( [TransNumber] ) ON [PRIMARY] ) ON [PRIMARY] END GO SET IDENTITY_INSERT [dbo].[AssetTempReport] ON SET IDENTITY_INSERT [dbo].[AssetTempReport] OFF /****** Object: Table [dbo].[AssetTransactions] Script Date: 22/07/2010 09:47:00 ******/ BEGIN CREATE TABLE [dbo].[AssetTransactions] ( [TranNumber] [bigint] IDENTITY (1000, 1) NOT NULL , [TranDate] [datetime] NOT NULL , [TranType] [nchar] (10) NOT NULL , [AssetNumber] [nchar] (20) NOT NULL , [TranDetail] [nvarchar] (50) NOT NULL , [TranAmount] [money] NOT NULL , CONSTRAINT [PK_AssetTransactions] PRIMARY KEY CLUSTERED ( [TranNumber] ) ON [PRIMARY] ) ON [PRIMARY] END ------GO SET IDENTITY_INSERT [dbo].[AssetTransactions] ON SET IDENTITY_INSERT [dbo].[AssetTransactions] OFF /****** Object: Table [dbo].[AssetUsers] Script Date: 22/07/2010 09:47:01 ******/ BEGIN CREATE TABLE [dbo].[AssetUsers] ( [StaffNumber] [nchar] (10) NOT NULL , [Title] [nchar] (10) NULL , [UserName] [nchar] (30) NOT NULL , [Designation] [nvarchar] (50) NULL , [Phone] [nchar] (10) NULL , [Email] [nvarchar] (50) NULL , [CostCentreID] [nchar] (10) NOT NULL , [Photo] [image] NULL , CONSTRAINT [PK_AssetUsers] PRIMARY KEY CLUSTERED ( [StaffNumber] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[DcgGroupMinors] Script Date: 22/07/2010 09:47:01 ******/ BEGIN CREATE TABLE [dbo].[DcgGroupMajors] ( [GroupTypeCode] [nchar] (10) NOT NULL , [GroupMajorCode] [nchar] (10) NOT NULL , [GroupMajorName] [nvarchar] (50) NOT NULL , CONSTRAINT [PK_DcgGroupMajors] PRIMARY KEY CLUSTERED ( [GroupTypeCode], [GroupMajorCode] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[DcgGroupMinors] Script Date: 22/07/2010 09:47:01 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DcgGroupMinors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[DcgGroupMinors] ( [GroupTypeCode] [nchar] (10) NOT NULL , [GroupMajorCode] [nchar] (10) NOT NULL , [GroupMinorCode] [nchar] (10) NOT NULL , [GroupMinorName] [nvarchar] (50) NOT NULL , CONSTRAINT [PK_DcgGroupMinors] PRIMARY KEY CLUSTERED ( [GroupTypeCode], [GroupMajorCode], [GroupMinorCode] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[DcgGroupTypes] Script Date: 22/07/2010 09:47:02 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DcgGroupTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[DcgGroupTypes] ( [GroupTypeCode] [nchar] (10) NOT NULL , [GroupTypeName] [nvarchar] (50) NOT NULL , CONSTRAINT [PK_DcgGroupTypes] PRIMARY KEY CLUSTERED ( [GroupTypeCode] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[OrgCostCentres] Script Date: 22/07/2010 09:47:02 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrgCostCentres]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[OrgCostCentres] ( [CostCentreID] [nchar] (10) NOT NULL , [CostCentreName] [nvarchar] (50) NOT NULL , [ZoneID] [nchar] (10) NOT NULL , [Address] [bigint] NULL , [Status] [nchar] (10) NULL , CONSTRAINT [PK_SysCostCentreID] PRIMARY KEY CLUSTERED ( [CostCentreID] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[OrgLocations] Script Date: 22/07/2010 09:47:02 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrgLocations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[OrgLocations] ( [LocationId] [nchar] (10) NOT NULL , [CostCentreID] [nchar] (10) NOT NULL , [LocationName] [nvarchar] (50) NOT NULL , [ApprovalPath] [int] NULL , [Status] [nchar] (4) NULL , [ContactInfo] [nvarchar] (50) NULL , CONSTRAINT [PK_OrgLocationId] PRIMARY KEY CLUSTERED ( [LocationId] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[OrgOrganisation] Script Date: 22/07/2010 09:47:02 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrgOrganisation]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[OrgOrganisation] ( [OrganisationName] [nvarchar] (50) NOT NULL , [AddressLine1] [nvarchar] (50) NULL , [AddressLine2] [nvarchar] (50) NULL , [AddressLine3] [nvarchar] (50) NULL , [AddressLine4] [nvarchar] (50) NULL , [Telephone] [nchar] (10) NULL , [Fax] [nchar] (10) NULL , [Email] [nvarchar] (50) NULL , [Website] [nchar] (10) NULL , CONSTRAINT [PK_OrgOrganisation] PRIMARY KEY CLUSTERED ( [OrganisationName] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[OrgZones] Script Date: 22/07/2010 09:47:03 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrgZones]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[OrgZones] ( [ZoneID] [nchar] (10) NOT NULL , [ZoneName] [nvarchar] (50) NOT NULL , CONSTRAINT [PK_OrgZoneID] PRIMARY KEY CLUSTERED ( [ZoneID] ) ON [PRIMARY] ) ON [PRIMARY] END GO /****** Object: Table [dbo].[SecFunctions] Script Date: 22/07/2010 09:47:03 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SecFunctions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[SecFunctions] ( [FunctionID] [nchar] (10) NOT NULL , [Function] [nvarchar] (50) NOT NULL , [IsActive] [bit] NULL , CONSTRAINT [PK_SecFunctions] PRIMARY KEY CLUSTERED ( [FunctionID] ) ON [PRIMARY] ) ON [PRIMARY] END GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('ABKCONMANT','Asset Suppliers ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('DCGMAJMANT','Data Class Groups Major ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('DCGMINMANT','Data Class Groups Minor ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('DCGTYPMANT','Data Class Groups Types ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FADEPRCALC','Fixed Asset Depreciation Calculation',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FADEPRESET','Fixed Asset Depreciation Reset',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FADISPOSAL','Fixed Asset Disposal',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FAINSPECT ','Fixed Asset Movement ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FAMOVEMENT','Fixed Asset Allocation ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FAMSTMANT ','Fixed Asset Master ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FAREPRMANT','Fixed Asset Maintenance',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FAREVALUE ','Fixed Asset Revaluation',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('FAUSERMANT','Fixed Asset Users ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('ORGCCMANT ','Organisation Cost Centre ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('ORGINFMANT','Organisation Information ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('ORGLOCMANT','Organisation Location ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('ORGZONMANT','Organisation Zone ',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('RPTFAADMIN','Fixed Assets Adminstration Reports',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('RPTFAREG ','Fixed Asset Register Reports',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('RPTSEC ','Security Reports',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('RPTSETUP ','Setup Reports',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('SECROLMANT','Security Roles',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('SECUSRMANT','Security Users',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('TOOBKPMANT','Backup Database',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('TOORSTMANT','Restore Database',1) --GO INSERT INTO [dbo].[SecFunctions] ([FunctionID],[Function],[IsActive]) VALUES ('UDPMANT ','User Define Values',1) --GO /****** Object: Table [dbo].[SecRoleFunctions] Script Date: 22/07/2010 09:47:04 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SecRoleFunctions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[SecRoleFunctions] ( [RoleID] [nchar] (10) NOT NULL , [FunctionID] [nchar] (10) NOT NULL , CONSTRAINT [PK_SecRoleFunctions] PRIMARY KEY CLUSTERED ( [RoleID], [FunctionID] ) ON [PRIMARY] ) ON [PRIMARY] END GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','ABKCONMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','DCGMAJMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','DCGMINMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','DCGTYPMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','FAUSERMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','ORGCCMANT ') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','ORGINFMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','ORGLOCMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','ORGZONMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','RPTSEC ') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','RPTSETUP ') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','SECROLMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','SECUSRMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','TOOBKPMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','TOORSTMANT') --GO INSERT INTO [dbo].[SecRoleFunctions] ([RoleID],[FunctionID]) VALUES ('SYSADM ','UDPMANT ') --GO /****** Object: Table [dbo].[SecRoles] Script Date: 22/07/2010 09:47:04 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SecRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[SecRoles] ( [RoleID] [nchar] (10) NOT NULL , [Role] [nvarchar] (50) NOT NULL , [IsActive] [bit] NULL , CONSTRAINT [PK_SecRoles] PRIMARY KEY CLUSTERED ( [RoleID] ) ON [PRIMARY] ) ON [PRIMARY] END GO INSERT INTO [dbo].[SecRoles] ([RoleID],[Role],[IsActive]) VALUES ('SYSADM ','System Administator',1) --GO /****** Object: Table [dbo].[SecRoleScreens] Script Date: 22/07/2010 09:47:04 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SecRoleScreens]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) --BEGIN CREATE TABLE [dbo].[SecRoleScreens] ( [RoleID] [nchar] (10) NOT NULL , [ScreenID] [nchar] (10) NOT NULL , [IsReadOnly] [bit] NULL , CONSTRAINT [PK_SecRoleScreens] PRIMARY KEY CLUSTERED ( [RoleID], [ScreenID] ) ON [PRIMARY] ) ON [PRIMARY] --END --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','ABKCONTACT',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','DCGSETWIZ ',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','FAUSERS ',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','ORGINFO ',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','ORGSETWIZ ',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','REPORTS ',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','SECROLEMGR',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','SECUSERMGR',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','TOOBKPDB ',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','TOORSTDB ',1) --GO INSERT INTO [dbo].[SecRoleScreens] ([RoleID],[ScreenID],[IsReadOnly]) VALUES ('SYSADM ','UDPARM ',1) --GO /****** Object: Table [dbo].[SecScreens] Script Date: 22/07/2010 09:47:05 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SecScreens]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) --BEGIN CREATE TABLE [dbo].[SecScreens] ( [ScreenID] [nchar] (10) NOT NULL , [Screen] [nvarchar] (50) NOT NULL , [IsActive] [bit] NULL , [MenuGroup] [nchar] (10) NULL , CONSTRAINT [PK_SecScreens] PRIMARY KEY CLUSTERED ( [ScreenID] ) ON [PRIMARY] ) ON [PRIMARY] --END --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('ABKCONTACT','Suppliers Screen',1,'SETUP ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('DCGSETWIZ ','Data Groups Setup Wizard',1,'SETUP ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FAAUDIT ','Fixed Assets Audit Screen',1,'ADMIN ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FADEPRE ','Fixed Assets Depreciation Screen',1,'ADMIN ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FADISPOSAL','Fixed Assets Disposal Screen',1,'ADMIN ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FAENQUIRY ','Fixed Assets Enquiry Screen',1,'SEARCH ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FAMOVEMENT','Fixed Assets Movement Screen',1,'ADMIN ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FAREGISTER','Fixed Assets Register Screen',1,'REGISTER ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FAREPAIR ','Fixed Assets Repair Screen',1,'ADMIN ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FASEARCH ','Fixed Assets Search Screen',1,'SEARCH ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('FAUSERS ','Fixed Asset Users Screen',1,'SETUP ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('ORGINFO ','Organisation Information Screen',1,'SETUP ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('ORGSETWIZ ','Organisation Setup Wizard',1,'SETUP ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('REPORTS ','Reports Manager Screen',1,'REPORTS ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('SECROLEMGR','Security Roles Manager Screen',1,'SECURITY ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('SECUSERMGR','Security Users Manager Screen',1,'SECURITY ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('TOOBKPDB ','Tools Backup Database Screen',1,'TOOLS ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('TOORSTDB ','Tools Restore Database Screen',1,'TOOLS ') --GO INSERT INTO [dbo].[SecScreens] ([ScreenID],[Screen],[IsActive],[MenuGroup]) VALUES ('UDPARM ','User Define Screen',1,'SETUP ') --GO /****** Object: Table [dbo].[SecUsers] Script Date: 22/07/2010 09:47:05 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SecUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[SecUsers] ( [UserID] [nvarchar] (20) NOT NULL , [UserName] [nvarchar] (50) NOT NULL , [CostCentreID] [nchar] (10) NOT NULL , [RoleID] [nchar] (10) NOT NULL , [Position] [nvarchar] (50) NULL , [StaffNumber] [nvarchar] (10) NULL , [Password] [nvarchar] (50) NULL , [LastLogin] [datetime] NULL , [IsLogin] [bit] NULL , [LastPasswordChange] [datetime] NULL , [IsActive] [bit] NULL , CONSTRAINT [PK_SecSystemUsers] PRIMARY KEY CLUSTERED ( [UserID] ) ON [PRIMARY] ) ON [PRIMARY] END GO INSERT INTO [dbo].[SecUsers] ([UserID],[UserName],[CostCentreID],[RoleID],[Position],[StaffNumber],[Password],[LastLogin],[IsLogin],[LastPasswordChange],[IsActive]) VALUES ('SYSADM','System Administrator','0000000000','SYSADM ','System Administrator','0000000000','4+Bpc++aikmYIBk1qta9smjz80k=','2010-05-30 00:00:00.000',0,'2010-05-29 00:00:00.000',1) --GO /****** Object: Table [dbo].[SysAuditTrails] Script Date: 22/07/2010 09:47:05 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SysAuditTrails]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[SysAuditTrails] ( [AuditTrailID] [bigint] IDENTITY (1, 1) NOT NULL , [EntryDateTime] [datetime] NOT NULL , [WorkstationID] [nchar] (20) NOT NULL , [NetworkIPAddress] [nchar] (20) NOT NULL , [WindowsUserID] [nchar] (20) NOT NULL , [SystemUserID] [nchar] (20) NOT NULL , [ScreenOrURL] [nchar] (100) NULL , [DataSource] [nvarchar] (50) NOT NULL , [DatabaseAction] [nchar] (10) NOT NULL , [ValuesBefore] [nvarchar] (1000) NULL , [ValuesAfter] [nvarchar] (1000) NULL , CONSTRAINT [PK_SysAuditTrails] PRIMARY KEY CLUSTERED ( [AuditTrailID] ) ON [PRIMARY] ) ON [PRIMARY] END GO SET IDENTITY_INSERT [dbo].[SysAuditTrails] ON SET IDENTITY_INSERT [dbo].[SysAuditTrails] OFF /****** Object: Table [dbo].[SysReports] Script Date: 22/07/2010 09:47:06 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SysReports]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[SysReports] ( [ReportID] [int] NOT NULL , [ReportType] [nchar] (20) NOT NULL , [ReportName] [nvarchar] (50) NOT NULL , [Show] [nchar] (10) NULL , CONSTRAINT [PK_SysReports] PRIMARY KEY CLUSTERED ( [ReportID] ) ON [PRIMARY] ) ON [PRIMARY] END GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (100,'REGISTER ','Asset By Cost Centres - Detail',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (101,'REGISTER ','Asset By Cost Centres - Summary',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (120,'REGISTER ','Asset By Types - Detail',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (121,'REGISTER ','Asset By Types - Summary',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (130,'REGISTER ','Asset By Groups',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (140,'REGISTER ','Asset By Acquisition Types - Detail',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (141,'REGISTER ','Asset By Acquisition Types - Summary',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (170,'REGISTER ','Assets Disposed Listing',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (200,'ADMINISTRATION ','Asset Current Movement',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (210,'ADMINISTRATION ','Asset Maintenance Report',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (220,'ADMINISTRATION ','Asset Inspection Report',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (230,'ADMINISTRATION ','Asset Depreciation - Detail',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (231,'ADMINISTRATION ','Asset Depreciation - Summary',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (300,'SETUP ','Organisation Tree Listing',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (310,'SETUP ','Asset Types and Groups Listing',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (320,'SETUP ','Supplier Listings',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (330,'SETUP ','Asset Users Listing',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (340,'SETUP ','User Define Values Listing',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (400,'SECURITY ','System Users',' ') --GO INSERT INTO [dbo].[SysReports] ([ReportID],[ReportType],[ReportName],[Show]) VALUES (410,'SECURITY ','System Roles and Functions',' ') --GO /****** Object: Table [dbo].[SysSearchCriteria] Script Date: 22/07/2010 09:47:06 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SysSearchCriteria]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[SysSearchCriteria] ( [Module] [nchar] (10) NOT NULL , [SearchKey] [nchar] (10) NOT NULL , [SearchCriteria] [nvarchar] (50) NOT NULL , CONSTRAINT [PK_SysSearchCriteria] PRIMARY KEY CLUSTERED ( [Module], [SearchKey] ) ON [PRIMARY] ) ON [PRIMARY] END GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','ASSETDESC ','Asset by Description') --GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','ASSETNBR ','Asset by Asset Number') --GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','CHEQUE ','Asset by Cheque Number') --GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','COSTCENTRE','Asset by Cost Centres') --GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','DISPOSED ','Disposed Assets') --GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','LOCATION ','Asset by Location') --GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','ORDER ','Asset by Order Number') --GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','SUPPLIER ','Asset by Supplier') --GO INSERT INTO [dbo].[SysSearchCriteria] ([Module],[SearchKey],[SearchCriteria]) VALUES ('ASSET ','USER ','Asset by User') --GO /****** Object: Table [dbo].[UdpParameters] Script Date: 22/07/2010 09:47:06 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UdpParameters]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[UdpParameters] ( [TypeCode] [nchar] (10) NOT NULL , [ValueCode] [nchar] (10) NOT NULL , [ValueName] [nvarchar] (50) NOT NULL , [IsSystemDefine] [bit] NULL , CONSTRAINT [PK_UdpParameters] PRIMARY KEY CLUSTERED ( [TypeCode], [ValueCode] ) ON [PRIMARY] ) ON [PRIMARY] END GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ABKCONTYPE','CUSTOMER ','Customer',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ABKCONTYPE','EMPLOYEE ','Employee',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ABKCONTYPE','SUPPLIER ','Supplier',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTAQUTYP ','DONATION ','Donation',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTAQUTYP ','PURCHASE ','Purchase through Supplier',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTCON ','0006 ','Asset recommeded for disposal',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTCON ','0007 ','Asset already disposed',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTDISMTD ','SOLD ','Asset Sold to customers',0) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTDISMTD ','STOLEN ','Asset stolen by thieves',0) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTDPRMTD ','REDUCING ','Reducing Balance',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTDPRMTD ','STRAIGHT ','Straight line ',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTSTATUS ','1 ','Active - Not Allocated',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTSTATUS ','2 ','Active - Allocated',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTSTATUS ','6 ','Disposed',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTTRNTYP ','DEPRE ','Asset Depreciation Transactions',1) --GO INSERT INTO [dbo].[UdpParameters] ([TypeCode],[ValueCode],[ValueName],[IsSystemDefine]) VALUES ('ASTTRNTYP ','REVAL ','Asset Revaluation Transactions',1) --GO /****** Object: Table [dbo].[UdpParameterTypes] Script Date: 22/07/2010 09:47:07 ******/ --if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UdpParameterTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN CREATE TABLE [dbo].[UdpParameterTypes] ( [TypeCode] [nchar] (10) NOT NULL , [TypeName] [nvarchar] (50) NOT NULL , [OnlySystemDefine] [bit] NULL , CONSTRAINT [PK_UdpParameterTypes] PRIMARY KEY CLUSTERED ( [TypeCode] ) ON [PRIMARY] ) ON [PRIMARY] END GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ABKCONTYPE','Contact Types',1) --GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ASTACCSRC ','Asset Account Source',0) --GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ASTAQUTYP ','Asset Aquisition Type',0) --GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ASTCON ','Asset Condition',0) --GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ASTDISMTD ','Asset Disposal Method',0) --GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ASTDPRMTD ','Asset Depreciation Method',1) --GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ASTGLACC ','Asset GL Account',0) --GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ASTSTATUS ','Asset Status',1) --GO INSERT INTO [dbo].[UdpParameterTypes] ([TypeCode],[TypeName],[OnlySystemDefine]) VALUES ('ASTTRNTYP ','Asset Transaction Types',1) --GO ALTER TABLE [dbo].[AppProduct] ADD CONSTRAINT [FK_AppProduct_OrgOrganisation] FOREIGN KEY ( [OrganisationName] ) REFERENCES [OrgOrganisation] ( [OrganisationName] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[AssetAudit] ADD CONSTRAINT [FK_AssetAudit_AssetMaster] FOREIGN KEY ( [AssetNumber] ) REFERENCES [AssetMaster] ( [AssetNumber] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[AssetMaintenance] ADD CONSTRAINT [FK_AssetRepair_AssetMaster] FOREIGN KEY ( [AssetNumber] ) REFERENCES [AssetMaster] ( [AssetNumber] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[AssetMaster] ADD CONSTRAINT [FK_AssetMaster_OrgCostCentres] FOREIGN KEY ( [CostCentreID] ) REFERENCES [OrgCostCentres] ( [CostCentreID] ) ON UPDATE CASCADE --GO ALTER TABLE [dbo].[AssetMovement] ADD CONSTRAINT [FK_AssetMovement_OrgLocations] FOREIGN KEY ( [LocationId] ) REFERENCES [OrgLocations] ( [LocationId] ) ON UPDATE CASCADE --GO ALTER TABLE [dbo].[AssetMovementHistory] ADD CONSTRAINT [FK_AssetMovementHistory_OrgLocations] FOREIGN KEY ( [LocationID] ) REFERENCES [OrgLocations] ( [LocationId] ) --GO ALTER TABLE [dbo].[AssetTempReport] ADD CONSTRAINT [FK_AssetTempReport_AssetMaster] FOREIGN KEY ( [AssetNumber] ) REFERENCES [AssetMaster] ( [AssetNumber] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[AssetTransactions] ADD CONSTRAINT [FK_AssetTransactions_AssetMaster] FOREIGN KEY ( [AssetNumber] ) REFERENCES [AssetMaster] ( [AssetNumber] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[AssetUsers] ADD CONSTRAINT [FK_AssetUsers_OrgCostCentres] FOREIGN KEY ( [CostCentreID] ) REFERENCES [OrgCostCentres] ( [CostCentreID] ) ON UPDATE CASCADE --GO ALTER TABLE [dbo].[DcgGroupMajors] ADD CONSTRAINT [FK_DcgGroupMajors_DcgGroupTypes] FOREIGN KEY ( [GroupTypeCode] ) REFERENCES [DcgGroupTypes] ( [GroupTypeCode] ) --GO ALTER TABLE [dbo].[DcgGroupMinors] ADD CONSTRAINT [FK_DcgGroupMinors_DcgGroupMajors] FOREIGN KEY ( [GroupTypeCode], [GroupMajorCode] ) REFERENCES [DcgGroupMajors] ( [GroupTypeCode], [GroupMajorCode] ) --GO ALTER TABLE [dbo].[OrgCostCentres] ADD CONSTRAINT [FK_OrgCostCentres_OrgZones] FOREIGN KEY ( [ZoneID] ) REFERENCES [OrgZones] ( [ZoneID] ) ON UPDATE CASCADE --GO ALTER TABLE [dbo].[OrgLocations] ADD CONSTRAINT [FK_OrgLocations_OrgCostCentres] FOREIGN KEY ( [CostCentreID] ) REFERENCES [OrgCostCentres] ( [CostCentreID] ) ON UPDATE CASCADE --GO ALTER TABLE [dbo].[OrgOrganisation] ADD CONSTRAINT [FK_OrgOrganisation_OrgOrganisation] FOREIGN KEY ( [OrganisationName] ) REFERENCES [OrgOrganisation] ( [OrganisationName] ) --GO ALTER TABLE [dbo].[OrgZones] ADD CONSTRAINT [FK_OrgZones_OrgZones] FOREIGN KEY ( [ZoneID] ) REFERENCES [OrgZones] ( [ZoneID] ) --GO ALTER TABLE [dbo].[SecRoleFunctions] ADD CONSTRAINT [FK_SecRoleFunctions_SecFunctions] FOREIGN KEY ( [FunctionID] ) REFERENCES [SecFunctions] ( [FunctionID] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[SecRoleFunctions] ADD CONSTRAINT [FK_SecRoleFunctions_SecRoles] FOREIGN KEY ( [RoleID] ) REFERENCES [SecRoles] ( [RoleID] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[SecRoleScreens] ADD CONSTRAINT [FK_SecRoleScreens_SecRoles] FOREIGN KEY ( [RoleID] ) REFERENCES [SecRoles] ( [RoleID] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[SecRoleScreens] ADD CONSTRAINT [FK_SecRoleScreens_SecScreens] FOREIGN KEY ( [ScreenID] ) REFERENCES [SecScreens] ( [ScreenID] ) ON DELETE CASCADE ON UPDATE CASCADE --GO ALTER TABLE [dbo].[SecUsers] ADD CONSTRAINT [FK_SecSystemUsers_SecRoles] FOREIGN KEY ( [RoleID] ) REFERENCES [SecRoles] ( [RoleID] ) ON UPDATE CASCADE --GO ALTER TABLE [dbo].[UdpParameters] ADD CONSTRAINT [FK_UdcParameters_UdcParameterTypes] FOREIGN KEY ( [TypeCode] ) REFERENCES [UdpParameterTypes] ( [TypeCode] ) --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON GO /****** Object: View dbo.qryAssetAudit Script Date: 22/07/2010 09:47:09 ******/ CREATE VIEW [dbo].[qryAssetAudit] AS SELECT dbo.AssetAudit.AssetNumber, dbo.AssetAudit.AuditId, dbo.AssetAudit.AuditDate, dbo.AssetAudit.Auditor, dbo.AssetAudit.Report FROM dbo.AssetAudit --GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON GO /****** Object: View dbo.qryAssetAvailableForUse Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW [dbo].[qryAssetAvailableForUse] AS SELECT TOP (100) PERCENT dbo.AssetMaster.AssetNumber, dbo.AssetMaster.Description, dbo.AssetMaster.CostCentreID, dbo.OrgCostCentres.CostCentreName, dbo.AssetMovement.LocationId, dbo.OrgLocations.LocationName, dbo.AssetMovement.StaffNumber, dbo.AssetMovement.AssignedStarts, dbo.AssetMovement.AssignedEnds, dbo.AssetMovement.Status, dbo.AssetMaster.StatusCode, dbo.AssetMovement.MovementReference FROM dbo.OrgCostCentres INNER JOIN dbo.AssetMaster ON dbo.OrgCostCentres.CostCentreID = dbo.AssetMaster.CostCentreID LEFT OUTER JOIN dbo.OrgLocations INNER JOIN dbo.AssetMovement ON dbo.OrgLocations.LocationId = dbo.AssetMovement.LocationId ON dbo.AssetMaster.AssetNumber = dbo.AssetMovement.AssetNumber WHERE (dbo.AssetMaster.StatusCode <> N'6') ORDER BY dbo.AssetMaster.StatusCode --GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON GO /****** Object: View dbo.qryAssetDepreciationTotal Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryAssetDepreciationTotal AS SELECT AssetNumber, SUM(TranAmount) AS TotalDepreciation FROM dbo.AssetTransactions WHERE (TranType = 'DEPRE') GROUP BY AssetNumber --GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON GO /****** Object: View dbo.qryAssetDepreciationTransactions Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryAssetDepreciationTransactions AS SELECT TranNumber, TranDate, TranType, AssetNumber, TranDetail, TranAmount FROM dbo.AssetTransactions WHERE (TranType = 'DEPRE') --GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON GO /****** Object: View dbo.qryAssetMaintenanceTotalCost Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryAssetMaintenanceTotalCost AS SELECT AssetNumber, SUM(MaintenanceCost) AS TotalMaintenanceCost FROM dbo.AssetMaintenance GROUP BY AssetNumber --GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON GO /****** Object: View dbo.qryAssetMovement Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryAssetMovement AS SELECT dbo.AssetMovement.AssetNumber, dbo.AssetMovement.LocationId, dbo.AssetMovement.StaffNumber, dbo.AssetMovement.AssignedStarts, dbo.AssetMovement.AssignedEnds, dbo.AssetMovement.Status, dbo.OrgLocations.LocationName, dbo.OrgCostCentres.CostCentreName, dbo.AssetUsers.UserName, dbo.AssetMovement.MovementReference FROM dbo.AssetMovement INNER JOIN dbo.OrgLocations ON dbo.AssetMovement.LocationId = dbo.OrgLocations.LocationId INNER JOIN dbo.OrgCostCentres ON dbo.OrgLocations.CostCentreID = dbo.OrgCostCentres.CostCentreID INNER JOIN dbo.AssetUsers ON dbo.AssetMovement.StaffNumber = dbo.AssetUsers.StaffNumber --GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON GO /****** Object: View dbo.qryAssetRevaluationTotal Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW [dbo].[qryAssetRevaluationTotal] AS SELECT AssetNumber, SUM(TranAmount) AS TotalRevaluation FROM dbo.AssetTransactions WHERE (TranType = 'REVAL') GROUP BY AssetNumber GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryOrgCostCentres Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryOrgCostCentres AS SELECT dbo.OrgCostCentres.*, dbo.OrgZones.ZoneName FROM dbo.OrgCostCentres INNER JOIN dbo.OrgZones ON dbo.OrgCostCentres.ZoneID = dbo.OrgZones.ZoneID GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryUdpAssetAccountSource Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryUdpAssetAccountSource AS SELECT ValueCode AS AccountSourceCode, ValueName AS AccountSourceName FROM dbo.UdpParameters WHERE (TypeCode = N'ASTACCSRC') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryUdpAssetAcquisitionTypes Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryUdpAssetAcquisitionTypes AS SELECT ValueCode AS AcquisitionTypeCode, ValueName AS AcquisitionTypeName FROM dbo.UdpParameters WHERE (TypeCode = N'ASTAQUTYP') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryUdpAssetCondition Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryUdpAssetCondition AS SELECT ValueCode AS ConditionCode, ValueName AS ConditionName FROM dbo.UdpParameters WHERE (TypeCode = N'ASTCON') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryUdpAssetDepreciationMethods Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryUdpAssetDepreciationMethods AS SELECT ValueCode AS DepreciationMethodCode, ValueName AS DepreciationMethodName FROM dbo.UdpParameters WHERE (TypeCode = N'ASTDPRMTD') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryUdpAssetGLAccounts Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryUdpAssetGLAccounts AS SELECT ValueCode AS GLAccountCode, ValueName AS GLAccountName FROM dbo.UdpParameters WHERE (TypeCode = N'ASTGLACC') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryUdpAssetStatus Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryUdpAssetStatus AS SELECT ValueCode AS StatusCode, ValueName AS StatusName FROM dbo.UdpParameters WHERE (TypeCode = N'ASTSTATUS') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryAssetMasterALL Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryAssetMasterALL AS SELECT TOP (100) PERCENT dbo.AssetMaster.AssetNumber, dbo.AssetMaster.Description, dbo.AssetMaster.CostCentreID, dbo.AssetMaster.AssetType, dbo.AssetMaster.GroupMajorCode, dbo.AssetMaster.GroupMinorCode, dbo.AssetMaster.AcquisitionType, dbo.AssetMaster.AcquiredDate, dbo.AssetMaster.AcquiredCost, dbo.AssetMaster.GLAccount, dbo.AssetMaster.SupplierID, dbo.AssetMaster.DisposalDate, dbo.AssetMaster.DisposalMethod, dbo.AssetMaster.DisposalReason, dbo.AssetMaster.DisposalReference, dbo.AssetMaster.DisposalOwner, dbo.AssetMaster.DisposalAmount, dbo.AssetMaster.DisposalPrice, dbo.AssetMaster.ConditionCode, dbo.AssetMaster.StatusCode, dbo.qryUdpAssetCondition.ConditionName, dbo.qryUdpAssetStatus.StatusName, dbo.qryAssetMovement.LocationId, dbo.qryAssetMovement.StaffNumber, dbo.qryAssetMovement.AssignedStarts, dbo.qryAssetMovement.AssignedEnds, dbo.qryAssetMovement.Status, dbo.qryAssetMovement.LocationName, dbo.qryAssetMovement.UserName, dbo.DcgGroupTypes.GroupTypeName, dbo.DcgGroupMajors.GroupMajorName, dbo.DcgGroupMinors.GroupMinorName, dbo.AbkContacts.ContactName, dbo.qryUdpAssetGLAccounts.GLAccountName, dbo.qryUdpAssetAcquisitionTypes.AcquisitionTypeName, dbo.AssetMaster.CurrentBookValue, dbo.AssetMaster.PONumber, dbo.AssetMaster.AccountSource, dbo.AssetMaster.ChqNumber, dbo.AssetMaster.POReference, dbo.AssetMaster.WrittenDownValue, dbo.AssetMaster.WrittenDownDate, dbo.AssetMaster.RevaluedAmount, dbo.AssetMaster.RevaluedDate, dbo.AssetMaster.LastDepreciation, dbo.AssetMaster.UsefulLifeSpan, dbo.AssetMaster.DepreciationRate, dbo.AssetMaster.DepreciationMethod, dbo.AssetMaster.DepreciationAmount, dbo.AssetMaster.DepreciationInterval, dbo.AssetMaster.Picture1, dbo.qryAssetMovement.MovementReference, dbo.qryUdpAssetDepreciationMethods.DepreciationMethodName, dbo.qryAssetDepreciationTotal.TotalDepreciation, dbo.qryUdpAssetAccountSource.AccountSourceCode, dbo.qryUdpAssetAccountSource.AccountSourceName, dbo.qryUdpAssetAcquisitionTypes.AcquisitionTypeCode, dbo.qryUdpAssetDepreciationMethods.DepreciationMethodCode, dbo.qryUdpAssetGLAccounts.GLAccountCode, dbo.qryOrgCostCentres.CostCentreName, dbo.qryOrgCostCentres.ZoneID, dbo.qryOrgCostCentres.ZoneName, dbo.AssetMaster.Notes, dbo.AssetMaster.CreatedDate, dbo.qryAssetMaintenanceTotalCost.TotalMaintenanceCost, dbo.qryAssetRevaluationTotal.TotalRevaluation, dbo.DcgGroupTypes.GroupTypeCode FROM dbo.AssetMaster LEFT OUTER JOIN dbo.qryAssetRevaluationTotal ON dbo.AssetMaster.AssetNumber = dbo.qryAssetRevaluationTotal.AssetNumber LEFT OUTER JOIN dbo.qryAssetMaintenanceTotalCost ON dbo.AssetMaster.AssetNumber = dbo.qryAssetMaintenanceTotalCost.AssetNumber LEFT OUTER JOIN dbo.qryOrgCostCentres ON dbo.AssetMaster.CostCentreID = dbo.qryOrgCostCentres.CostCentreID LEFT OUTER JOIN dbo.qryUdpAssetAccountSource ON dbo.AssetMaster.AccountSource = dbo.qryUdpAssetAccountSource.AccountSourceCode LEFT OUTER JOIN dbo.qryAssetDepreciationTotal ON dbo.AssetMaster.AssetNumber = dbo.qryAssetDepreciationTotal.AssetNumber LEFT OUTER JOIN dbo.qryUdpAssetDepreciationMethods ON dbo.AssetMaster.DepreciationMethod = dbo.qryUdpAssetDepreciationMethods.DepreciationMethodCode LEFT OUTER JOIN dbo.qryUdpAssetAcquisitionTypes ON dbo.AssetMaster.AcquisitionType = dbo.qryUdpAssetAcquisitionTypes.AcquisitionTypeCode LEFT OUTER JOIN dbo.qryUdpAssetGLAccounts ON dbo.AssetMaster.GLAccount = dbo.qryUdpAssetGLAccounts.GLAccountCode LEFT OUTER JOIN dbo.AbkContacts ON dbo.AssetMaster.SupplierID = dbo.AbkContacts.ContactId LEFT OUTER JOIN dbo.DcgGroupMinors ON dbo.AssetMaster.GroupMajorCode = dbo.DcgGroupMinors.GroupMajorCode AND dbo.AssetMaster.AssetType = dbo.DcgGroupMinors.GroupTypeCode AND dbo.AssetMaster.GroupMinorCode = dbo.DcgGroupMinors.GroupMinorCode LEFT OUTER JOIN dbo.DcgGroupMajors ON dbo.AssetMaster.GroupMajorCode = dbo.DcgGroupMajors.GroupMajorCode AND dbo.AssetMaster.AssetType = dbo.DcgGroupMajors.GroupTypeCode LEFT OUTER JOIN dbo.DcgGroupTypes ON dbo.AssetMaster.AssetType = dbo.DcgGroupTypes.GroupTypeCode LEFT OUTER JOIN dbo.qryAssetMovement ON dbo.AssetMaster.AssetNumber = dbo.qryAssetMovement.AssetNumber LEFT OUTER JOIN dbo.qryUdpAssetStatus ON dbo.AssetMaster.StatusCode = dbo.qryUdpAssetStatus.StatusCode LEFT OUTER JOIN dbo.qryUdpAssetCondition ON dbo.AssetMaster.ConditionCode = dbo.qryUdpAssetCondition.ConditionCode ORDER BY dbo.AssetMaster.CreatedDate DESC GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryAssetMasterActive Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryAssetMasterActive AS SELECT AssetNumber, Description, CostCentreID, AssetType, GroupMajorCode, GroupMinorCode, AcquisitionType, AcquiredDate, AcquiredCost, GLAccount, SupplierID, DisposalDate, DisposalMethod, DisposalReason, DisposalReference, DisposalOwner, DisposalAmount, DisposalPrice, ConditionCode, StatusCode, ConditionName, StatusName, LocationId, StaffNumber, AssignedStarts, AssignedEnds, Status, LocationName, CostCentreName, UserName, GroupTypeName, GroupMajorName, GroupMinorName, ContactName, GLAccountName, AcquisitionTypeName, CurrentBookValue, PONumber, AccountSource, ChqNumber, POReference, WrittenDownValue, WrittenDownDate, RevaluedAmount, RevaluedDate, LastDepreciation, UsefulLifeSpan, DepreciationRate, DepreciationMethod, DepreciationAmount, DepreciationInterval, Picture1, MovementReference, DepreciationMethodName, TotalDepreciation, AccountSourceCode, AccountSourceName, AcquisitionTypeCode, DepreciationMethodCode, GLAccountCode, ZoneID, ZoneName, Notes, CreatedDate, TotalMaintenanceCost, TotalRevaluation, GroupTypeCode FROM dbo.qryAssetMasterALL WHERE (StatusCode <> N'6') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryAssetMasterDisposal Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryAssetMasterDisposal AS SELECT AssetNumber, Description, CostCentreID, AssetType, GroupMajorCode, GroupMinorCode, AcquisitionType, AcquiredDate, AcquiredCost, GLAccount, SupplierID, DisposalDate, DisposalMethod, DisposalReason, DisposalReference, DisposalOwner, DisposalAmount, DisposalPrice, ConditionCode, StatusCode, ConditionName, StatusName, LocationId, StaffNumber, AssignedStarts, AssignedEnds, Status, LocationName, CostCentreName, UserName, GroupTypeName, GroupMajorName, GroupMinorName, ContactName, GLAccountName, AcquisitionTypeName, CurrentBookValue, PONumber, AccountSource, ChqNumber, POReference, WrittenDownValue, WrittenDownDate, RevaluedAmount, RevaluedDate, LastDepreciation, UsefulLifeSpan, DepreciationRate, DepreciationMethod, DepreciationAmount, DepreciationInterval, Picture1, MovementReference, TotalDepreciation, ZoneID, ZoneName, Notes FROM dbo.qryAssetMasterALL WHERE (ConditionCode = N'0006') OR (StatusCode = N'6') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryAssetMasterDisposed Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW [dbo].[qryAssetMasterDisposed] AS SELECT * FROM dbo.qryAssetMasterALL WHERE (StatusCode = N'6') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryAssetDepreciationList Script Date: 22/07/2010 09:47:10 ******/ CREATE VIEW dbo.qryAssetDepreciationList AS SELECT AssetNumber, Description, CostCentreID, AssetType, GroupMajorCode, GroupMinorCode, AcquisitionType, AcquiredDate, AcquiredCost, GLAccount, SupplierID, DisposalDate, DisposalMethod, DisposalReason, DisposalReference, DisposalOwner, DisposalAmount, DisposalPrice, ConditionCode, StatusCode, ConditionName, StatusName, LocationId, StaffNumber, AssignedStarts, AssignedEnds, Status, LocationName, CostCentreName, UserName, GroupTypeName, GroupMajorName, GroupMinorName, ContactName, GLAccountName, AcquisitionTypeName, CurrentBookValue, PONumber, AccountSource, ChqNumber, POReference, WrittenDownValue, WrittenDownDate, RevaluedAmount, RevaluedDate, LastDepreciation, UsefulLifeSpan, DepreciationRate, DepreciationMethod, DepreciationAmount, DepreciationInterval, Picture1, MovementReference, DepreciationMethodName, TotalDepreciation FROM dbo.qryAssetMasterALL WHERE (StatusCode <> N'6') AND (AssetType <> 'LAND') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryAssetMovementHistory Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryAssetMovementHistory AS SELECT dbo.AssetMovementHistory.LocationID, dbo.AssetMovementHistory.StaffNumber, dbo.AssetMovementHistory.AssetNumber, dbo.AssetMovementHistory.AssignedStarts, dbo.AssetMovementHistory.AssignedEnds, dbo.OrgLocations.LocationName, dbo.AssetUsers.UserName, dbo.AssetMovementHistory.HistoryID, dbo.AssetMovementHistory.MovementReference, dbo.OrgCostCentres.CostCentreName FROM dbo.OrgCostCentres RIGHT OUTER JOIN dbo.OrgLocations ON dbo.OrgCostCentres.CostCentreID = dbo.OrgLocations.CostCentreID RIGHT OUTER JOIN dbo.AssetMovementHistory INNER JOIN dbo.AssetUsers ON dbo.AssetMovementHistory.StaffNumber = dbo.AssetUsers.StaffNumber ON dbo.OrgLocations.LocationId = dbo.AssetMovementHistory.LocationID GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryAssetSearchCriteria Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW [dbo].[qryAssetSearchCriteria] AS SELECT TOP (100) PERCENT Module, SearchKey, SearchCriteria FROM dbo.SysSearchCriteria WHERE (Module = N'ASSET') ORDER BY SearchCriteria GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryAssetUsers Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryAssetUsers AS SELECT dbo.AssetUsers.StaffNumber, dbo.AssetUsers.UserName, dbo.AssetUsers.Designation, dbo.AssetUsers.Phone, dbo.AssetUsers.Email, dbo.AssetUsers.CostCentreID, dbo.AssetUsers.Photo, dbo.OrgCostCentres.CostCentreName, dbo.AssetUsers.Title, dbo.AssetUsers.StaffNumber + ' ' + dbo.AssetUsers.UserName AS AssetUser FROM dbo.AssetUsers LEFT OUTER JOIN dbo.OrgCostCentres ON dbo.AssetUsers.CostCentreID = dbo.OrgCostCentres.CostCentreID GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryOrgLocations Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW [dbo].[qryOrgLocations] AS SELECT dbo.OrgLocations.LocationId, dbo.OrgLocations.CostCentreID, dbo.OrgLocations.LocationName, dbo.OrgLocations.ApprovalPath, dbo.OrgLocations.Status, dbo.OrgLocations.ContactInfo, dbo.OrgCostCentres.ZoneID, dbo.OrgCostCentres.CostCentreName FROM dbo.OrgLocations INNER JOIN dbo.OrgCostCentres ON dbo.OrgLocations.CostCentreID = dbo.OrgCostCentres.CostCentreID GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryRptAbkContacts Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptContacts AS SELECT dbo.AbkContacts.* FROM dbo.AbkContacts GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryRptAssetAudit Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptAssetAudit AS SELECT dbo.qryAssetMasterActive.CostCentreID, dbo.qryAssetMasterActive.GroupTypeName, dbo.qryAssetMasterActive.AssetType, dbo.AssetAudit.AssetNumber, dbo.AssetAudit.AuditId, dbo.AssetAudit.AuditDate, dbo.AssetAudit.Auditor, dbo.AssetAudit.Report, dbo.qryAssetMasterActive.CostCentreName, dbo.qryAssetMasterActive.ConditionName, dbo.qryAssetMasterActive.StatusName, dbo.qryAssetMasterActive.LocationName, dbo.qryAssetMasterActive.UserName, dbo.qryAssetMasterActive.Description, dbo.qryAssetMasterActive.GroupTypeCode FROM dbo.AssetAudit INNER JOIN dbo.qryAssetMasterActive ON dbo.AssetAudit.AssetNumber = dbo.qryAssetMasterActive.AssetNumber GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryRptAssetDepreciation Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptAssetDepreciation AS SELECT CostCentreID, CostCentreName, AssetType, GroupTypeName, AssetNumber, Description, AcquiredDate, AcquiredCost, TotalDepreciation, CurrentBookValue FROM dbo.qryAssetMasterActive GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryRptAssetMaintenance Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptAssetMaintenance AS SELECT dbo.AssetMaintenance.AssetNumber, dbo.AssetMaintenance.MaintenanceId, dbo.AssetMaintenance.MaintenanceDescription, dbo.AssetMaintenance.MaintenanceDate, dbo.AssetMaintenance.MaintenanceProviderID, dbo.qryAssetMasterActive.CostCentreName, dbo.qryAssetMasterActive.CostCentreID, dbo.qryAssetMasterActive.Description, dbo.qryAssetMasterActive.AcquiredDate, dbo.qryAssetMasterActive.AcquiredCost, dbo.qryAssetMasterActive.CurrentBookValue, dbo.AbkContacts.ContactName, dbo.AssetMaintenance.RevalueAmount, dbo.AssetMaintenance.MaintenanceCost, dbo.qryAssetMasterActive.GroupTypeCode FROM dbo.AssetMaintenance LEFT OUTER JOIN dbo.qryAssetMasterActive ON dbo.AssetMaintenance.AssetNumber = dbo.qryAssetMasterActive.AssetNumber LEFT OUTER JOIN dbo.AbkContacts ON dbo.AssetMaintenance.MaintenanceProviderID = dbo.AbkContacts.ContactId GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryRptAssetMasterActive Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptAssetMasterActive AS SELECT AssetNumber, Description, CostCentreID, AssetType, GroupMajorCode, GroupMinorCode, AcquisitionType, AcquiredDate, AcquiredCost, GLAccount, SupplierID, DisposalDate, DisposalMethod, DisposalReason, DisposalReference, DisposalOwner, DisposalAmount, DisposalPrice, ConditionCode, StatusCode, ConditionName, StatusName, LocationId, StaffNumber, AssignedStarts, AssignedEnds, Status, LocationName, CostCentreName, UserName, GroupTypeName, GroupMajorName, GroupMinorName, ContactName, GLAccountName, AcquisitionTypeName, CurrentBookValue, PONumber, AccountSource, ChqNumber, POReference, WrittenDownValue, WrittenDownDate, RevaluedAmount, RevaluedDate, LastDepreciation, UsefulLifeSpan, DepreciationRate, DepreciationMethod, DepreciationAmount, DepreciationInterval, Picture1, MovementReference, DepreciationMethodName, TotalDepreciation, TotalMaintenanceCost, AccountSourceCode, AccountSourceName, AcquisitionTypeCode, DepreciationMethodCode, GLAccountCode FROM dbo.qryAssetMasterALL WHERE (StatusCode <> N'6') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryRptAssetMasterDisposed Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW [dbo].[qryRptAssetMasterDisposed] AS SELECT * FROM dbo.qryAssetMasterALL WHERE (StatusCode = N'6') GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryRptAssetMovement Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptAssetMovement AS SELECT dbo.AssetMovement.AssetNumber, dbo.AssetMovement.LocationId, dbo.AssetMovement.StaffNumber, dbo.AssetMovement.AssignedStarts, dbo.AssetMovement.AssignedEnds, dbo.AssetMovement.Status, dbo.OrgLocations.LocationName, dbo.OrgCostCentres.CostCentreName, dbo.AssetUsers.UserName, dbo.AssetMovement.MovementReference, dbo.AssetMaster.Description, dbo.DcgGroupTypes.GroupTypeName, dbo.DcgGroupTypes.GroupTypeCode, dbo.AssetMaster.CostCentreID FROM dbo.DcgGroupTypes RIGHT OUTER JOIN dbo.AssetMaster ON dbo.DcgGroupTypes.GroupTypeCode = dbo.AssetMaster.AssetType RIGHT OUTER JOIN dbo.AssetMovement INNER JOIN dbo.OrgLocations ON dbo.AssetMovement.LocationId = dbo.OrgLocations.LocationId INNER JOIN dbo.OrgCostCentres ON dbo.OrgLocations.CostCentreID = dbo.OrgCostCentres.CostCentreID INNER JOIN dbo.AssetUsers ON dbo.AssetMovement.StaffNumber = dbo.AssetUsers.StaffNumber ON dbo.AssetMaster.AssetNumber = dbo.AssetMovement.AssetNumber GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: View dbo.qryRptAssetMovementHistory Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptAssetMovementHistory AS SELECT dbo.AssetMovementHistory.LocationID, dbo.AssetMovementHistory.StaffNumber, dbo.AssetMovementHistory.AssetNumber, dbo.AssetMovementHistory.AssignedStarts, dbo.AssetMovementHistory.AssignedEnds, dbo.OrgCostCentres.CostCentreName, dbo.OrgLocations.LocationName, dbo.AssetUsers.UserName, dbo.AssetMovementHistory.HistoryID, dbo.AssetMovementHistory.MovementReference, dbo.OrgLocations.CostCentreID FROM dbo.AssetMovementHistory INNER JOIN dbo.AssetUsers ON dbo.AssetMovementHistory.StaffNumber = dbo.AssetUsers.StaffNumber INNER JOIN dbo.OrgCostCentres ON dbo.AssetUsers.CostCentreID = dbo.OrgCostCentres.CostCentreID INNER JOIN dbo.OrgLocations ON dbo.AssetMovementHistory.LocationID = dbo.OrgLocations.LocationId GO /****** Object: View dbo.qryRptAssetTempReport Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW [dbo].[qryRptAssetTempReport] AS SELECT dbo.AssetTempReport.* FROM dbo.AssetTempReport GO /****** Object: View dbo.qryRptAssetUsers Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptAssetUsers AS SELECT dbo.AssetUsers.*, dbo.OrgCostCentres.CostCentreName FROM dbo.AssetUsers INNER JOIN dbo.OrgCostCentres ON dbo.AssetUsers.CostCentreID = dbo.OrgCostCentres.CostCentreID GO /****** Object: View dbo.qryRptDcgGroupsTree Script Date: 22/07/2010 09:47:11 ******/ CREATE VIEW dbo.qryRptDcgGroupsTree AS SELECT dbo.DcgGroupTypes.GroupTypeCode, dbo.DcgGroupTypes.GroupTypeName, dbo.DcgGroupMajors.GroupMajorCode, dbo.DcgGroupMajors.GroupMajorName, dbo.DcgGroupMinors.GroupMinorCode, dbo.DcgGroupMinors.GroupMinorName FROM dbo.DcgGroupMajors INNER JOIN dbo.DcgGroupMinors ON dbo.DcgGroupMajors.GroupTypeCode = dbo.DcgGroupMinors.GroupTypeCode AND dbo.DcgGroupMajors.GroupMajorCode = dbo.DcgGroupMinors.GroupMajorCode FULL OUTER JOIN dbo.DcgGroupTypes ON dbo.DcgGroupMajors.GroupTypeCode = dbo.DcgGroupTypes.GroupTypeCode GO /****** Object: View dbo.qryRptOrgTree Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW dbo.qryRptOrgTree AS SELECT dbo.OrgZones.ZoneID, dbo.OrgZones.ZoneName, dbo.OrgCostCentres.CostCentreID, dbo.OrgCostCentres.CostCentreName, dbo.OrgLocations.LocationId, dbo.OrgLocations.LocationName FROM dbo.OrgCostCentres INNER JOIN dbo.OrgLocations ON dbo.OrgCostCentres.CostCentreID = dbo.OrgLocations.CostCentreID INNER JOIN dbo.OrgZones ON dbo.OrgCostCentres.ZoneID = dbo.OrgZones.ZoneID GO /****** Object: View dbo.qrySecRoleFunctions Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW [dbo].[qrySecRoleFunctions] AS SELECT dbo.SecRoleFunctions.RoleId, dbo.SecRoleFunctions.FunctionId, dbo.SecFunctions.[Function], dbo.SecRoles.[Role], dbo.SecRoles.IsActive FROM dbo.SecRoleFunctions LEFT OUTER JOIN dbo.SecRoles ON dbo.SecRoleFunctions.RoleId = dbo.SecRoles.RoleId LEFT OUTER JOIN dbo.SecFunctions ON dbo.SecRoleFunctions.FunctionId = dbo.SecFunctions.FunctionId GO /****** Object: View dbo.qryRptSecRoleFunctionsListing Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW dbo.qryRptSecRoleFunctionsListing AS SELECT RoleId, FunctionId, [Function], Role, IsActive FROM dbo.qrySecRoleFunctions GO /****** Object: View dbo.qrySecUsers Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW dbo.qrySecUsers AS SELECT dbo.SecUsers.UserID, dbo.SecUsers.UserName, dbo.SecUsers.CostCentreID, dbo.SecUsers.RoleID, dbo.SecUsers.Position, dbo.SecUsers.StaffNumber, dbo.SecUsers.Password, dbo.SecUsers.LastLogin, dbo.SecUsers.IsLogin, dbo.SecUsers.LastPasswordChange, dbo.SecUsers.IsActive, dbo.OrgCostCentres.CostCentreName, dbo.SecRoles.Role FROM dbo.SecUsers INNER JOIN dbo.SecRoles ON dbo.SecUsers.RoleID = dbo.SecRoles.RoleID LEFT OUTER JOIN dbo.OrgCostCentres ON dbo.SecUsers.CostCentreID = dbo.OrgCostCentres.CostCentreID GO /****** Object: View dbo.qryRptSecUsersListing Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW dbo.qryRptSecUsersListing AS SELECT UserID, UserName, CostCentreID, RoleID, Position, StaffNumber, Password, LastLogin, IsLogin, LastPasswordChange, IsActive, CostCentreName, Role FROM dbo.qrySecUsers GO /****** Object: View dbo.qryRptUdpParameters Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW dbo.qryRptUdpParameters AS SELECT dbo.UdpParameterTypes.TypeName, dbo.UdpParameterTypes.TypeCode, dbo.UdpParameters.ValueCode, dbo.UdpParameters.ValueName, dbo.UdpParameters.IsSystemDefine FROM dbo.UdpParameters FULL OUTER JOIN dbo.UdpParameterTypes ON dbo.UdpParameters.TypeCode = dbo.UdpParameterTypes.TypeCode GO /****** Object: View dbo.qrySecRoleScreens Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW dbo.qrySecRoleScreens AS SELECT dbo.SecRoleScreens.RoleID, dbo.SecRoleScreens.ScreenID, dbo.SecScreens.Screen, dbo.SecScreens.MenuGroup, dbo.SecRoles.Role FROM dbo.SecRoleScreens LEFT OUTER JOIN dbo.SecScreens ON dbo.SecRoleScreens.ScreenID = dbo.SecScreens.ScreenID LEFT OUTER JOIN dbo.SecRoles ON dbo.SecRoleScreens.RoleID = dbo.SecRoles.RoleID GO /****** Object: View dbo.qrySysReportsActive Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW dbo.qrySysReportsActive AS SELECT ReportID, ReportType, ReportName, Show FROM dbo.SysReports WHERE (Show <> 'N') GO /****** Object: View dbo.qryUdpAbkContactTypes Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW [dbo].[qryUdpAbkContactTypes] AS SELECT ValueCode AS ContactTypeCode, ValueName AS ContactTypeName FROM dbo.UdpParameters WHERE (TypeCode = N'ABKCONTYPE') GO /****** Object: View dbo.qryUdpAssetDisposalMethods Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW dbo.qryUdpAssetDisposalMethods AS SELECT ValueCode AS DisposalMethodCode, ValueName AS DisposalMethodName FROM dbo.UdpParameters WHERE (TypeCode = N'ASTDISMTD') GO /****** Object: View dbo.qryUdpParameterTypesOnlyUserDefine Script Date: 22/07/2010 09:47:12 ******/ CREATE VIEW [dbo].[qryUdpParameterTypesOnlyUserDefine] AS SELECT TypeCode, TypeName, OnlySystemDefine FROM dbo.UdpParameterTypes WHERE (OnlySystemDefine <> 1) GO /****** Object: Stored Procedure dbo.AbkContacts_Delete Script Date: 22/07/2010 09:47:12 ******/ ---------------------------------------------------------------------------- -- Delete a single record from AbkContacts ---------------------------------------------------------------------------- CREATE PROC uspAbkContacts_Delete @ContactID nchar(20) AS DELETE AbkContacts WHERE ContactID = @ContactID GO /****** Object: Stored Procedure dbo.AbkContacts_Insert Script Date: 22/07/2010 09:47:12 ******/ ---------------------------------------------------------------------------- -- Insert a single record into AbkContacts ---------------------------------------------------------------------------- CREATE PROC [dbo].[AbkContacts_Insert] @ContactID nchar(20), @ContactName nvarchar(100), @ContactType nchar(20), @PostalAddress nvarchar(100) = NULL, @PhysicalAddress nvarchar(100) = NULL, @Telephone nchar(20) = NULL, @Fax nchar(20) = NULL, @Email nvarchar(100) = NULL, @ContactPerson nvarchar(100) = NULL AS INSERT AbkContacts(ContactID, ContactName, ContactType, PostalAddress,PhysicalAddress, Telephone, Fax, Email, ContactPerson) VALUES (@ContactID, @ContactName, @ContactType, @PostalAddress, @PhysicalAddress, @Telephone, @Fax, @Email, @ContactPerson) GO /****** Object: Stored Procedure dbo.AbkContacts_Update Script Date: 22/07/2010 09:47:12 ******/ CREATE PROC [dbo].[AbkContacts_Update] @ContactID nchar(20), @ContactName nvarchar(100), @ContactType nchar(20), @PostalAddress nvarchar(100) = NULL, @PhysicalAddress nvarchar(100) = NULL, @Telephone nchar(20) = NULL, @Fax nchar(20) = NULL, @Email nvarchar(100) = NULL, @ContactPerson nvarchar(100) = NULL, @CurrentContactID nchar(20) AS UPDATE AbkContacts SET ContactID = @ContactID, ContactName = @ContactName, ContactType = @ContactType, PostalAddress = @PostalAddress, PhysicalAddress = @PhysicalAddress, Telephone = @Telephone, Fax = @Fax, Email = @Email, ContactPerson = @ContactPerson WHERE ContactID = @CurrentContactID UPDATE AssetMaster SET SupplierId=@ContactID WHERE SupplierId=@CurrentContactID GO /****** Object: Stored Procedure dbo.AppProduct_Insert Script Date: 22/07/2010 09:47:12 ******/ CREATE PROC [dbo].[AppProduct_Insert] @OrganisationName nvarchar(100), @LicenceType nchar(20) = NULL, @SerialNumber nvarchar(100) = NULL, @ActivationCode nvarchar(100) = NULL, @DatabaseVersion nchar(40) = NULL, @InstallationDate datetime = NULL AS INSERT AppProduct(OrganisationName, LicenceType, SerialNumber, ActivationCode, DatabaseVersion, InstallationDate) VALUES (@OrganisationName, @LicenceType, @SerialNumber, @ActivationCode, @DatabaseVersion, GetDate()) GO /****** Object: Stored Procedure dbo.AssetAudit_Delete Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC AssetAudit_Delete @AssetNumber nchar(40), @AuditId bigint AS DELETE AssetAudit WHERE AssetNumber = @AssetNumber AND AuditId = @AuditId GO /****** Object: Stored Procedure dbo.AssetAudit_Insert Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC AssetAudit_Insert @AssetNumber nchar(40), @AuditDate datetime = NULL, @Auditor nvarchar(60) = NULL, @Report nvarchar(800) = NULL AS INSERT AssetAudit(AssetNumber, AuditDate, Auditor, Report) VALUES (@AssetNumber, @AuditDate, @Auditor, @Report) RETURN SCOPE_IDENTITY() GO /****** Object: Stored Procedure dbo.AssetAudit_Update Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC AssetAudit_Update @AssetNumber nchar(40), @AuditId bigint, @AuditDate datetime = NULL, @Auditor nvarchar(60) = NULL, @Report nvarchar(800) = NULL AS UPDATE AssetAudit SET AuditDate = @AuditDate, Auditor = @Auditor, Report = @Report WHERE AssetNumber = @AssetNumber AND AuditId = @AuditId GO /****** Object: Stored Procedure dbo.AssetMaintenance_Delete Script Date: 22/07/2010 09:47:13 ******/ ---------------------------------------------------------------------------- -- Delete a single record from AssetMaintenance ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetMaintenance_Delete] @AssetNumber nchar(40), @MaintenanceId bigint AS DELETE AssetMaintenance WHERE AssetNumber = @AssetNumber AND MaintenanceId = @MaintenanceId GO /****** Object: Stored Procedure dbo.AssetMaintenance_Insert Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC AssetMaintenance_Insert @AssetNumber nchar(40), @MaintenanceDescription nvarchar(800), @MaintenanceDate datetime, @MaintenanceProviderID nchar(20) = NULL, @MaintenanceCost money = NULL, @RevalueAmount money = NULL AS INSERT AssetMaintenance(AssetNumber, MaintenanceDescription, MaintenanceDate, MaintenanceProviderID, MaintenanceCost, RevalueAmount) VALUES (@AssetNumber, @MaintenanceDescription, @MaintenanceDate, @MaintenanceProviderID, @MaintenanceCost, @RevalueAmount) RETURN SCOPE_IDENTITY() GO /****** Object: Stored Procedure dbo.AssetMaintenance_Update Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC AssetMaintenance_Update @AssetNumber nchar(40), @MaintenanceId bigint, @MaintenanceDescription nvarchar(800), @MaintenanceDate datetime, @MaintenanceProviderID nchar(20) = NULL, @MaintenanceCost money = NULL, @RevalueAmount money = NULL AS UPDATE AssetMaintenance SET MaintenanceDescription = @MaintenanceDescription, MaintenanceDate = @MaintenanceDate, MaintenanceProviderID = @MaintenanceProviderID, MaintenanceCost = @MaintenanceCost, RevalueAmount = @RevalueAmount WHERE AssetNumber = @AssetNumber AND MaintenanceId = @MaintenanceId GO /****** Object: Stored Procedure dbo.AssetMaster_DisposeAsset Script Date: 22/07/2010 09:47:13 ******/ ---------------------------------------------------------------------------- -- Dispose record in AssetMaster ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetMaster_DisposeAsset] @AssetNumber nchar(20), @DisposalDate datetime, @DisposalReference nchar(50), @DisposalMethod nchar(50)=null, @DisposalOwner nchar(50)=null, @DisposalPrice money =null, @StatusCode nchar(10), @ConditionCode nchar(10) AS UPDATE AssetMaster SET DisposalDate = @DisposalDate, DisposalReference = @DisposalReference, DisposalMethod = @DisposalMethod, DisposalOwner = @DisposalOwner, DisposalPrice = @DisposalPrice, StatusCode = @StatusCode, ConditionCode=@ConditionCode WHERE AssetNumber = @AssetNumber GO /****** Object: Stored Procedure dbo.AssetMaster_Insert Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC [dbo].[AssetMaster_Insert] @AssetNumber nvarchar(40), @Description nvarchar(100), @Notes nvarchar(100), @CostCentreID nchar(20), @AssetType nchar(20), @GroupMajorCode nchar(20), @GroupMinorCode nchar(20), @AcquisitionType nchar(20) = NULL, @AcquiredDate datetime = NULL, @AcquiredCost money = NULL, @CurrentBookValue money = NULL, @PONumber nchar(40) = NULL, @AccountSource nchar(20) = NULL, @GLAccount nchar(20) = NULL, @POReference nvarchar(100) = NULL, @ChqNumber nchar(40) = NULL, @SupplierID char(10) = NULL, @DisposalAmount money = NULL, @ConditionCode nvarchar(100) = NULL, @UsefulLifeSpan int = NULL, @DepreciationRate decimal(18, 4) = NULL, @DepreciationMethod nchar(20) = NULL, @DepreciationAmount money = NULL, @StatusCode nchar(20) = NULL, @Picture1 image = NULL AS INSERT AssetMaster(AssetNumber, Description, Notes,CostCentreID, AssetType, GroupMajorCode, GroupMinorCode, AcquisitionType, AcquiredDate, AcquiredCost, CurrentBookValue, PONumber, AccountSource, GLAccount, POReference, ChqNumber, SupplierID, DisposalAmount, ConditionCode, UsefulLifeSpan, DepreciationRate, DepreciationMethod, DepreciationAmount, StatusCode, Picture1,CreatedDate) VALUES (@AssetNumber, @Description, @Notes, @CostCentreID, @AssetType, @GroupMajorCode, @GroupMinorCode, @AcquisitionType, @AcquiredDate, @AcquiredCost, @CurrentBookValue, @PONumber, @AccountSource, @GLAccount, @POReference, @ChqNumber, @SupplierID, @DisposalAmount, @ConditionCode, @UsefulLifeSpan, @DepreciationRate, @DepreciationMethod, @DepreciationAmount, @StatusCode, @Picture1,GetDate()) GO /****** Object: Stored Procedure dbo.AssetMaster_ResetDepreciationInfo Script Date: 22/07/2010 09:47:13 ******/ ---------------------------------------------------------------------------- -- Update Depreciation Information on AssetMaster ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetMaster_ResetDepreciationInfo] @AssetNumber nchar(20), @CurrentBookValue money AS UPDATE AssetMaster SET CurrentBookValue = @CurrentBookValue, LastDepreciation =null WHERE AssetNumber = @AssetNumber GO /****** Object: Stored Procedure dbo.AssetMaster_Update Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC [dbo].[AssetMaster_Update] @AssetNumber nvarchar(40), @Description nvarchar(100), @Notes nvarchar(100), @CostCentreID nchar(20), @AssetType nchar(20), @GroupMajorCode nchar(20), @GroupMinorCode nchar(20), @AcquisitionType nchar(20) = NULL, @AcquiredDate datetime = NULL, @AcquiredCost money = NULL, @CurrentBookValue money = NULL, @PONumber nchar(40) = NULL, @AccountSource nchar(20) = NULL, @GLAccount nchar(20) = NULL, @POReference nvarchar(100) = NULL, @ChqNumber nchar(40) = NULL, @SupplierID char(10) = NULL, @UsefulLifeSpan int = NULL, @DepreciationRate decimal(18, 4) = NULL, @DepreciationMethod nchar(20) = NULL, @DepreciationAmount money = NULL, @DisposalAmount money = NULL, @DepreciationInterval nchar(20) = NULL, @Picture1 image = NULL, @CurrentAssetNumber nvarchar(40) AS UPDATE AssetMaster SET AssetNumber = @AssetNumber, Description = @Description, Notes = @Notes, CostCentreID = @CostCentreID, AssetType = @AssetType, GroupMajorCode = @GroupMajorCode, GroupMinorCode = @GroupMinorCode, AcquisitionType = @AcquisitionType, AcquiredDate = @AcquiredDate, AcquiredCost = @AcquiredCost, CurrentBookValue = @CurrentBookValue, PONumber = @PONumber, AccountSource = @AccountSource, GLAccount = @GLAccount, POReference = @POReference, ChqNumber = @ChqNumber, SupplierID = @SupplierID, UsefulLifeSpan = @UsefulLifeSpan, DepreciationRate = @DepreciationRate, DepreciationMethod = @DepreciationMethod, DisposalAmount=@DisposalAmount, DepreciationAmount = @DepreciationAmount, DepreciationInterval = @DepreciationInterval, Picture1 = @Picture1 WHERE AssetNumber = @CurrentAssetNumber UPDATE AssetMovement SET AssetNumber=@AssetNumber WHERE AssetNumber=@CurrentAssetNumber UPDATE AssetMovementHistory SET AssetNumber=@AssetNumber WHERE AssetNumber=@CurrentAssetNumber GO /****** Object: Stored Procedure dbo.AssetMaster_UpdateDepreciationInfo Script Date: 22/07/2010 09:47:13 ******/ ---------------------------------------------------------------------------- -- Update Depreciation Information on AssetMaster ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetMaster_UpdateDepreciationInfo] @AssetNumber nchar(20), @CurrentBookValue money AS UPDATE AssetMaster SET CurrentBookValue = @CurrentBookValue, LastDepreciation =GetDate() WHERE AssetNumber = @AssetNumber GO /****** Object: Stored Procedure dbo.AssetMovement_Delete Script Date: 22/07/2010 09:47:13 ******/ ---------------------------------------------------------------------------- -- Delete a single record from AssetMovement ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetMovement_Delete] @AssetNumber nchar(40) AS DELETE AssetMovement WHERE AssetNumber = @AssetNumber --------------------GO GO /****** Object: Stored Procedure dbo.AssetMovement_Insert Script Date: 22/07/2010 09:47:13 ******/ ---------------------------------------------------------------------------- -- Insert a single record into AssetMovement ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetMovement_Insert] @AssetNumber nchar(40), @LocationId nchar(20) = NULL, @StaffNumber nchar(20) = NULL, @AssignedStarts datetime = NULL, @AssignedEnds datetime = NULL, @MovementReference nvarchar(400) = NULL, @Status nchar(20) = NULL AS INSERT AssetMovement(AssetNumber, LocationId, StaffNumber, AssignedStarts, AssignedEnds, MovementReference, Status) VALUES (@AssetNumber, @LocationId, @StaffNumber, @AssignedStarts, @AssignedEnds, @MovementReference, @Status) GO /****** Object: Stored Procedure dbo.AssetMovement_Update Script Date: 22/07/2010 09:47:13 ******/ ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetMovement_Update] @AssetNumber nchar(40), @LocationId nchar(20) = NULL, @StaffNumber nchar(20) = NULL, @AssignedStarts datetime = NULL, @AssignedEnds datetime = NULL, @MovementReference nvarchar(400) = NULL, @Status nchar(20) = NULL AS UPDATE AssetMovement SET LocationId = @LocationId, StaffNumber = @StaffNumber, AssignedStarts = @AssignedStarts, AssignedEnds = @AssignedEnds, MovementReference = @MovementReference, Status = @Status WHERE AssetNumber = @AssetNumber GO /****** Object: Stored Procedure dbo.AssetMovementHistory_CheckDate Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC [dbo].[AssetMovementHistory_CheckDate] @DateToTest datetime, @AssetNumber nchar(20) AS SELECT * FROM AssetMovementHistory WHERE @DateToTest < AssignedEnds AND AssetNumber=@AssetNumber GO /****** Object: Stored Procedure dbo.AssetMovementHistory_Insert Script Date: 22/07/2010 09:47:13 ******/ CREATE PROC [dbo].[AssetMovementHistory_Insert] @LocationID nchar(20), @StaffNumber nchar(20), @AssetNumber nchar(40), @AssignedStarts datetime, @AssignedEnds datetime, @MovementReference nvarchar(400) = NULL AS INSERT AssetMovementHistory(LocationID, StaffNumber, AssetNumber, AssignedStarts, AssignedEnds, MovementReference) VALUES (@LocationID, @StaffNumber, @AssetNumber, @AssignedStarts, @AssignedEnds, @MovementReference) RETURN SCOPE_IDENTITY() GO /****** Object: Stored Procedure dbo.AssetTempReport_Delete Script Date: 22/07/2010 09:47:13 ******/ ---------------------------------------------------------------------------- -- Delete AssetTempReport ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetTempReport_Delete] AS DELETE AssetTempReport GO /****** Object: Stored Procedure dbo.AssetTempReport_Insert Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC [dbo].[AssetTempReport_Insert] @AssetNumber nchar(40), @Description nvarchar(100) = NULL, @CostCentreID nchar(20) = NULL, @AssetType nchar(20) = NULL, @GroupMajorCode nchar(20) = NULL, @GroupMinorCode nchar(20) = NULL, @TransactionAmount money = NULL, @Result nchar(20) = NULL, @Message nvarchar(100) = NULL AS INSERT AssetTempReport(TransDate, AssetNumber, Description, CostCentreID, AssetType, GroupMajorCode, GroupMinorCode, TransactionAmount, Result, Message) VALUES (GetDate(), @AssetNumber, @Description, @CostCentreID, @AssetType, @GroupMajorCode, @GroupMinorCode, @TransactionAmount, @Result, @Message) RETURN SCOPE_IDENTITY() GO /****** Object: Stored Procedure dbo.AssetTransactions_Insert Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC AssetTransactions_Insert @TranDate datetime, @TranType nchar(20), @AssetNumber nchar(40), @TranDetail nvarchar(100), @TranAmount money AS INSERT AssetTransactions(TranDate, TranType, AssetNumber, TranDetail, TranAmount) VALUES (@TranDate, @TranType, @AssetNumber, @TranDetail, @TranAmount) RETURN SCOPE_IDENTITY() GO /****** Object: Stored Procedure dbo.AssetTransactionsDepreciation_Delete Script Date: 22/07/2010 09:47:14 ******/ ---------------------------------------------------------------------------- -- Insert a single record into assettransactions ---------------------------------------------------------------------------- CREATE PROC [dbo].[AssetTransactionsDepreciation_Delete] @TranType nchar(20), @AssetNumber nchar(20) AS DELETE AssetTransactions WHERE TranType=@TranType and AssetNumber=@AssetNumber GO /****** Object: Stored Procedure dbo.AssetUsers_Delete Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC AssetUsers_Delete @StaffNumber nchar(20) AS DELETE AssetUsers WHERE StaffNumber = @StaffNumber GO /****** Object: Stored Procedure dbo.AssetUsers_Insert Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC [dbo].[AssetUsers_Insert] @StaffNumber nchar(20), @Title nchar(20) = NULL, @UserName nchar(60), @Designation nvarchar(100) = NULL, @Phone nchar(20) = NULL, @Email nvarchar(100) = NULL, @CostCentreID nchar(20), @Photo image = NULL AS INSERT AssetUsers(StaffNumber, Title, UserName, Designation, Phone, Email, CostCentreID, Photo) VALUES (@StaffNumber, @Title, @UserName, @Designation, @Phone, @Email, @CostCentreID, @Photo) GO /****** Object: Stored Procedure dbo.AssetUsers_Update Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC [dbo].[AssetUsers_Update] @CurrentStaffNumber nchar(20), @StaffNumber nchar(20), @Title nchar(20) = NULL, @UserName nchar(60), @Designation nvarchar(100) = NULL, @Phone nchar(20) = NULL, @Email nvarchar(100) = NULL, @CostCentreID nchar(20), @Photo image = NULL AS UPDATE AssetUsers SET StaffNumber = @StaffNumber, Title = @Title, UserName = @UserName, Designation = @Designation, Phone = @Phone, Email = @Email, CostCentreID = @CostCentreID, Photo = @Photo WHERE StaffNumber = @CurrentStaffNumber UPDATE AssetMovement SET StaffNumber=@StaffNumber WHERE StaffNumber=@CurrentStaffNumber UPDATE AssetMovementHistory SET StaffNumber=@StaffNumber WHERE StaffNumber=@CurrentStaffNumber GO /****** Object: Stored Procedure dbo.OrgOrganisation_Insert Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC OrgOrganisation_Insert @OrganisationName nvarchar(100), @AddressLine1 nvarchar(100) = NULL, @AddressLine2 nvarchar(100) = NULL, @AddressLine3 nvarchar(100) = NULL, @AddressLine4 nvarchar(100) = NULL, @Telephone nchar(20) = NULL, @Fax nchar(20) = NULL, @Email nvarchar(100) = NULL, @Website nchar(20) = NULL AS INSERT OrgOrganisation(OrganisationName, AddressLine1, AddressLine2, AddressLine3, AddressLine4, Telephone, Fax, Email, Website) VALUES (@OrganisationName, @AddressLine1, @AddressLine2, @AddressLine3, @AddressLine4, @Telephone, @Fax, @Email, @Website) GO /****** Object: Stored Procedure dbo.OrgOrganisation_Update Script Date: 22/07/2010 09:47:14 ******/ ---------------------------------------------------------------------------- -- Update a single record in OrgOrganisation ---------------------------------------------------------------------------- CREATE PROC OrgOrganisation_Update @OrganisationName nvarchar(100), @AddressLine1 nvarchar(100) = NULL, @AddressLine2 nvarchar(100) = NULL, @AddressLine3 nvarchar(100) = NULL, @AddressLine4 nvarchar(100) = NULL, @Telephone nchar(20) = NULL, @Fax nchar(20) = NULL, @Email nvarchar(100) = NULL, @Website nchar(20) = NULL AS UPDATE OrgOrganisation SET AddressLine1 = @AddressLine1, AddressLine2 = @AddressLine2, AddressLine3 = @AddressLine3, AddressLine4 = @AddressLine4, Telephone = @Telephone, Fax = @Fax, Email = @Email, Website = @Website WHERE OrganisationName = @OrganisationName GO /****** Object: Stored Procedure dbo.sp_alterdiagram Script Date: 22/07/2010 09:47:14 ******/ CREATE PROCEDURE dbo.sp_alterdiagram ( @diagramname sysname, @owner_id int = null, @version int, @definition varbinary(max) ) WITH EXECUTE AS 'dbo' AS BEGIN set nocount on declare @theId int declare @retval int declare @IsDbo int declare @UIDFound int declare @DiagId int declare @ShouldChangeUID int if(@diagramname is null) begin RAISERROR ('Invalid ARG', 16, 1) return -1 end execute as caller; select @theId = DATABASE_PRINCIPAL_ID(); select @IsDbo = IS_MEMBER(N'db_owner'); if(@owner_id is null) select @owner_id = @theId; revert; select @ShouldChangeUID = 0 select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname if(@DiagId IS NULL or (@IsDbo = 0 and @theId <> @UIDFound)) begin RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1); return -3 end if(@IsDbo <> 0) begin if(@UIDFound is null or USER_NAME(@UIDFound) is null) -- invalid principal_id begin select @ShouldChangeUID = 1 ; end end -- update dds data update dbo.sysdiagrams set definition = @definition where diagram_id = @DiagId ; -- change owner if(@ShouldChangeUID = 1) update dbo.sysdiagrams set principal_id = @theId where diagram_id = @DiagId ; -- update dds version if(@version is not null) update dbo.sysdiagrams set version = @version where diagram_id = @DiagId ; return 0 END GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: Stored Procedure dbo.sp_creatediagram Script Date: 22/07/2010 09:47:14 ******/ CREATE PROCEDURE dbo.sp_creatediagram ( @diagramname sysname, @owner_id int = null, @version int, @definition varbinary(max) ) WITH EXECUTE AS 'dbo' AS BEGIN set nocount on declare @theId int declare @retval int declare @IsDbo int declare @userName sysname if(@version is null or @diagramname is null) begin RAISERROR (N'E_INVALIDARG', 16, 1); return -1 end execute as caller; select @theId = DATABASE_PRINCIPAL_ID(); select @IsDbo = IS_MEMBER(N'db_owner'); revert; if @owner_id is null begin select @owner_id = @theId; end else begin if @theId <> @owner_id begin if @IsDbo = 0 begin RAISERROR (N'E_INVALIDARG', 16, 1); return -1 end select @theId = @owner_id end end -- next 2 line only for test, will be removed after define name unique if EXISTS(select diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @diagramname) begin RAISERROR ('The name is already used.', 16, 1); return -2 end insert into dbo.sysdiagrams(name, principal_id , version, definition) VALUES(@diagramname, @theId, @version, @definition) ; select @retval = @@IDENTITY return @retval END GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: Stored Procedure dbo.sp_dropdiagram Script Date: 22/07/2010 09:47:14 ******/ CREATE PROCEDURE dbo.sp_dropdiagram ( @diagramname sysname, @owner_id int = null ) WITH EXECUTE AS 'dbo' AS BEGIN set nocount on declare @theId int declare @IsDbo int declare @UIDFound int declare @DiagId int if(@diagramname is null) begin RAISERROR ('Invalid value', 16, 1); return -1 end EXECUTE AS CALLER; select @theId = DATABASE_PRINCIPAL_ID(); select @IsDbo = IS_MEMBER(N'db_owner'); if(@owner_id is null) select @owner_id = @theId; REVERT; select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId)) begin RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1) return -3 end delete from dbo.sysdiagrams where diagram_id = @DiagId; return 0; END GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: Stored Procedure dbo.sp_helpdiagramdefinition Script Date: 22/07/2010 09:47:14 ******/ CREATE PROCEDURE dbo.sp_helpdiagramdefinition ( @diagramname sysname, @owner_id int = null ) WITH EXECUTE AS N'dbo' AS BEGIN set nocount on declare @theId int declare @IsDbo int declare @DiagId int declare @UIDFound int if(@diagramname is null) begin RAISERROR (N'E_INVALIDARG', 16, 1); return -1 end execute as caller; select @theId = DATABASE_PRINCIPAL_ID(); select @IsDbo = IS_MEMBER(N'db_owner'); if(@owner_id is null) select @owner_id = @theId; revert; select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname; if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId )) begin RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1); return -3 end select version, definition FROM dbo.sysdiagrams where diagram_id = @DiagId ; return 0 END GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: Stored Procedure dbo.sp_helpdiagrams Script Date: 22/07/2010 09:47:14 ******/ CREATE PROCEDURE dbo.sp_helpdiagrams ( @diagramname sysname = NULL, @owner_id int = NULL ) WITH EXECUTE AS N'dbo' AS BEGIN DECLARE @user sysname DECLARE @dboLogin bit EXECUTE AS CALLER; SET @user = USER_NAME(); SET @dboLogin = CONVERT(bit,IS_MEMBER('db_owner')); REVERT; SELECT [Database] = DB_NAME(), [Name] = name, [ID] = diagram_id, [Owner] = USER_NAME(principal_id), [OwnerID] = principal_id FROM sysdiagrams WHERE (@dboLogin = 1 OR USER_NAME(principal_id) = @user) AND (@diagramname IS NULL OR name = @diagramname) AND (@owner_id IS NULL OR principal_id = @owner_id) ORDER BY 4, 5, 1 END GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON --GO /****** Object: Stored Procedure dbo.sp_renamediagram Script Date: 22/07/2010 09:47:14 ******/ CREATE PROCEDURE dbo.sp_renamediagram ( @diagramname sysname, @owner_id int = null, @new_diagramname sysname ) WITH EXECUTE AS 'dbo' AS BEGIN set nocount on declare @theId int declare @IsDbo int declare @UIDFound int declare @DiagId int declare @DiagIdTarg int declare @u_name sysname if((@diagramname is null) or (@new_diagramname is null)) begin RAISERROR ('Invalid value', 16, 1); return -1 end EXECUTE AS CALLER; select @theId = DATABASE_PRINCIPAL_ID(); select @IsDbo = IS_MEMBER(N'db_owner'); if(@owner_id is null) select @owner_id = @theId; REVERT; select @u_name = USER_NAME(@owner_id) select @DiagId = diagram_id, @UIDFound = principal_id from dbo.sysdiagrams where principal_id = @owner_id and name = @diagramname if(@DiagId IS NULL or (@IsDbo = 0 and @UIDFound <> @theId)) begin RAISERROR ('Diagram does not exist or you do not have permission.', 16, 1) return -3 end if((@u_name is not null) and (@new_diagramname = @diagramname)) -- nothing will change return 0; if(@u_name is null) select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @theId and name = @new_diagramname else select @DiagIdTarg = diagram_id from dbo.sysdiagrams where principal_id = @owner_id and name = @new_diagramname if((@DiagIdTarg is not null) and @DiagId <> @DiagIdTarg) begin RAISERROR ('The name is already used.', 16, 1); return -2 end if(@u_name is null) update dbo.sysdiagrams set [name] = @new_diagramname, principal_id = @theId where diagram_id = @DiagId else update dbo.sysdiagrams set [name] = @new_diagramname where diagram_id = @DiagId return 0 END GO /****** Object: Stored Procedure dbo.sp_upgraddiagrams Script Date: 22/07/2010 09:47:14 ******/ CREATE PROCEDURE dbo.sp_upgraddiagrams AS BEGIN IF OBJECT_ID(N'dbo.sysdiagrams') IS NOT NULL return 0; CREATE TABLE dbo.sysdiagrams ( name sysname NOT NULL, principal_id int NOT NULL, -- we may change it to varbinary(85) diagram_id int PRIMARY KEY IDENTITY, version int, definition varbinary(max) CONSTRAINT UK_principal_name UNIQUE ( principal_id, name ) ); /* Add this --if we need to have some form of extended properties for diagrams */ /* --IF OBJECT_ID(N'dbo.sysdiagram_properties') IS NULL --BEGIN CREATE TABLE dbo.sysdiagram_properties ( diagram_id int, name sysname, value varbinary(max) NOT NULL ) --END */ IF OBJECT_ID(N'dbo.dtproperties') IS NOT NULL begin insert into dbo.sysdiagrams ( [name], [principal_id], [version], [definition] ) select convert(sysname, dgnm.[uvalue]), DATABASE_PRINCIPAL_ID(N'dbo'), -- will change to the sid of sa 0, -- zero for old format, dgdef.[version], dgdef.[lvalue] from dbo.[dtproperties] dgnm inner join dbo.[dtproperties] dggd on dggd.[property] = 'DtgSchemaGUID' and dggd.[objectid] = dgnm.[objectid] inner join dbo.[dtproperties] dgdef on dgdef.[property] = 'DtgSchemaDATA' and dgdef.[objectid] = dgnm.[objectid] where dgnm.[property] = 'DtgSchemaNAME' and dggd.[uvalue] like N'_EA3E6268-D998-11CE-9454-00AA00A3F36E_' return 2; end return 1; END GO /****** Object: Stored Procedure dbo.SysMakeDeleteRecordProc Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC [dbo].[pr__SYS_MakeDeleteRecordProc] @sTableName varchar(128), @bExecute bit = 0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) = 0 BEGIN RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1) RETURN END DECLARE @sProcText varchar(8000), @sKeyFields varchar(2000), @sWhereClause varchar(2000), @sColumnName varchar(128), @nColumnID smallint, @bPrimaryKeyColumn bit, @nAlternateType int, @nColumnLength int, @nColumnPrecision int, @nColumnScale int, @IsNullable bit, @IsIdentity int, @sTypeName varchar(128), @sDefaultValue varchar(4000), @sCRLF char(2), @sTAB char(1) SET @sTAB = char(9) SET @sCRLF = char(13) + char(10) SET @sProcText = '' SET @sKeyFields = '' SET @sWhereClause = '' SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''usp' + @sTableName + '_Delete'')' + @sCRLF SET @sProcText = @sProcText + @sTAB + 'DROP PROC usp' + @sTableName + '_Delete' + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + '--------------------GO' + @sCRLF SET @sProcText = @sProcText + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) SET @sProcText = '' SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF SET @sProcText = @sProcText + '-- Delete a single record from ' + @sTableName + @sCRLF SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF SET @sProcText = @sProcText + 'CREATE PROC usp' + @sTableName + '_Delete' + @sCRLF DECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo(@sTableName) ORDER BY 2 OPEN crKeyFields FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS = 0) BEGIN IF (@bPrimaryKeyColumn = 1) BEGIN IF (@sKeyFields <> '') SET @sKeyFields = @sKeyFields + ',' + @sCRLF SET @sKeyFields = @sKeyFields + @sTAB + '@' + @sColumnName + ' ' + @sTypeName IF (@nAlternateType = 2) --decimal, numeric SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', ' + CAST(@nColumnScale AS varchar(3)) + ')' ELSE IF (@nAlternateType = 1) --character and binary SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')' IF (@sWhereClause = '') SET @sWhereClause = @sWhereClause + 'WHERE ' ELSE SET @sWhereClause = @sWhereClause + ' AND ' SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ' = @' + @sColumnName + @sCRLF END FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET @sProcText = @sProcText + @sKeyFields + @sCRLF SET @sProcText = @sProcText + 'AS' + @sCRLF SET @sProcText = @sProcText + @sCRLF SET @sProcText = @sProcText + 'DELETE ' + @sTableName + @sCRLF SET @sProcText = @sProcText + @sWhereClause SET @sProcText = @sProcText + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + '--------------------GO' + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) --GO --SET QUOTED_IDENTIFIER OFF --GO --SET ANSI_NULLS ON --GO --SET QUOTED_IDENTIFIER ON --GO --SET ANSI_NULLS ON GO /****** Object: Stored Procedure dbo.SysMakeInsertRecordProc Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC [dbo].[pr__SYS_MakeInsertRecordProc] @sTableName varchar(128), @bExecute bit = 0 AS --IF dbo.fnTableHasPrimaryKey(@sTableName) = 0 --BEGIN RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1) RETURN --END DECLARE @sProcText varchar(8000), @sKeyFields varchar(2000), @sAllFields varchar(2000), @sAllParams varchar(2000), @sWhereClause varchar(2000), @sColumnName varchar(128), @nColumnID smallint, @bPrimaryKeyColumn bit, @nAlternateType int, @nColumnLength int, @nColumnPrecision int, @nColumnScale int, @IsNullable bit, @IsIdentity int, @HasIdentity int, @sTypeName varchar(128), @sDefaultValue varchar(4000), @sCRLF char(2), @sTAB char(1) --SET @HasIdentity = 0 --SET @sTAB = char(9) --SET @sCRLF = char(13) + char(10) --SET @sProcText = '' --SET @sKeyFields = '' --SET @sAllFields = '' --SET @sWhereClause = '' --SET @sAllParams = '' --SET @sProcText = @sProcText + '--IF EXISTS(SELECT * FROM sysobjects WHERE name = ''usp' + @sTableName + '_Insert'')' + @sCRLF --SET @sProcText = @sProcText + @sTAB + 'DROP PROC usp' + @sTableName + '_Insert' + @sCRLF --IF @bExecute = 0 --SET @sProcText = @sProcText + '--------------------GO' + @sCRLF --SET @sProcText = @sProcText + @sCRLF PRINT @sProcText --IF @bExecute = 1 EXEC (@sProcText) --SET @sProcText = '' --SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF --SET @sProcText = @sProcText + '-- Insert a single record into ' + @sTableName + @sCRLF --SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF --SET @sProcText = @sProcText + 'CREATE PROC usp' + @sTableName + '_Insert' + @sCRLF DECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo(@sTableName) ORDER BY 2 OPEN crKeyFields FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS = 0) BEGIN IF (@IsIdentity = 0) BEGIN IF (@sKeyFields <> '') SET @sKeyFields = @sKeyFields + ',' + @sCRLF SET @sKeyFields = @sKeyFields + @sTAB + '@' + @sColumnName + ' ' + @sTypeName IF (@sAllFields <> '') BEGIN SET @sAllParams = @sAllParams + ', ' SET @sAllFields = @sAllFields + ', ' END IF (@sTypeName = 'timestamp') SET @sAllParams = @sAllParams + 'NULL' ELSE IF (@sDefaultValue IS NOT NULL) SET @sAllParams = @sAllParams + 'COALESCE(@' + @sColumnName + ', ' + @sDefaultValue + ')' ELSE SET @sAllParams = @sAllParams + '@' + @sColumnName SET @sAllFields = @sAllFields + @sColumnName END ELSE BEGIN SET @HasIdentity = 1 END IF (@nAlternateType = 2) --decimal, numeric SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', ' + CAST(@nColumnScale AS varchar(3)) + ')' ELSE IF (@nAlternateType = 1) --character and binary SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')' IF (@IsIdentity = 0) BEGIN IF (@sDefaultValue IS NOT NULL) OR (@IsNullable = 1) OR (@sTypeName = 'timestamp') SET @sKeyFields = @sKeyFields + ' = NULL' END FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET @sProcText = @sProcText + @sKeyFields + @sCRLF SET @sProcText = @sProcText + 'AS' + @sCRLF SET @sProcText = @sProcText + @sCRLF SET @sProcText = @sProcText + 'INSERT ' + @sTableName + '(' + @sAllFields + ')' + @sCRLF SET @sProcText = @sProcText + 'VALUES (' + @sAllParams + ')' + @sCRLF SET @sProcText = @sProcText + @sCRLF IF (@HasIdentity = 1) BEGIN SET @sProcText = @sProcText + 'RETURN SCOPE_IDENTITY()' + @sCRLF SET @sProcText = @sProcText + @sCRLF END IF @bExecute = 0 SET @sProcText = @sProcText + '--------------------GO' + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) GO /****** Object: Stored Procedure dbo.SysMakeUpdateRecordProc Script Date: 22/07/2010 09:47:14 ******/ CREATE PROC [dbo].[pr__SYS_MakeUpdateRecordProc] @sTableName varchar(128), @bExecute bit = 0 AS IF dbo.fnTableHasPrimaryKey(@sTableName) = 0 BEGIN RAISERROR ('Procedure cannot be created on a table with no primary key.', 10, 1) RETURN END DECLARE @sProcText varchar(8000), @sKeyFields varchar(2000), @sSetClause varchar(2000), @sWhereClause varchar(2000), @sColumnName varchar(128), @nColumnID smallint, @bPrimaryKeyColumn bit, @nAlternateType int, @nColumnLength int, @nColumnPrecision int, @nColumnScale int, @IsNullable bit, @IsIdentity int, @sTypeName varchar(128), @sDefaultValue varchar(4000), @sCRLF char(2), @sTAB char(1) SET @sTAB = char(9) SET @sCRLF = char(13) + char(10) SET @sProcText = '' SET @sKeyFields = '' SET @sSetClause = '' SET @sWhereClause = '' SET @sProcText = @sProcText + 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''usp' + @sTableName + '_Update'')' + @sCRLF SET @sProcText = @sProcText + @sTAB + 'DROP PROC usp' + @sTableName + '_Update' + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + '--------------------GO' + @sCRLF SET @sProcText = @sProcText + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) SET @sProcText = '' SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF SET @sProcText = @sProcText + '-- Update a single record in ' + @sTableName + @sCRLF SET @sProcText = @sProcText + '----------------------------------------------------------------------------' + @sCRLF SET @sProcText = @sProcText + 'CREATE PROC usp' + @sTableName + '_Update' + @sCRLF DECLARE crKeyFields cursor for SELECT * FROM dbo.fnTableColumnInfo(@sTableName) ORDER BY 2 OPEN crKeyFields FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue WHILE (@@FETCH_STATUS = 0) BEGIN IF (@sKeyFields <> '') SET @sKeyFields = @sKeyFields + ',' + @sCRLF SET @sKeyFields = @sKeyFields + @sTAB + '@' + @sColumnName + ' ' + @sTypeName IF (@nAlternateType = 2) --decimal, numeric SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnPrecision AS varchar(3)) + ', ' + CAST(@nColumnScale AS varchar(3)) + ')' ELSE IF (@nAlternateType = 1) --character and binary SET @sKeyFields = @sKeyFields + '(' + CAST(@nColumnLength AS varchar(4)) + ')' IF (@bPrimaryKeyColumn = 1) BEGIN IF (@sWhereClause = '') SET @sWhereClause = @sWhereClause + 'WHERE ' ELSE SET @sWhereClause = @sWhereClause + ' AND ' SET @sWhereClause = @sWhereClause + @sTAB + @sColumnName + ' = @' + @sColumnName + @sCRLF END ELSE IF (@IsIdentity = 0) BEGIN IF (@sSetClause = '') SET @sSetClause = @sSetClause + 'SET' ELSE SET @sSetClause = @sSetClause + ',' + @sCRLF SET @sSetClause = @sSetClause + @sTAB + @sColumnName + ' = ' IF (@sTypeName = 'timestamp') SET @sSetClause = @sSetClause + 'NULL' ELSE IF (@sDefaultValue IS NOT NULL) SET @sSetClause = @sSetClause + 'COALESCE(@' + @sColumnName + ', ' + @sDefaultValue + ')' ELSE SET @sSetClause = @sSetClause + '@' + @sColumnName END IF (@IsIdentity = 0) BEGIN IF (@IsNullable = 1) OR (@sTypeName = 'timestamp') SET @sKeyFields = @sKeyFields + ' = NULL' END FETCH NEXT FROM crKeyFields INTO @sColumnName, @nColumnID, @bPrimaryKeyColumn, @nAlternateType, @nColumnLength, @nColumnPrecision, @nColumnScale, @IsNullable, @IsIdentity, @sTypeName, @sDefaultValue END CLOSE crKeyFields DEALLOCATE crKeyFields SET @sSetClause = @sSetClause + @sCRLF SET @sProcText = @sProcText + @sKeyFields + @sCRLF SET @sProcText = @sProcText + 'AS' + @sCRLF SET @sProcText = @sProcText + @sCRLF SET @sProcText = @sProcText + 'UPDATE ' + @sTableName + @sCRLF SET @sProcText = @sProcText + @sSetClause SET @sProcText = @sProcText + @sWhereClause SET @sProcText = @sProcText + @sCRLF IF @bExecute = 0 SET @sProcText = @sProcText + '--------------------GO' + @sCRLF PRINT @sProcText IF @bExecute = 1 EXEC (@sProcText) GO /****** Object: User Defined Function dbo.fn_diagramobjects Script Date: 22/07/2010 09:47:24 ******/ CREATE FUNCTION dbo.fn_diagramobjects() RETURNS int WITH EXECUTE AS N'dbo' AS BEGIN declare @id_upgraddiagrams int declare @id_sysdiagrams int declare @id_helpdiagrams int declare @id_helpdiagramdefinition int declare @id_creatediagram int declare @id_renamediagram int declare @id_alterdiagram int declare @id_dropdiagram int declare @InstalledObjects int select @InstalledObjects = 0 select @id_upgraddiagrams = object_id(N'dbo.sp_upgraddiagrams'), @id_sysdiagrams = object_id(N'dbo.sysdiagrams'), @id_helpdiagrams = object_id(N'dbo.sp_helpdiagrams'), @id_helpdiagramdefinition = object_id(N'dbo.sp_helpdiagramdefinition'), @id_creatediagram = object_id(N'dbo.sp_creatediagram'), @id_renamediagram = object_id(N'dbo.sp_renamediagram'), @id_alterdiagram = object_id(N'dbo.sp_alterdiagram'), @id_dropdiagram = object_id(N'dbo.sp_dropdiagram') if @id_upgraddiagrams is not null select @InstalledObjects = @InstalledObjects + 1 if @id_sysdiagrams is not null select @InstalledObjects = @InstalledObjects + 2 if @id_helpdiagrams is not null select @InstalledObjects = @InstalledObjects + 4 if @id_helpdiagramdefinition is not null select @InstalledObjects = @InstalledObjects + 8 if @id_creatediagram is not null select @InstalledObjects = @InstalledObjects + 16 if @id_renamediagram is not null select @InstalledObjects = @InstalledObjects + 32 if @id_alterdiagram is not null select @InstalledObjects = @InstalledObjects + 64 if @id_dropdiagram is not null select @InstalledObjects = @InstalledObjects + 128 return @InstalledObjects END GO /****** Object: User Defined Function dbo.fnCleanDefaultValue Script Date: 22/07/2010 09:47:24 ******/ CREATE FUNCTION [dbo].[fnCleanDefaultValue](@sDefaultValue varchar(4000)) RETURNS varchar(4000) AS BEGIN RETURN SubString(@sDefaultValue, 2, DataLength(@sDefaultValue)-2) END GO /****** Object: User Defined Function dbo.fnColumnDefault Script Date: 22/07/2010 09:47:24 ******/ CREATE FUNCTION [dbo].[fnColumnDefault](@sTableName varchar(128), @sColumnName varchar(128)) RETURNS varchar(4000) AS BEGIN DECLARE @sDefaultValue varchar(4000) SELECT @sDefaultValue = dbo.fnCleanDefaultValue(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @sTableName AND COLUMN_NAME = @sColumnName RETURN @sDefaultValue END GO /****** Object: User Defined Function dbo.fnIsColumnPrimaryKey Script Date: 22/07/2010 09:47:24 ******/ CREATE FUNCTION [dbo].[fnIsColumnPrimaryKey](@sTableName varchar(128), @nColumnName varchar(128)) RETURNS bit AS BEGIN DECLARE @nTableID int, @nIndexID int, @i int SET @nTableID = OBJECT_ID(@sTableName) SELECT @nIndexID = indid FROM sysindexes WHERE id = @nTableID AND indid BETWEEN 1 And 254 AND (status & 2048) = 2048 IF @nIndexID Is Null RETURN 0 IF @nColumnName IN (SELECT sc.[name] FROM sysindexkeys sik INNER JOIN syscolumns sc ON sik.id = sc.id AND sik.colid = sc.colid WHERE sik.id = @nTableID AND sik.indid = @nIndexID) BEGIN RETURN 1 END RETURN 0 END GO /****** Object: User Defined Function dbo.fnTableColumnInfo Script Date: 22/07/2010 09:47:24 ******/ CREATE FUNCTION [dbo].[fnTableColumnInfo](@sTableName varchar(128)) RETURNS TABLE AS RETURN SELECT c.name AS sColumnName, c.colid AS nColumnID, dbo.fnIsColumnPrimaryKey(@sTableName, c.name) AS bPrimaryKeyColumn, CASE WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar') THEN 1 WHEN t.name IN ('decimal', 'numeric') THEN 2 ELSE 0 END AS nAlternateType, c.length AS nColumnLength, c.prec AS nColumnPrecision, c.scale AS nColumnScale, c.IsNullable, SIGN(c.status & 128) AS IsIdentity, t.name as sTypeName, dbo.fnColumnDefault(@sTableName, c.name) AS sDefaultValue FROM syscolumns c INNER JOIN systypes t ON c.xtype = t.xtype and c.usertype = t.usertype WHERE c.id = OBJECT_ID(@sTableName) GO /****** Object: User Defined Function dbo.fnTableHasPrimaryKey Script Date: 22/07/2010 09:47:24 ******/ create FUNCTION [dbo].[fnTableHasPrimaryKey](@sTableName varchar(128)) RETURNS bit AS BEGIN DECLARE @nTableID int, @nIndexID int SET @nTableID = OBJECT_ID(@sTableName) SELECT @nIndexID = indid FROM sysindexes WHERE id = @nTableID AND indid BETWEEN 1 And 254 AND (status & 2048) = 2048 IF @nIndexID IS NOT Null RETURN 1 RETURN 0 END GO--END--GO |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-13 : 04:12:09
|
| [code]If EXISTS (SELECT id FROM dbo.sysobjects where id = object_id(N'[dbo].[AbkContacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)BEGINPRINT 'DATABASE ALREADY EXIST SO OBJECTS NOT CREATED'ENDELSE[/code]Here's what my SSMS is generating for a "Does Table Exist":[code]IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AbkContacts]') AND type in (N'U'))[/code]Also your test is if the Table exists, or not, so your PRINT message is not really appropriately worded! |
 |
|
|
|
|
|
|
|
Generating Database not working