笔记106 创建数据库并生成数据库说明和各个表的表说明和字段说明 扩展属性方法sp_addextendedproperty 2012-11-29
1 --创建数据库并生成数据库说明和各个表的表说明和字段说明 扩展属性方法sp_addextendedproperty 2012-11-29
2
3 /*--- 创建数据库WEIBANG ---*/
4
5 USE master
6 GO
7
8 /*--- 检查是否已经存在WEIBANG数据库: 查询master数据库中的系统表sysdatabase ---*/
9 IF DB_ID('WEIBANG') IS NOT NULL
10 DROP DATABASE WEIBANG
11
12 CREATE DATABASE WEIBANG ON
13 (
14 /*--数据文件的具体描述--*/
15 name = 'WEIBANG_Data',
16 filename = 'C:\WEIBANG.mdf'
17 )
18 LOG ON
19 (
20 /*--日志文件的具体描述--*/
21 name = 'WEIBANG_Log',
22 filename = 'C:\WEIBANG_Log.ldf'
23 )
24 GO
25
26 USE WEIBANG
27 GO
28 CREATE TABLE [SystemPara]
29 (
30 [RecordNo] [int] IDENTITY(1, 1) NOT NULL ,
31 [ParaValue] [varchar](50) NULL ,
32 [Name] [varchar](50) NULL ,
33 [Description] [varchar](50) NULL
34 )
35 GO
36
37 --添加数据库注释
38 USE WEIBANG
39 GO
40 EXEC sp_addextendedproperty
41 @name = N'Caption',
42 @value = '微邦BBS数据库';
43 GO
44
45 EXEC [sys].[sp_addextendedproperty] @name = 'MS_Description', -- sysname
46 @value ='系统配置表' , -- sql_variant
47 @level0type = 'schema', -- varchar(128)
48 @level0name = 'dbo', -- sysname
49 @level1type = 'table', -- varchar(128)
50 @level1name = 'SystemPara' -- sysname
51 GO
52
53
54 EXEC sp_addextendedproperty
55 @name = N'Caption',
56 @value = '自增列',
57 @level0type = N'Schema',
58 @level0name = 'dbo',
59 @level1type = N'Table',
60 @level1name = 'SystemPara',
61 @level2type = N'Column',
62 @level2name = 'RecordNo';
63 GO
64
65 EXEC sp_addextendedproperty
66 @name = N'Caption',
67 @value = '参数值',
68 @level0type = N'Schema',
69 @level0name = 'dbo',
70 @level1type = N'Table',
71 @level1name = 'SystemPara',
72 @level2type = N'Column',
73 @level2name = 'ParaValue';
74 GO
75
76 EXEC sp_addextendedproperty
77 @name = N'Caption',
78 @value = '参数名',
79 @level0type = N'Schema',
80 @level0name = 'dbo',
81 @level1type = N'Table',
82 @level1name = 'SystemPara',
83 @level2type = N'Column',
84 @level2name = 'Name';
85 GO
86
87 EXEC sp_addextendedproperty
88 @name = N'Caption',
89 @value = '参数作用描述',
90 @level0type = N'Schema',
91 @level0name = 'dbo',
92 @level1type = N'Table',
93 @level1name = 'SystemPara',
94 @level2type = N'Column',
95 @level2name = 'Description';
96 GO
