idmax.sql

这是一个关于论坛的一点小的源代码

SET  ARITHABORT, CONCAT_NULL_YIELDS_NULL, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, QUOTED_IDENTIFIER ON 
SET  NUMERIC_ROUNDABORT OFF
GO
:setvar DatabaseName "idMax"
:setvar PrimaryFilePhysicalName "E:\SQL Server 2005 Data\MSSQL.1\MSSQL\DATA\idMax.mdf"
:setvar PrimaryLogFilePhysicalName "E:\SQL Server 2005 Data\MSSQL.1\MSSQL\DATA\idMax_log.ldf"

USE [master]

GO

:on error exit

IF  (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'目标数据库 %s 的状态未设置为 ONLINE。要部署此数据库，数据库状态必须设置为 ONLINE。', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END
GO

:on error exit

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END
GO
CREATE DATABASE [$(DatabaseName)] ON ( NAME = N'PrimaryFileName', FILENAME = N'$(PrimaryFilePhysicalName)') LOG ON ( NAME = N'PrimaryLogFileName', FILENAME = N'$(PrimaryLogFilePhysicalName)') COLLATE Chinese_PRC_CI_AS 

GO

:on error resume
     
EXEC sp_dbcmptlevel N'$(DatabaseName)', 80

GO

IF EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)') 
    ALTER DATABASE [$(DatabaseName)] SET  
	MULTI_USER,
	CURSOR_CLOSE_ON_COMMIT OFF,
	CURSOR_DEFAULT LOCAL,
	AUTO_CLOSE OFF,
	AUTO_CREATE_STATISTICS ON,
	AUTO_SHRINK OFF,
	AUTO_UPDATE_STATISTICS ON,
	ANSI_NULL_DEFAULT ON,
	ANSI_NULLS ON,
	ANSI_PADDING ON,
	ANSI_WARNINGS ON,
	ARITHABORT ON,
	CONCAT_NULL_YIELDS_NULL ON,
	NUMERIC_ROUNDABORT OFF,
	QUOTED_IDENTIFIER ON,
	RECURSIVE_TRIGGERS OFF,
	RECOVERY SIMPLE,
	TORN_PAGE_DETECTION OFF
	WITH ROLLBACK IMMEDIATE
GO

IF (IS_SRVROLEMEMBER(N'sysadmin') = 1)
BEGIN
    EXEC sp_dboption N'$(DatabaseName)', 'db chaining', 'OFF'
END
ELSE
BEGIN
    RAISERROR(N'无法为 DB_CHAINING 或 TRUSTWORTHY 修改数据库设置。您必须是 SysAdmin 才能应用这些设置。',0,1)
END


GO

USE [$(DatabaseName)]

GO
-- 预先部署脚本模板							
----------------------------------------------------------------------------------------
-- 此文件包含将在生成脚本前执行的 SQL 语句	
-- 使用 SQLCMD 语法将文件包含在预先部署脚本中			
-- 示例:      :r .\filename.sql								
-- 使用 SQLCMD 语法引用预先部署脚本中的变量		
-- 示例:      :setvar $TableName MyTable							
--               SELECT * FROM [$(TableName)]					
----------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Max_Identities_4000]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[Max_Identities_4000]
GO

SELECT TOP 4000 ID = IDENTITY(int,1,1) INTO [dbo].[Max_Identities_4000]
FROM syscolumns a, syscolumns b
ALTER TABLE [dbo].[Max_Identities_4000] ADD CONSTRAINT PK_Max_Identities_4000 PRIMARY KEY(ID)
GO


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Max_Identities_8000]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[Max_Identities_8000]
GO

SELECT TOP 8000 ID = IDENTITY(int,1,1) INTO [dbo].[Max_Identities_8000]
FROM syscolumns a, syscolumns b
ALTER TABLE [dbo].[Max_Identities_8000] ADD CONSTRAINT PK_Max_Identities_8000 PRIMARY KEY(ID)
GO
GO








GO

:on error exit

:on error resume
GO
PRINT N'正在创建 [dbo].[Max_Split_text]'
GO
CREATE FUNCTION [dbo].[Max_Split_text]
(
	@text text,
	@Separator varchar(2) = ','
)
RETURNS @ItemTable TABLE 
(
	id int identity(1,1),
	item varchar(256) 
)
AS
BEGIN

	DECLARE @s varchar(8000), @i int, @j int   
	SELECT @s = SUBSTRING(@text, 1, 8000), @i=1   
	IF (@s = '')
		INSERT @ItemTable VALUES ('')
	ELSE BEGIN
		WHILE @s <> '' BEGIN

			IF len(@s) = 8000   
				SELECT @j = 8000 - CHARINDEX(@Separator, REVERSE(@s)), @i = @i + @j + 1, @s = LEFT(@s, @j)
			ELSE     
				SELECT @i = @i + 8000, @j = LEN(@s)

			INSERT @ItemTable
			SELECT SUBSTRING(@s, ID ,CHARINDEX(@Separator, @s + @Separator, ID) - ID)   
			FROM Max_Identities_8000 WITH (NOLOCK)
			WHERE ID <= @j + 1 AND CHARINDEX(@Separator, @Separator + @s, ID) - ID = 0   

			SELECT @s = SUBSTRING(@text, @i, 8000)

		END  
	END
	RETURN;
END
GO
PRINT N'正在创建 [dbo].[Max_AdminLoginLogs]'
GO
CREATE TABLE [dbo].[Max_AdminLoginLogs]
(
[LogID] [int] NOT NULL IDENTITY(1, 1),
[UserID] [int] NOT NULL,
[UserIP] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[ErrorPassword] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_AdminLoginLog_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_AdminLoginLogs] 上创建主键 [PK_Max_AdminLoginLog]'
GO
ALTER TABLE [dbo].[Max_AdminLoginLogs] ADD CONSTRAINT [PK_Max_AdminLoginLog] PRIMARY KEY CLUSTERED  ([LogID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_Split_ntext]'
GO
CREATE FUNCTION [dbo].[Max_Split_ntext]
(
	@text ntext,
	@Separator nvarchar(2) = N','
)
RETURNS @ItemTable TABLE 
(
	id int identity(1,1),
	item nvarchar(256) 
)
AS
BEGIN

	DECLARE @s nvarchar(4000), @i int, @j int   
	SELECT @s = SUBSTRING(@text, 1, 4000), @i=1  
	IF (@s = '')
		INSERT @ItemTable VALUES ('')
	ELSE BEGIN 
		WHILE @s <> '' BEGIN

			IF len(@s) = 4000   
				SELECT @j = 4000 - CHARINDEX(@Separator, REVERSE(@s)), @i = @i + @j + 1, @s = LEFT(@s, @j)
			ELSE     
				SELECT @i = @i + 4000, @j = LEN(@s)

			INSERT @ItemTable
			SELECT SUBSTRING(@s, ID ,CHARINDEX(@Separator, @s + @Separator, ID) - ID)   
			FROM Max_Identities_4000 WITH (NOLOCK)
			WHERE ID <= @j + 1 AND CHARINDEX(@Separator, @Separator + @s, ID) - ID = 0   

			SELECT @s = SUBSTRING(@text, @i, 4000)

		END   
	END
	RETURN;
END
GO
PRINT N'正在创建 [dbo].[System_Max_PermissionSchemes]'
GO
CREATE TABLE [dbo].[System_Max_PermissionSchemes]
(
[SchemeID] [int] NOT NULL IDENTITY(1, 1),
[PermissionType] [varchar] (256) NOT NULL,
[SchemeName] [nvarchar] (256) NOT NULL,
[Description] [ntext] NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_Permissions_CreateDate] DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[System_Max_PermissionSchemes] 的索引 [IX_System_Max_PermissionSchemes]'
GO
CREATE UNIQUE CLUSTERED INDEX [IX_System_Max_PermissionSchemes] ON [dbo].[System_Max_PermissionSchemes] ([PermissionType], [SchemeID]) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[System_Max_PermissionSchemes] 上创建主键 [PK_System_Max_PermissionSchemes]'
GO
ALTER TABLE [dbo].[System_Max_PermissionSchemes] ADD CONSTRAINT [PK_System_Max_PermissionSchemes] PRIMARY KEY NONCLUSTERED  ([SchemeID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_ChangePasswordLogs]'
GO
CREATE TABLE [dbo].[Max_ChangePasswordLogs]
(
[LogID] [int] NOT NULL IDENTITY(1, 1),
[UserID] [int] NOT NULL,
[UserName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[OldPassword] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_Max_ChangePasswordLogs_CreateDate] DEFAULT (getdate())
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_ChangePasswordLogs] 上创建主键 [PK_Max_ChangePasswordLogs]'
GO
ALTER TABLE [dbo].[Max_ChangePasswordLogs] ADD CONSTRAINT [PK_Max_ChangePasswordLogs] PRIMARY KEY CLUSTERED  ([LogID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_ContactGroups]'
GO
CREATE TABLE [dbo].[Max_ContactGroups]
(
[GroupID] [int] NOT NULL IDENTITY(1, 1),
[GroupName] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_ContactGroups] 上创建主键 [PK_Max_ContactsGroups]'
GO
ALTER TABLE [dbo].[Max_ContactGroups] ADD CONSTRAINT [PK_Max_ContactsGroups] PRIMARY KEY CLUSTERED  ([GroupID]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_ContactGroups] 的索引 [IX_Max_ContactGroups_List]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Max_ContactGroups_List] ON [dbo].[Max_ContactGroups] ([UserID], [GroupName]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_IpAddresses]'
GO
CREATE TABLE [dbo].[Max_IpAddresses]
(
[StartIP] [bigint] NOT NULL,
[EndIP] [bigint] NOT NULL,
[Location1] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL,
[Location2] [nvarchar] (64) COLLATE Chinese_PRC_CI_AS_WS NOT NULL
) ON [PRIMARY]
GO
PRINT N'正在 [dbo].[Max_IpAddresses] 上创建主键 [PK_Max_IpAddresses]'
GO
ALTER TABLE [dbo].[Max_IpAddresses] ADD CONSTRAINT [PK_Max_IpAddresses] PRIMARY KEY CLUSTERED  ([StartIP], [EndIP]) ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_GetLocationByIP]'
GO
CREATE PROCEDURE [dbo].[Max_GetLocationByIP]
	@IP bigint
AS

	SET NOCOUNT ON
	SELECT Location1,Location2 FROM [Max_IpAddresses]  WITH (NOLOCK) WHERE (StartIP < @IP) AND (EndIP > @IP)
	RETURN
GO
PRINT N'正在创建 [dbo].[Max_SystemMessages]'
GO
CREATE TABLE [dbo].[Max_SystemMessages]
(
[MessageID] [int] NOT NULL IDENTITY(1, 1),
[Roles] [nvarchar] (100) NOT NULL,
[Subject] [nvarchar] (512) NOT NULL,
[Content] [ntext] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'正在创建 [dbo].[Max_CreateSystemMessage]'
GO
---Create Date 2007/9/6 By 帅帅
CREATE PROCEDURE [dbo].[Max_CreateSystemMessage]
	@Roles NVARCHAR(100),
	@Subject NVARCHAR(512) ,
	@Content NTEXT,
	@EndTime DATETIME,
	@MessageID INT OUTPUT
AS
	INSERT Max_SystemMessages
	(
		Roles,
		Subject,
		Content,
		CreateDate,
		EndTime
		
	)
	VALUES
	(
		@Roles,
		@Subject,
		@Content,
		getdate(),
		@EndTime
	)
	SET @MessageID = @@Identity
	IF(@@ROWCOUNT>0)
		RETURN 0
	ELSE
		RETURN -1
GO
PRINT N'正在创建 [dbo].[Max_Common_GetRecordsByPage]'
GO
-- =============================================
-- Author:		zzbird
-- Create date: 2006/12/07
-- Description:	Page
-- =============================================
CREATE PROCEDURE [dbo].Max_Common_GetRecordsByPage
	@PageIndex int,
	@PageSize int,
	@TableName varchar(256),
	@SelectFields varchar(512) = '*', --查询字段，默认为 *
	@Condition nvarchar(256) = N'',     --条件例如"DirectoryID=4"
	@SortField varchar(256) = '[SortOrder]',
	@IsDesc bit = 1, --是否倒序
	@TotalRecords int = -1
AS
BEGIN

	SET NOCOUNT ON;

	DECLARE @SQLString nvarchar(4000);
	DECLARE @WhereString1 nvarchar(800);
	DECLARE @WhereString2 nvarchar(800);
	DECLARE @ResetOrder bit----------- 1表示读取数据的时候 排序要反过来
	IF @Condition IS NULL OR @Condition = N'' BEGIN
		SELECT @WhereString1 = N'';
		SELECT @WhereString2 = N'WHERE ';
	END
	ELSE BEGIN
		SELECT @WhereString1 = N'WHERE ' + @Condition;
		SELECT @WhereString2 = N'WHERE ' + @Condition + N' AND ';
	END

	IF @PageIndex = 0 BEGIN
		SELECT @SQLString = N'SELECT TOP ' + STR(@PageSize)
			+ N' ' + @SelectFields
			+ N' FROM ' + @TableName + ' WITH (NOLOCK)
			' + @WhereString1 + '
			ORDER BY ' + @SortField;
		IF @IsDesc = 1
			SELECT @SQLString = @SQLString + ' DESC';
		SET @ResetOrder=0	
	END
	ELSE BEGIN
	-----------------------------------------------
		DECLARE @GetFromLast BIT
		IF @TotalRecords=-1
			SET @GetFromLast=0
		ELSE BEGIN
			DECLARE @TotalPage INT,@ResidualCount INT
			SET @ResidualCount=@TotalRecords%@PageSize
			IF @ResidualCount=0
				SET @TotalPage=@TotalRecords/@PageSize
			ELSE
				SET @TotalPage=@TotalRecords/@PageSize+1
			IF @PageIndex>@TotalPage/2 --从最后页算上来
				SET @GetFromLast=1