--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>