SHARE
TWEET

Untitled

a guest Jun 25th, 2019 47 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- ==========================================================
  2. -- Check for description existence
  3. -- ==========================================================
  4.  
  5. SELECT * FROM sys.extended_properties AS xp
  6. WHERE xp.name = 'MS_Description'
  7. -- WHERE xp.value = 'NiHao'
  8.  
  9.  
  10. -- ==========================================================
  11. -- Add or alter custom-property:
  12. -- ==========================================================
  13.  
  14.  
  15. DECLARE @v sql_variant
  16. SET @v = N'Lisa says hello ;)'
  17.  
  18. DECLARE @property_name national character varying(256)
  19. SET @property_name = N'malibu_stacy'
  20.  
  21. EXECUTE sp_addextendedproperty
  22.      @property_name
  23.     ,@v
  24.     ,N'SCHEMA'
  25.     ,N'dbo'
  26.     ,N'TABLE'
  27.     ,N'ELMAH_Error'
  28.     ,N'COLUMN'
  29.     ,N'ErrorId'
  30. ;
  31.  
  32.  
  33. EXEC sp_updateextendedproperty
  34.      @name = @property_name
  35.     ,@value = 'Your description'
  36.     ,@level0type = N'Schema'
  37.     ,@level0name = 'dbo'
  38.     ,@level1type = N'Table'
  39.     ,@level1name = 'ELMAH_Error'
  40.     ,@level2type = N'Column'
  41.     ,@level2name = 'ErrorId'
  42. ;
  43.  
  44.  
  45. -- ==========================================================
  46. -- Add description to table
  47. -- ==========================================================
  48.  
  49. DECLARE @v sql_variant
  50. SET @v = N'Noob'
  51.  
  52. EXECUTE sp_addextendedproperty
  53.      N'MS_Description'
  54.     ,@v
  55.     ,N'SCHEMA'
  56.     ,N'dbo'
  57.     ,N'TABLE'
  58.     ,N'_T_VWS_SVG'
  59.     ,NULL
  60.     ,NULL
  61. ;
  62.  
  63. -- ==========================================================
  64. -- Alter table-description
  65. -- ==========================================================
  66.  
  67. EXECUTE sp_updateextendedproperty
  68.      N'MS_Description'
  69.     ,@v
  70.     ,N'SCHEMA'
  71.     ,N'dbo'
  72.     ,N'TABLE'
  73.     ,N'ELMAH_Error'
  74.     ,NULL
  75.     ,NULL
  76. ;
  77.  
  78.  
  79. -- ==========================================================
  80. -- Add description to table-column
  81. -- ==========================================================
  82.  
  83. DECLARE @v sql_variant
  84. SET @v = N'foobar2000'
  85.  
  86. EXECUTE sp_addextendedproperty N'MS_Description'
  87.     , @v
  88.     , N'SCHEMA'
  89.     , N'dbo'
  90.     , N'TABLE'
  91.     , N'ELMAH_Error'
  92.     , N'COLUMN'
  93.     , N'ErrorId'
  94. ;
  95.  
  96. -- ==========================================================
  97. -- Alter table-column description
  98. -- ==========================================================
  99.  
  100. EXEC sp_updateextendedproperty
  101.      @name = N'MS_Description'
  102.     ,@value = 'Your description'
  103.     ,@level0type = N'Schema'
  104.     ,@level0name = 'dbo'
  105.     ,@level1type = N'Table'
  106.     ,@level1name = 'ELMAH_Error'
  107.     ,@level2type = N'Column'
  108.     ,@level2name = 'ErrorId'
  109. ;
  110.  
  111. -- ==========================================================
  112. -- Drop table-column description
  113. -- ==========================================================
  114.  
  115.  
  116. EXEC sp_dropextendedproperty    
  117.      @name = N'MS_Description'
  118.     ,@level0type = N'Schema'
  119.     ,@level0name = 'dbo'
  120.     ,@level1type = N'Table'
  121.     ,@level1name = 'ELMAH_Error'
  122.     ,@level2type = N'Column'
  123.     ,@level2name = 'ErrorId'
  124. ;
  125.  
  126.  
  127. -- ==========================================================
  128. -- Drop descriptions on tables and table-columns
  129. -- ==========================================================
  130.  
  131. SELECT
  132.     'EXEC sp_dropextendedproperty
  133.      @name = ''' + xp.name + '''
  134.     ,@level0type = ''schema''
  135.     ,@level0name = ''' + sch.name + '''
  136.     ,@level1type = ''table''
  137.     ,@level1name = ''' + syst.name + '''; '
  138. FROM sys.extended_properties AS xp
  139.  
  140. INNER JOIN sys.tables AS syst
  141.     ON syst.object_id = xp.major_id
  142.  
  143. INNER JOIN sys.schemas AS sch
  144.     ON sch.schema_id = syst.schema_id
  145.  
  146. WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
  147. AND xp.minor_id = 0
  148. AND xp.name = 'MS_Description'
  149.  
  150.  
  151. UNION ALL
  152.  
  153.  
  154. SELECT
  155.     'EXEC sp_dropextendedproperty
  156.      @name = ''' + xp.name + '''
  157.     ,@level0type = ''schema''
  158.     ,@level0name = ''' + sch.name + '''
  159.     ,@level1type = ''table''
  160.     ,@level1name = ''' + syst.name + '''
  161.     ,@level2type = ''column''
  162.     ,@level2name = ''' + sysc.name + '''; '
  163. FROM sys.extended_properties AS xp
  164.  
  165. INNER JOIN sys.tables AS syst
  166.     ON syst.object_id = xp.major_id
  167.  
  168. INNER JOIN sys.schemas AS sch
  169.     ON sch.schema_id = syst.schema_id
  170.  
  171. INNER JOIN sys.columns AS sysc
  172.     ON sysc.object_id = xp.major_id
  173.     AND sysc.column_id = xp.minor_id
  174.  
  175. WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
  176. AND xp.minor_id > 0
  177. AND xp.name = 'MS_Description'
  178.  
  179.  
  180. UNION ALL
  181.  
  182.  
  183. SELECT
  184.     'EXEC sp_dropextendedproperty
  185.      @name = ''' + xp.name + '''
  186.     ,@level0type = ''schema''
  187.     ,@level0name = ''' + sch.name + '''
  188.     ,@level1type = ''table''
  189.     ,@level1name = ''' + syst.name + '''
  190.     ,@level2type = ''column''
  191.     ,@level2name = ''' + sysc.name + '''; '
  192. FROM sys.extended_properties AS xp
  193.  
  194. INNER JOIN sys.tables AS syst
  195.     ON syst.object_id = xp.major_id
  196.  
  197. INNER JOIN sys.schemas AS sch
  198.     ON sch.schema_id = syst.schema_id
  199.  
  200. INNER JOIN sys.columns AS sysc
  201.     ON sysc.object_id = xp.major_id
  202.     AND sysc.column_id = xp.minor_id
  203.  
  204. WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
  205. AND xp.minor_id > 0
  206. AND xp.name = 'malibu_stacy'
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top