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