MS SQL imitation ORACLE DESC

       Objective: to have a DES in the ORACLE database SQL*PLUS (DESCRIBE) command, it can return the database storage object description, as shown below 

SQL> DESC STUDENT_SCORE   Name Type Nullable Default Comments   ---------------- ---------- -------- ------- --------   STUDENT_NO NUMBER(10) Student ID   CHINESE_SCORE NUMBER Y Language achievement   ENGLISH_SCORE NUMBER Y English scores   MATH_SOCRE NUMBER Y Mathematics achievement   PHYSICAL_SCORE NUMBER Y Physical performance   SPORTS_SCORE NUMBER Y Sports scores   CHEMICAL_SCORE NUMBER Y The chemical score   BIOLOGICAL_SCORE NUMBER Y Biological performance

DESC can acquire a table, view, field names, field type, and field notes and other information. In the development process, this command is very practical, convenient, and relatively high frequency of use command, not the command in the MS SQL, is a sp_help command, but also more convenient access to information, even more than the DESC command, but it has a flaw, cannot retrieve field annotation information, give you a lot of you don't want to pay attention to information sometimes. Below we to create a stored procedure, simulation function of DESC command and customize some functions you want. Hope this stored procedure can facilitate the work. The first edition of code as follows:  

sp_desc
  1.    
  2. SET ANSI_NULLS ON;
  3. GO
  4.  
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7.  
  8.   IF  EXISTS(SELECT 1 FROM sysobjects WHEREid=OBJECT_ID(N'sp_desc')
  9.                       AND OBJECTPROPERTY(id, 'IsProcedure') =1)
  10.     DROP PROCEDURE sp_desc;
  11. GO
  12.  
  13. --==================================================================================================
  14. --            ProcedureName            :            sp_desc
  15. --            Author                   :            Kerry
  16. --            CreateDate               :            2013-05-13
  17. --            Blog                     :            www.cnblogs.com/kerrycode/
  18. --            Description              :            ORACLE imitate the SQLPLUS command DESC, and the reference sp_help
  19. --                                                  Enhancements
  20. /***************************************************************************************************
  21.         Parameters                    :             Explanation of parameters
  22. ****************************************************************************************************
  23.             @ObjName                  :            To view the object name, such as the name of the table, view, etc.
  24. ****************************************************************************************************
  25.         Modified Date            Modified User     Version            Modified Reason
  26. ****************************************************************************************************
  27.        2013-05-19                Kerry             V01.00.01        Increase of Print information, prompt output content
  28. ***************************************************************************************************/
  29. --==================================================================================================
  30. CREATE PROCEDURE sp_desc
  31. (
  32.     @ObjName        VARCHAR(32)
  33. )
  34. AS
  35.  
  36. SET NOCOUNT ON;
  37.  
  38. DECLARE @ObjectId        INT;
  39. DECLARE @Sysobj_Type    CHAR(2);
  40.  
  41. IF @ObjName IS NULL
  42.     BEGIN
  43.         PRINT 'you must assign the parameter @ObjNam';
  44.         
  45.         RETURN 0;
  46.     END
  47.  
  48.  
  49. SELECT @ObjectId = object_id, @Sysobj_Type=type FROM sys.all_objects
  50.     WHERE object_id =OBJECT_ID(@ObjName);
  51.     
  52. IF @Sysobj_Type ='U' AND @ObjectId > 0
  53. BEGIN
  54.     
  55.                 
  56.         SELECT N'************The function table description information **********'AS N' table function description information';
  57.         
  58.         -- the function table description information
  59.         SELECT    ISNULL(value, 'Trouble to complete the table function description information') AS Table_Desc
  60.         FROM      sys.extended_properties
  61.         WHERE     major_id = @ObjectId
  62.             AND minor_id = 0
  63.          
  64.         SELECT N'************Table structure basic information ************'AS N' basic information structure';
  65.            
  66.         Basic information: lists the table structure
  67.         SELECT  C.Name AS Column_Nam ,
  68.                 CASE WHEN T.Name = 'nvarchar'
  69.                      THEN T.name + '(' + CAST(C.max_length / 2 AS VARCHAR) + ')'
  70.                      ELSE T.name
  71.                 END AS Data_Type ,
  72.                 CASE WHEN C.Max_Length = -1 THEN 'Max'
  73.                      ELSE CAST(C.Max_Length AS VARCHAR)
  74.                 END AS Max_Length ,
  75.                 C.Precision ,
  76.                 C.Scale     ,
  77.                 CASE WHEN C.is_nullable = 0 THEN '×'
  78.                      ELSE '√'
  79.                 END AS Is_Nullable ,
  80.                 ISNULL(CAST(I.seed_value AS VARCHAR) + '-'
  81.                        + CAST(I.increment_value AS VARCHAR), '') AS Is_Identity ,
  82.                 ISNULL(M.text, '') AS Default_Value ,
  83.                 ISNULL(P.value, '') AS Column_Comments
  84.         FROM    sys.columns C
  85.                 INNER JOIN sys.types T ON C.system_type_id = T.user_type_id
  86.                 LEFT  JOIN dbo.syscomments M ON M.id = C.default_object_id
  87.                 LEFT  JOIN sys.extended_properties P ON P.major_id = C.object_id
  88.                                                         AND C.column_id = P.minor_id
  89.                 LEFT  JOIN sys.identity_columns I ON I.column_id = C.column_id
  90.                                                      AND C.object_id = I.object_id
  91.         WHERE   C.[object_id] = @ObjectId
  92.         ORDER BY C.Column_Id ASC;
  93.         
  94.         SELECT N'**********Table constraint information ************'AS N' table constraint information';
  95.         
  96.         Constraint information table
  97.           SELECT    name ,
  98.                     type
  99.           FROM      sys.objects
  100.           WHERE     parent_object_id = @ObjectId
  101.                     AND type IN( 'C ', 'PK', 'UQ', 'F ', 'D ' ) ;
  102.         
  103.         
  104.         SELECT N'********Index to the basic information ********'AS N' index table of basic information table';
  105.         
  106.           --±The index information table
  107.           SELECT    i.index_id ,
  108.                     i.data_space_id ,
  109.                     i.name ,
  110.                     CASE WHEN type = 0 THEN 'Heap'
  111.                          WHEN type = 1 THEN 'The clustered index'
  112.                          WHEN type = 2 THEN 'A non clustered index'
  113.                          WHEN type = 3 THEN 'XML'
  114.                          WHEN TYPE = 4 THEN 'Space'
  115.                     END AS [type] ,
  116.                     i.ignore_dup_key ,
  117.                     i.is_unique ,
  118.                     i.is_hypothetical ,
  119.                     i.is_primary_key ,
  120.                     i.is_unique_constraint ,
  121.                     s.auto_created ,
  122.                     s.no_recompute
  123.           FROM      sys.indexes i
  124.                     JOIN sys.stats s ON i.object_id = s.object_id
  125.                                         AND i.index_id = s.stats_id
  126.           WHERE     i.object_id = @ObjectId;
  127.   
  128.           SELECT N'********The index contains the field ********'AS' index field information';
  129.             
  130.           SELECT  d.name, i.index_id, c.name
  131.             FROM   sys.indexes d
  132.                 INNER JOIN    sys.index_columns i ON d.object_id = i.object_id
  133.                     LEFT JOIN sys.columns c ON i.object_id = c.object_id
  134.                                                AND i.index_column_id = c.column_id
  135.             WHERE   d.object_id = @ObjectId;
  136.         
  137.         
  138.          SELECT N'********Table of basic information ********'trigger AS N' trigger information';
  139.         The trigger information table
  140.         
  141.         SELECT  trigger_name = name ,
  142.                 trigger_owner = USER_NAME(OBJECTPROPERTY(object_id, 'ownerid')) ,
  143.                 isupdate = OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') ,
  144.                 isdelete = OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') ,
  145.                 isinsert = OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') ,
  146.                 isafter = OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') ,
  147.                 isinsteadof = OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') ,
  148.                 trigger_schema = SCHEMA_NAME(schema_id)
  149.         FROM      sys.objects
  150.         WHERE     parent_object_id = @ObjectId
  151.                 AND type IN( 'TR', 'TA' ) ;
  152.     
  153. END
  154. ELSE IF @Sysobj_Type ='V' AND @ObjectId > 0
  155. BEGIN
  156.  
  157.     SELECT N'*********In view of the function describing information of **********'  AS N'view function description information';
  158.     
  159.     -- in view of the function description information
  160.     SELECT    ISNULL(value, N'To complete the description of the view function trouble information') AS View_Desc
  161.     FROM      sys.extended_properties
  162.     WHERE     major_id = @ObjectId
  163.         AND minor_id = 0
  164.             
  165.     SELECT '*************View basic information *****************'AS N' view basic information';
  166.     
  167.     SELECT  C.Name AS Column_Nam ,
  168.             CASE WHEN T.Name = 'nvarchar'
  169.                  THEN T.name + '(' + CAST(C.max_length / 2 AS VARCHAR) + ')'
  170.                  ELSE T.name
  171.             END AS Data_Type ,
  172.             CASE WHEN C.Max_Length = -1 THEN 'Max'
  173.                  ELSE CAST(C.Max_Length AS VARCHAR)
  174.             END AS Max_Length ,
  175.             C.Precision ,
  176.             C.Scale     ,
  177.             CASE WHEN C.is_nullable = 0 THEN '×'
  178.                  ELSE '√'
  179.             END AS Is_Nullable ,
  180.             ISNULL(CAST(I.seed_value AS VARCHAR) + '-'
  181.                    + CAST(I.increment_value AS VARCHAR), '') AS Is_Identity ,
  182.             ISNULL(M.text, '') AS Default_Value ,
  183.             ISNULL(P.value, '') AS Column_Comments
  184.     FROM    sys.columns C
  185.             INNER JOIN sys.types T ON C.system_type_id = T.user_type_id
  186.             LEFT  JOIN dbo.syscomments M ON M.id = C.default_object_id
  187.             LEFT  JOIN sys.extended_properties P ON P.major_id = C.object_id
  188.                                                     AND C.column_id = P.minor_id
  189.             LEFT  JOIN sys.identity_columns I ON I.column_id = C.column_id
  190.                                                  AND C.object_id = I.object_id
  191.     WHERE   C.[object_id] = @ObjectId
  192.     ORDER BY C.Column_Id ASC;
  193.     
  194.     SELECT '**********View script ***********'AS' view script';
  195.     
  196.     EXEC sp_helptext @ObjName;
  197.     
  198.     
  199. END
  200. ELSE IF @Sysobj_Type ='P' AND @ObjectId > 0
  201. BEGIN
  202.     SELECT N'*********Describe the stored procedure **********'  AS N'function information; describes the function of the information store process';
  203.     
  204.     - stored procedure function description information
  205.     SELECT    ISNULL(value, N'Trouble describing the stored procedure function complete information') AS View_Desc
  206.     FROM      sys.extended_properties
  207.     WHERE     major_id = @ObjectId
  208.         AND minor_id = 0;
  209.         
  210.     EXEC sp_help  @ObjName;
  211. END
  212.  
  213. ELSE IF @Sysobj_Type IN('IF') AND @ObjectId > 0
  214. BEGIN
  215.  
  216.         SELECT N'*********Describe a custom function information **********'  AS N'describe a custom function information';
  217.     
  218.         Describes the custom function information
  219.         SELECT    ISNULL(value, N'The trouble to complete the description of the custom function information') AS View_Desc
  220.         FROM      sys.extended_properties
  221.         WHERE     major_id = @ObjectId
  222.             AND minor_id = 0;
  223.         
  224.         
  225.         SELECT  'Name' = o.name ,
  226.                 'Owner' = USER_NAME(OBJECTPROPERTY(object_id, 'ownerid')) ,
  227.                 'Object_type' = SUBSTRING(v.name, 5, 31)
  228.         FROM    sys.all_objects o ,
  229.                 master.dbo.spt_values v
  230.         WHERE  o.object_id =@ObjectId AND  o.type = SUBSTRING(v.name, 1, 2) COLLATE database_default
  231.                 AND v.type = 'O9T'
  232.         ORDER BY [Owner] ASC ,
  233.                 Object_type DESC ,
  234.                 Name ASC
  235. END
  236.  
  237. GO

 

Next, we create a table to have a look the effect how, views, stored procedures, user-defined functions are not war space to show, one example is OK, interested, try

  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'Number of employees' , @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'Employee name' , @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'Gender' , @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'Department number' , @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'Salary' , @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'Length of service' , @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'Employee information form' , @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'Employee information form' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'V_Employee'       EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'To store user permissions batch Fu' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'sp_authorize_right'

Execute a stored procedure, the basic information you can get the Employee table, as shown below

image

Posted by Moses at November 21, 2013 - 8:37 PM