MS SQL 模仿ORACLE的DESC

时间：2023-07-22 18:08:14

前言： 在ORACLE数据库的SQL*PLUS里面有个DES(DESCRIBE)命令,它可以返回数据库所存储对象的描述,如下所示

SQL> DESC STUDENT_SCORE
 
Name             Type       Nullable Default Comments
 
---------------- ---------- -------- ------- --------
 
STUDENT_NO       NUMBER(10)                  学号    
 
CHINESE_SCORE    NUMBER     Y                语文成绩
 
ENGLISH_SCORE    NUMBER     Y                英语成绩
 
MATH_SOCRE       NUMBER     Y                数学成绩
 
PHYSICAL_SCORE   NUMBER     Y                物理成绩
 
SPORTS_SCORE     NUMBER     Y                体育成绩
 
CHEMICAL_SCORE   NUMBER     Y                化学成绩
 
BIOLOGICAL_SCORE NUMBER     Y                生物成绩

DESC可以获取表、视图等的字段名、字段类型、以及字段注释等信息。在开发过程中，这个命令非常实用，方便，也是使用频率比较高的命令，在MS SQL中没有这个命令，倒是有个sp_help命令，也比较方便，获取的信息甚至比DESC命令还多，但是它有个缺陷，不能获取字段的注释信息，有时候给你来一堆你不想关注的信息。下面我们我们来创建一个存储过程，模拟实现DESC命令的功能以及定制一些你想要的功能。希望这个存储过程能方便大家的工作。初版代码如下：

sp_desc
  1. SET ANSI_NULLS ON;
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. IF  EXISTS(SELECT 1 FROM sysobjects WHEREid=OBJECT_ID(N'sp_desc')
  6. AND OBJECTPROPERTY(id, 'IsProcedure') =1)
  7. DROP PROCEDURE sp_desc;
  8. GO
  9. --==================================================================================================
  10. --            ProcedureName            :            sp_desc
  11. --            Author                   :            Kerry
  12. --            CreateDate               :            2013-05-13
  13. --            Blog                     :            www.cnblogs.com/kerrycode/
  14. --            Description              :            模仿ORACLE的SQLPLUS命令DESC，并且参考sp_help相关
  15. --                                                  增强功能
  16. /***************************************************************************************************
  17. Parameters                    :             参数说明
  18. ****************************************************************************************************
  19. @ObjName                  :            需要查看的对象名称，例如表名、视图等
  20. ****************************************************************************************************
  21. Modified Date            Modified User     Version            Modified Reason
  22. ****************************************************************************************************
  23. 2013-05-19                Kerry             V01.00.01        增加Print信息，提示输出内容
  24. ***************************************************************************************************/
  25. --==================================================================================================
  26. CREATE PROCEDURE sp_desc
  27. (
  28. @ObjName        VARCHAR(32)
  29. )
  30. AS
  31. SET NOCOUNT ON;
  32. DECLARE @ObjectId        INT;
  33. DECLARE @Sysobj_Type    CHAR(2);
  34. IF @ObjName IS NULL
  35. BEGIN
  36. PRINT 'you must assign the parameter @ObjNam';
  37. RETURN 0;
  38. END
  39. SELECT @ObjectId = object_id, @Sysobj_Type=type FROM sys.all_objects
  40. WHERE object_id =OBJECT_ID(@ObjName);
  41. IF @Sysobj_Type ='U' AND @ObjectId > 0
  42. BEGIN
  43. SELECT N'************表的功能描述信息**********' AS N'表的功能描述信息';
  44. --表的功能描述信息
  45. SELECT    ISNULL(value, '麻烦补齐表的功能描述信息') AS Table_Desc
  46. FROM      sys.extended_properties
  47. WHERE     major_id = @ObjectId
  48. AND minor_id = 0
  49. SELECT N'************表结构基本信息************' AS N'表结构基本信息';
  50. --列出表结构的基本信息
  51. SELECT  C.Name AS Column_Nam ,
  52. CASE WHEN T.Name = 'nvarchar'
  53. THEN T.name + '(' + CAST(C.max_length / 2 AS VARCHAR) + ')'
  54. ELSE T.name
  55. END AS Data_Type ,
  56. CASE WHEN C.Max_Length = -1 THEN 'Max'
  57. ELSE CAST(C.Max_Length AS VARCHAR)
  58. END AS Max_Length ,
  59. C.Precision ,
  60. C.Scale     ,
  61. CASE WHEN C.is_nullable = 0 THEN '×'
  62. ELSE '√'
  63. END AS Is_Nullable ,
  64. ISNULL(CAST(I.seed_value AS VARCHAR) + '-'
  65. + CAST(I.increment_value AS VARCHAR), '') AS Is_Identity ,
  66. ISNULL(M.text, '') AS Default_Value ,
  67. ISNULL(P.value, '') AS Column_Comments
  68. FROM    sys.columns C
  69. INNER JOIN sys.types T ON C.system_type_id = T.user_type_id
  70. LEFT  JOIN dbo.syscomments M ON M.id = C.default_object_id
  71. LEFT  JOIN sys.extended_properties P ON P.major_id = C.object_id
  72. AND C.column_id = P.minor_id
  73. LEFT  JOIN sys.identity_columns I ON I.column_id = C.column_id
  74. AND C.object_id = I.object_id
  75. WHERE   C.[object_id] = @ObjectId
  76. ORDER BY C.Column_Id ASC;
  77. SELECT N'**********表约束基本信息************' AS N'表约束基本信息';
  78. --表的约束信息
  79. SELECT    name ,
  80. type
  81. FROM      sys.objects
  82. WHERE     parent_object_id = @ObjectId
  83. AND type IN( 'C ', 'PK', 'UQ', 'F ', 'D ' ) ;
  84. SELECT N'********表的索引基本信息********' AS N'表的索引基本信息';
  85. --±表的索引信息
  86. SELECT    i.index_id ,
  87. i.data_space_id ,
  88. i.name ,
  89. CASE WHEN type = 0 THEN '堆'
  90. WHEN type = 1 THEN '聚集索引'
  91. WHEN type = 2 THEN '非聚集索引'
  92. WHEN type = 3 THEN 'XML'
  93. WHEN TYPE = 4 THEN '空间'
  94. END AS [type] ,
  95. i.ignore_dup_key ,
  96. i.is_unique ,
  97. i.is_hypothetical ,
  98. i.is_primary_key ,
  99. i.is_unique_constraint ,
  100. s.auto_created ,
  101. s.no_recompute
  102. FROM      sys.indexes i
  103. JOIN sys.stats s ON i.object_id = s.object_id
  104. AND i.index_id = s.stats_id
  105. WHERE     i.object_id = @ObjectId;
  106. SELECT N'********索引包含那些字段********' AS '索引字段信息';
  107. SELECT  d.name, i.index_id, c.name
  108. FROM   sys.indexes d
  109. INNER JOIN    sys.index_columns i ON d.object_id = i.object_id
  110. LEFT JOIN sys.columns c ON i.object_id = c.object_id
  111. AND i.index_column_id = c.column_id
  112. WHERE   d.object_id = @ObjectId;
  113. SELECT N'********表的触发器基本信息********' AS N'触发器信息';
  114. --表的触发器信息
  115. SELECT  trigger_name = name ,
  116. trigger_owner = USER_NAME(OBJECTPROPERTY(object_id, 'ownerid')) ,
  117. isupdate = OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') ,
  118. isdelete = OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') ,
  119. isinsert = OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') ,
  120. isafter = OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') ,
  121. isinsteadof = OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') ,
  122. trigger_schema = SCHEMA_NAME(schema_id)
  123. FROM      sys.objects
  124. WHERE     parent_object_id = @ObjectId
  125. AND type IN( 'TR', 'TA' ) ;
  126. END
  127. ELSE IF @Sysobj_Type ='V' AND @ObjectId > 0
  128. BEGIN
  129. SELECT N'*********视图的功能描述信息**********'  AS N'视图的功能描述信息';
  130. --视图的功能描述信息
  131. SELECT    ISNULL(value, N'麻烦补齐描述该视图功能的信息') AS View_Desc
  132. FROM      sys.extended_properties
  133. WHERE     major_id = @ObjectId
  134. AND minor_id = 0
  135. SELECT '*************视图基本信息*****************' AS N'视图基本信息';
  136. SELECT  C.Name AS Column_Nam ,
  137. CASE WHEN T.Name = 'nvarchar'
  138. THEN T.name + '(' + CAST(C.max_length / 2 AS VARCHAR) + ')'
  139. ELSE T.name
  140. END AS Data_Type ,
  141. CASE WHEN C.Max_Length = -1 THEN 'Max'
  142. ELSE CAST(C.Max_Length AS VARCHAR)
  143. END AS Max_Length ,
  144. C.Precision ,
  145. C.Scale     ,
  146. CASE WHEN C.is_nullable = 0 THEN '×'
  147. ELSE '√'
  148. END AS Is_Nullable ,
  149. ISNULL(CAST(I.seed_value AS VARCHAR) + '-'
  150. + CAST(I.increment_value AS VARCHAR), '') AS Is_Identity ,
  151. ISNULL(M.text, '') AS Default_Value ,
  152. ISNULL(P.value, '') AS Column_Comments
  153. FROM    sys.columns C
  154. INNER JOIN sys.types T ON C.system_type_id = T.user_type_id
  155. LEFT  JOIN dbo.syscomments M ON M.id = C.default_object_id
  156. LEFT  JOIN sys.extended_properties P ON P.major_id = C.object_id
  157. AND C.column_id = P.minor_id
  158. LEFT  JOIN sys.identity_columns I ON I.column_id = C.column_id
  159. AND C.object_id = I.object_id
  160. WHERE   C.[object_id] = @ObjectId
  161. ORDER BY C.Column_Id ASC;
  162. SELECT '**********视图脚本***********' AS '视图脚本';
  163. EXEC sp_helptext @ObjName;
  164. END
  165. ELSE IF @Sysobj_Type ='P' AND @ObjectId > 0
  166. BEGIN
  167. SELECT N'*********描述存储过程功能信息**********'  AS N'描述存储过程功能信息';
  168. --存储过程的功能描述信息
  169. SELECT    ISNULL(value, N'麻烦补齐描述该存储过程功能的信息') AS View_Desc
  170. FROM      sys.extended_properties
  171. WHERE     major_id = @ObjectId
  172. AND minor_id = 0;
  173. EXEC sp_help  @ObjName;
  174. END
  175. ELSE IF @Sysobj_Type IN('IF') AND @ObjectId > 0
  176. BEGIN
  177. SELECT N'*********描述自定义函数功能信息**********'  AS N'描述自定义函数功能信息';
  178. --描述自定义函数功能信息
  179. SELECT    ISNULL(value, N'麻烦补齐描述该自定义函数功能的信息') AS View_Desc
  180. FROM      sys.extended_properties
  181. WHERE     major_id = @ObjectId
  182. AND minor_id = 0;
  183. SELECT  'Name' = o.name ,
  184. 'Owner' = USER_NAME(OBJECTPROPERTY(object_id, 'ownerid')) ,
  185. 'Object_type' = SUBSTRING(v.name, 5, 31)
  186. FROM    sys.all_objects o ,
  187. master.dbo.spt_values v
  188. WHERE  o.object_id =@ObjectId AND  o.type = SUBSTRING(v.name, 1, 2) COLLATE database_default
  189. AND v.type = 'O9T'
  190. ORDER BY [Owner] ASC ,
  191. Object_type DESC ,
  192. Name ASC
  193. END
  194. GO

接下来，我们新建一张表来看看效果如何，视图，存储过程、自定义函数就不大战篇幅去展示了，一个例子就OK了，有兴趣的，自己试试

 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
IF EXISTS(SELECT * FROM sysobjects WHERE id=OBJECT_ID(N'Employee') AND type='U')
 
    DROP TABLE dbo.Employee;
 
GO 
 
CREATE TABLE Employee
 
(
 
    Employee_ID          INT  IDENTITY(1,1) ,
    Employee_Name        NVARCHAR(12)        ,
    Sex                     SMALLINT DEFAULT(1),
    Department_ID         INT                ,
    Salary                 FLOAT                ,
    WorkYear             INT                ,
    CONSTRAINT PK_Employee PRIMARY KEY(Employee_ID)
 
);
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Employee_ID'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工姓名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Employee_Name'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Sex'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'部门编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Department_ID'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'薪水' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'Salary'
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'工龄' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee', @level2type=N'COLUMN',@level2name=N'WorkYear'
GO
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Employee'
 
 
CREATE TRIGGER TR_Employee_Salary ON Employee
 AFTER INSERT
AS
 
DECLARE @Salary FLOAT;
 
    SELECT @Salary = Salary FROM INSERTED;
    
 IF (@Salary < 0) 
    BEGIN
        RAISERROR('The Salary  Small than 0 ',10,1);
        ROLLBACK TRANSACTION;
 
    END
        
GO
 
 
CREATE VIEW V_Employee
AS 
    SELECT Employee_ID, Employee_Name, WorkYear FROM Employee
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'员工信息表' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_Employee'
 
 
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'给用户批量赋权限的存储过程' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'sp_authorize_right'

执行存储过程，你可以获取表Employee的基本信息了，如下所示

MS SQL 模仿ORACLE的DESC

相关文章