T-SQL 函数获取数据字典

1、查询函数 

CREATE FUNCTION [dbo].[get_dicName] (@ItemCode VARCHAR(50), @ItemValue VARCHAR(50))
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS NVARCHAR(50)   --返回返回值的数据类型
AS
BEGIN
  DECLARE @result_name NVARCHAR(50)
  SELECT
    @result_name = d.ItemName
  FROM [dbo].Base_DataItem b
  LEFT JOIN [Base_DataItemDetail] d
    ON b.ItemId = d.ParentId
  WHERE b.ItemCode = @ItemCode
  AND d.ItemValue = @ItemValue
  RETURN @result_name
END
 
--select dbo.func_date_get_name('20180808') name;
--select * from test_ceshi;

2、数据字典主表

CREATE TABLE [dbo].[Base_DataItem](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ItemId] [varchar](50) NOT NULL,
	[ParentId] [varchar](50) NULL,
	[ItemCode] [varchar](50) NULL,
	[ItemName] [nvarchar](50) NULL,
	[IsTree] [int] NULL,
	[IsNav] [int] NULL,
	[IsDefault] [int] NULL,
	[SortCode] [int] NULL,
	[DeleteMark] [int] NULL,
	[EnabledMark] [int] NULL,
	[Description] [nvarchar](200) NULL,
	[CreateDate] [datetime] NULL,
	[CreateUserId] [varchar](50) NULL,
	[CreateUserName] [nvarchar](50) NULL,
	[ModifyDate] [datetime] NULL,
	[ModifyUserId] [varchar](50) NULL,
	[ModifyUserName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Base_DataItem] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Base_DataItem] ADD  DEFAULT ((0)) FOR [IsDefault]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据字典主表，左侧树结构' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItem'
GO

3、子表

CREATE TABLE [dbo].[Base_DataItemDetail](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ItemDetailId] [varchar](50) NOT NULL,
	[ItemId] [varchar](50) NULL,
	[ParentId] [varchar](50) NULL,
	[ItemCode] [nvarchar](50) NULL,
	[ItemName] [nvarchar](50) NULL,
	[ItemValue] [nvarchar](50) NULL,
	[QuickQuery] [nvarchar](200) NULL,
	[SimpleSpelling] [nvarchar](200) NULL,
	[IsDefault] [int] NULL,
	[SortCode] [int] NULL,
	[DeleteMark] [int] NULL,
	[EnabledMark] [int] NULL,
	[Description] [nvarchar](200) NULL,
	[CreateDate] [datetime] NULL,
	[CreateUserId] [varchar](50) NULL,
	[CreateUserName] [nvarchar](50) NULL,
	[ModifyDate] [datetime] NULL,
	[ModifyUserId] [varchar](50) NULL,
	[ModifyUserName] [nvarchar](50) NULL,
 CONSTRAINT [PK_BASE_DATAITEMDETAIL] PRIMARY KEY NONCLUSTERED 
(
	[ItemDetailId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'明细主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ItemDetailId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'分类主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ItemId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'父级主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ParentId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ItemCode'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ItemName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ItemValue'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'快速查询' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'QuickQuery'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'简拼' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'SimpleSpelling'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否默认' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'IsDefault'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'排序码' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'SortCode'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'删除标记' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'DeleteMark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'有效标志' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'EnabledMark'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'Description'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'CreateDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建用户主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'CreateUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建用户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'CreateUserName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ModifyDate'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改用户主键' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ModifyUserId'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改用户' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail', @level2type=N'COLUMN',@level2name=N'ModifyUserName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据字典明细表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_DataItemDetail'
GO