Rumah > pangkalan data > tutorial mysql > 用表来管理SQLServer中的扩展属性(描述)

用表来管理SQLServer中的扩展属性(描述)

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Lepaskan: 2016-06-07 15:33:56
asal
1023 orang telah melayarinya

数据字典是个好东东,对于开发、维护非常重要。 但Sql Server中写描述确实不方便,如何化繁为简、批量地增加修改扩展属性呢? 增加2个表和5个存储过程、2个触发器、1个表函数就好了。 把下面的SQL执行一遍生成相关的对象, 然后执行一下: 1. EXECProc_Util_

数据字典是个好东东,对于开发、维护非常重要。

但Sql Server中写描述确实不方便,如何化繁为简、批量地增加修改扩展属性呢?

增加2个表和5个存储过程、2个触发器、1个表值函数就好了。

把下面的SQL执行一遍生成相关的对象, 然后执行一下:

1. EXEC Proc_Util_Desc_GetColumnNameToDescTable , 生成表的描述对应记录

2. EXEC Proc_Util_Desc_GetTableNameToDescTable, 生成列的描述对应记录

3. 查看, 修改一下 dc_util_column_desc 中的某个表某个列的描述,

4. 查看: select * from [dbo].[Fun_GetTableStru]('表名')

爽吧?!


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

--1.1 建表(存放表的描述):dbo.dc_util_table_desc

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dc_util_table_desc]') AND type in (N'U'))

    DROP TABLE [dbo].[dc_util_table_desc]

GO

CREATE TABLE [dbo].[dc_util_table_desc](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [tableName] [varchar](100) NULL,

    [tableDesc] [nvarchar](200) NULL,

 CONSTRAINT [PK_dc_util_table_desc] PRIMARY KEY CLUSTERED

(

    [id] 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

 

--1.2 建表(存放列的描述):[dc_util_column_desc]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dc_util_column_desc]') AND type in (N'U'))

    DROP TABLE [dbo].[dc_util_column_desc]

GO

CREATE TABLE [dbo].[dc_util_column_desc](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [tableName] [varchar](100) NULL,

    [columnName] [varchar](100) NULL,

    [columnDesc] [nvarchar](200) NULL,

 CONSTRAINT [PK_dc_util_column_desc] PRIMARY KEY CLUSTERED

(

    [id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],

 CONSTRAINT [UQ_dc_util_column_desc_tableName_columnName] UNIQUE NONCLUSTERED

(

    [tableName] ASC,

    [columnName] 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

 

--2.1 存储过程

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_DeleteInvalidData]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[Proc_Util_Desc_DeleteInvalidData]

GO

-- =============================================

-- Author:      yenange

-- Create date: 2014-05-29

-- Description: 删除 dc_util_table_desc 表和

--              dc_util_column_desc 表中不正确的数据

-- =============================================

CREATE PROCEDURE [dbo].[Proc_Util_Desc_DeleteInvalidData]

AS

BEGIN

    SET NOCOUNT ON;

    --删除 dc_util_table_desc 中的无效数据

    DELETE FROM dbo.dc_util_table_desc WHERE NOT EXISTS (

        SELECT 1 FROM sys.tables T WHERE dbo.dc_util_table_desc.tableName=T.name

    )

    --删除 dc_util_column_desc 中的无效数据

    DELETE

    FROM   dbo.dc_util_column_desc

    WHERE  NOT EXISTS (SELECT 1 FROM sys.tables t INNER JOIN sys.columns c ON  t.object_id = c.object_id

                            WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID FROM sys.schemas WHERE  NAME = 'dbo')

                                AND dbo.dc_util_column_desc.tableName=t.name AND dbo.dc_util_column_desc.columnName=c.name 

                        )

END

GO

 

--2.2 存储过程

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_GetTableNameToDescTable]') AND type in (N'P', N'PC'))

    DROP PROCEDURE [dbo].[Proc_Util_Desc_GetTableNameToDescTable]

GO

-- =============================================

-- Author:     

-- Create date: 2014-05-29

-- Description: 将以 @tablePrefix 为前缀的表名和表对应的扩展属性 insert 到 dc_util_table_desc 表中去.

--              @tablePrefix 如果为 '' 或者 null, 则为全部表(默认为null)

--              @overrideDesc : 如果已有记录存在,是否覆盖原记录的扩展属性 (默认为1)

-- =============================================

CREATE procedure [dbo].[Proc_Util_Desc_GetTableNameToDescTable]

    @tablePrefix VARCHAR(100) =null,

    @overrideDesc BIT =1

AS

BEGIN

    SET NOCOUNT ON

    --删除表中无效的数据

    exec Proc_Util_Desc_DeleteInvalidData

     

    DECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),tabledesc NVARCHAR(200))

    --插入以 @tablePrefix 为前缀的表到@t1

    INSERT INTO @t1

    (

        tablename,

        tabledesc

    )

    SELECT convert(VARCHAR(100),t.name),

           convert (nvarchar(200),p.value)

    FROM   sys.tables                         AS t

           LEFT JOIN sys.extended_properties  AS p

                ON  p.major_id = t.object_id

                AND p.minor_id = 0

                AND p.class = 1

                AND p.name = 'MS_Description'

    WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID

                           FROM   sys.schemas

                           WHERE  NAME = 'dbo')

     AND (ISNULL(@tablePrefix,'')='' or t.name LIKE +@tablePrefix+'%' )

     

    DECLARE @i INT

    DECLARE @i_max INT

    DECLARE @t_name VARCHAR(100)

    DECLARE @t_desc NVARCHAR(200)

    SET @i=1

    SELECT @i_max=COUNT(1) FROM @t1

    WHILE @i<br>

<br>

Salin selepas log masuk
Kenyataan Laman Web ini
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn
Tutorial Popular
Lagi>
Muat turun terkini
Lagi>
kesan web
Kod sumber laman web
Bahan laman web
Templat hujung hadapan