USE [master] GO /****** 对象: Database [carfitting] 脚本日期: 08/02/2007 12:07:14 ******/ CREATE DATABASE [carfitting] ON PRIMARY ( NAME = N'carfitting', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\carfitting.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'carfitting_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\carfitting_log.ldf' , SIZE = 11200KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) GO EXEC dbo.sp_dbcmptlevel @dbname=N'carfitting', @new_cmptlevel=90 GO IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) begin EXEC [carfitting].[dbo].[sp_fulltext_database] @action = 'disable' end GO ALTER DATABASE [carfitting] SET ANSI_NULL_DEFAULT OFF GO ALTER DATABASE [carfitting] SET ANSI_NULLS OFF GO ALTER DATABASE [carfitting] SET ANSI_PADDING OFF GO ALTER DATABASE [carfitting] SET ANSI_WARNINGS OFF GO ALTER DATABASE [carfitting] SET ARITHABORT OFF GO ALTER DATABASE [carfitting] SET AUTO_CLOSE OFF GO ALTER DATABASE [carfitting] SET AUTO_CREATE_STATISTICS ON GO ALTER DATABASE [carfitting] SET AUTO_SHRINK OFF GO ALTER DATABASE [carfitting] SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE [carfitting] SET CURSOR_CLOSE_ON_COMMIT OFF GO ALTER DATABASE [carfitting] SET CURSOR_DEFAULT GLOBAL GO ALTER DATABASE [carfitting] SET CONCAT_NULL_YIELDS_NULL OFF GO ALTER DATABASE [carfitting] SET NUMERIC_ROUNDABORT OFF GO ALTER DATABASE [carfitting] SET QUOTED_IDENTIFIER OFF GO ALTER DATABASE [carfitting] SET RECURSIVE_TRIGGERS OFF GO ALTER DATABASE [carfitting] SET DISABLE_BROKER GO ALTER DATABASE [carfitting] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GO ALTER DATABASE [carfitting] SET DATE_CORRELATION_OPTIMIZATION OFF GO ALTER DATABASE [carfitting] SET TRUSTWORTHY OFF GO ALTER DATABASE [carfitting] SET ALLOW_SNAPSHOT_ISOLATION OFF GO ALTER DATABASE [carfitting] SET PARAMETERIZATION SIMPLE GO ALTER DATABASE [carfitting] SET READ_WRITE GO ALTER DATABASE [carfitting] SET RECOVERY FULL GO ALTER DATABASE [carfitting] SET MULTI_USER GO ALTER DATABASE [carfitting] SET PAGE_VERIFY CHECKSUM GO ALTER DATABASE [carfitting] SET DB_CHAINING OFF
USE [carfitting] GO /****** 对象: Table [dbo].[Puser] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Puser]( [name] [varchar](20) NOT NULL, [psw] [varchar](50) NULL, [qx] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[Psn] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Psn]( [sn] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[JHD] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[JHD]( [jhdh] [varchar](15) NOT NULL, [bm] [varchar](20) NOT NULL, [sl] [float] NULL, [jj] [money] NULL, [dj] [money] NULL, [jhrq] [datetime] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[TMP_jhd] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TMP_jhd]( [jhdh] [varchar](15) NOT NULL, [bm] [varchar](20) NOT NULL, [sl] [float] NULL, [jj] [money] NULL, [dj] [money] NULL, [jhrq] [datetime] NULL, [jhze] AS ([sl]*[jj]) ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[dwgl] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[dwgl]( [dwbm] [varchar](20) NOT NULL, [dwmc] [varchar](30) NOT NULL, [lxr] [varchar](10) NULL, [ch] [varchar](10) NULL, [dh] [varchar](15) NULL, [sh] [varchar](20) NULL, [dz] [varchar](30) NULL, [yhzh] [varchar](20) NULL, [khyh] [varchar](20) NULL, [clxx] [varchar](20) NULL, [CX] [varchar](50) NULL, [FDJH] [varchar](50) NULL, [CJH] [varchar](50) NULL, [GCSJ] [datetime] NULL, [CCRQ] [datetime] NULL, [WXRQ] [datetime] NULL, [BZ] [varchar](50) NULL, CONSTRAINT [PK_dwgl] PRIMARY KEY CLUSTERED ( [dwbm] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[gz_yggz] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[gz_yggz]( [gzyf] [varchar](10) NOT NULL, [name] [varchar](50) NOT NULL, [jbgz] [float] NOT NULL, [jb] [float] NULL, [mc] [float] NULL, [JL] [float] NULL, [dhf] [float] NULL, [qt] [float] NULL, [hj] [float] NULL, [bsj] [float] NULL, [qk] [float] NULL, [dyj] [float] NULL, [hsf] [float] NULL, [sgf] [float] NULL, [bx] [float] NULL, [qtkk] [float] NULL, [sfgz] [float] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[gz_ygxx] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[gz_ygxx]( [name] [varchar](50) NOT NULL, [xb] [varchar](4) NULL, [sr] [datetime] NULL, [zz] [varchar](200) NULL, [sfzh] [varchar](40) NULL, [zbgz] [float] NOT NULL, [zzbz] [varchar](4) NOT NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[spxx] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[spxx]( [hw] [varchar](10) NOT NULL, [bm] [varchar](20) NOT NULL, [pm] [varchar](50) NULL, [gg] [nchar](20) NULL, [cd] [varchar](50) NULL, [dw] [varchar](16) NULL, [jj] [money] NULL, [dj] [money] NULL, [sl] [float] NULL, CONSTRAINT [PK_spxx_1] PRIMARY KEY CLUSTERED ( [bm] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[XS070711082858] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[XS070711082858]( [xsdh] [varchar](15) NULL, [bm] [varchar](30) NOT NULL, [pm] [nvarchar](50) NULL, [gg] [nchar](15) NULL, [cd] [varchar](50) NULL, [sl] [float] NOT NULL, [jj] [money] NULL, [dj] [money] NOT NULL, [je] [money] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[XS070712145847] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[XS070712145847]( [xsdh] [varchar](15) NULL, [bm] [varchar](30) NOT NULL, [pm] [nvarchar](50) NULL, [gg] [nchar](15) NULL, [cd] [varchar](50) NULL, [sl] [float] NOT NULL, [jj] [money] NULL, [dj] [money] NOT NULL, [je] [money] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[xsdmx] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[xsdmx]( [xsdh] [varchar](15) NOT NULL, [bm] [varchar](30) NOT NULL, [pm] [nvarchar](50) NULL, [gg] [nchar](15) NULL, [cd] [varchar](50) NULL, [sl] [float] NOT NULL, [jj] [money] NULL, [dj] [money] NOT NULL, [je] [money] NULL, [rq] [datetime] NULL CONSTRAINT [DF_xsdmx_rq] DEFAULT (getdate()) ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[xsd] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[xsd]( [xsdh] [varchar](20) NOT NULL, [dwbm] [varchar](30) NULL, [hklx] [varchar](10) NULL, [thr] [varchar](10) NULL, [shr] [varchar](10) NULL, [bgy] [varchar](10) NULL, [zbr] [varchar](10) NULL, [xsrq] [datetime] NULL, [zje] [money] NULL, [jzbz] [varchar](2) NOT NULL CONSTRAINT [DF_xsd_jzbz] DEFAULT ('N'), CONSTRAINT [PK_xsd_1] PRIMARY KEY CLUSTERED ( [xsdh] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'货款类型(现金,支票,挂账)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'xsd', @level2type=N'COLUMN',@level2name=N'hklx' GO /****** 对象: Table [dbo].[TMP_xsdmx] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TMP_xsdmx]( [xsdh] [varchar](20) NULL, [bm] [varchar](30) NOT NULL, [pm] [varchar](50) NULL, [gg] [nchar](15) NULL, [cd] [varchar](50) NULL, [sl] [float] NOT NULL, [jj] [money] NULL, [dj] [money] NOT NULL, [je] [money] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[TableDescription] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TableDescription]( [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [TableName] [nvarchar](50) NOT NULL, [FDescription] [nvarchar](100) NULL, [FDescription_ent] [nvarchar](200) NULL, CONSTRAINT [PK_TableDescription] PRIMARY KEY CLUSTERED ( [TableName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** 对象: Table [dbo].[P_xsdmx] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[P_xsdmx]( [xsdh] [varchar](15) NOT NULL, [bm] [varchar](30) NOT NULL, [pm] [nvarchar](50) NULL, [gg] [nchar](15) NULL, [cd] [varchar](50) NULL, [sl] [float] NOT NULL, [jj] [money] NULL, [dj] [money] NOT NULL, [je] [money] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[XS070724085422] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[XS070724085422]( [xsdh] [varchar](15) NULL, [bm] [varchar](30) NOT NULL, [pm] [nvarchar](50) NULL, [gg] [nchar](15) NULL, [cd] [varchar](50) NULL, [sl] [float] NOT NULL, [jj] [money] NULL, [dj] [money] NOT NULL, [je] [money] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[dybq] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[dybq]( [dwmc] [varchar](30) NULL, [lxdh] [varchar](30) NULL, [lxr] [varchar](10) NULL, [jsdw] [varchar](30) NULL, [ch] [varchar](30) NULL, [dh] [varchar](30) NULL, [yhzh] [varchar](30) NULL, [khyh] [varchar](30) NULL, [sh] [varchar](30) NULL, [jssj] [varchar](30) NULL, [hj] [varchar](30) NULL, [zje] [varchar](30) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[kcxx] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[kcxx]( [bm] [varchar](20) NOT NULL, [sl] [float] NULL, [jj] [money] NULL, [dj] [money] NULL, CONSTRAINT [PK_kcxx] PRIMARY KEY CLUSTERED ( [bm] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** 对象: Table [dbo].[FieldDescription] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FieldDescription]( [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [TableName] [nvarchar](50) NOT NULL DEFAULT (''), [FieldName] [nvarchar](50) NOT NULL DEFAULT (''), [FieldType] [nvarchar](20) NOT NULL, [Prec] [smallint] NULL, [Scale] [smallint] NULL, [FDescription] [nvarchar](100) NULL, [FDescription_ent] [nvarchar](200) NULL, CONSTRAINT [PK_t_TableDescription] PRIMARY KEY NONCLUSTERED ( [TableName] ASC, [FieldName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** 对象: StoredProcedure [dbo].[UpdateDict] 脚本日期: 08/02/2007 12:01:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[UpdateDict] AS SET nocount ON DECLARE @strTable NVARCHAR(100), --表名 @Fields INT--字段數
--1.1刪除已經不存在的表 DELETE tabledescription WHERE tablename NOT IN(SELECT Name FROM sys.tables)
--1.2將新增的表插入到:TableDescription INSERT INTO tabledescription(tablename) SELECT name FROM sys.tables WHERE name NOT IN (SELECT tablename FROM tabledescription)
--2.更新表結構到FieldDescription DECLARE cur CURSOR FOR SELECT tablename FROM tabledescription OPEN cur FETCH NEXT FROM cur INTO @strTable WHILE @@FETCH_STATUS = 0
BEGIN --2.1如果沒有則將表結構插入到FieldDescription SELECT @Fields = Count(* ) FROM fielddescription WHERE tablename = @strTable SELECT tablename = @strTable, sn = a.colorder, fieldname = a.name, fieldtype = b.name, prec = CASE b.name WHEN 'nvarchar' THEN a.length / 2 ELSE a.length END, scale = CASE WHEN a.xtype = 61 THEN 0 ELSE Isnull(Columnproperty(a.id,a.name,'scale'),0) END INTO #aa FROM syscolumns a LEFT JOIN systypes b ON a.id = Object_id(@strTable) AND a.xusertype = b.xusertype INNER JOIN sysobjects d ON d.id = Object_id(@strTable) AND a.id = d.id AND d.xtype = 'u' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id WHERE d.name = @strTable
IF @Fields = 0 INSERT INTO fielddescription(tablename, fieldname, fieldtype, prec, scale) SELECT tablename, fieldname, fieldtype, prec, scale FROM #aa ORDER BY sn
ELSE
BEGIN --2.2插入新增的欄位 INSERT INTO fielddescription(tablename, fieldname, fieldtype, prec, scale) SELECT tablename, fieldname, fieldtype, prec, scale FROM #aa WHERE fieldname NOT IN (SELECT fieldname FROM fielddescription WHERE tablename = @strTable) ORDER BY sn
--2.3刪除已經不存在的欄位 DELETE fielddescription WHERE tablename = @strTable AND fieldname NOT IN (SELECT fieldname FROM #AA)
--2.4更新欄位類型等內容 UPDATE a SET a.fieldtype = b.fieldtype, a.prec = b.prec, a.scale = b.scale FROM fielddescription a, #aa b WHERE a.tablename = @strTable AND a.tablename = b.tablename AND a.fieldname = b.fieldname END
DROP TABLE #aa
--2.5如果在擴展屬性事有對應的說明,則更新 SELECT a.name 'fieldname', b.VALUE INTO #bb FROM sys.columns a, sys.extended_properties b WHERE object_id = Object_id(@strTable) AND a.object_id = b.major_id AND a.column_id = minor_id
UPDATE a SET a.FDescription =convert(NVARCHAR(100), b.VALUE) FROM fielddescription a, #bb b WHERE a.tablename =@strTable AND a.fieldname = b.fieldname AND ISNULL(b.Value,'')<>''
DROP TABLE #bb
FETCH NEXT FROM cur INTO @strTable END
CLOSE cur DEALLOCATE cur IF @@ERROR <> 0 ROLLBACK TRAN SET nocount off GO /****** 对象: ForeignKey [FK_FieldDescription_TableDescription] 脚本日期: 08/02/2007 12:01:23 ******/ ALTER TABLE [dbo].[FieldDescription] WITH CHECK ADD CONSTRAINT [FK_FieldDescription_TableDescription] FOREIGN KEY([TableName]) REFERENCES [dbo].[TableDescription] ([TableName]) ON UPDATE CASCADE ON DELETE CASCADE GO ALTER TABLE [dbo].[FieldDescription] CHECK CONSTRAINT [FK_FieldDescription_TableDescription] GO