Adding extended properties to stored procedures/functions

• SQL Prog

  SSCrazy

  Points: 2020

  More actions

  June 11, 2012 at 3:16 pm

  #392489

  Hello All,

  I am trying to add 4 extended properties to the 250 stored procedures and 200 functions that doesn't have. I figured out this by using

  select so.name, t.*

  from syscomments t

  join sysobjects so on t.id = so.id

  left join sys.extended_properties p on p.major_id = t.id WHERE p.name IS NULL

  Can anyone suggest me how to add the extended properties to the stored procedures/functions in one single script.

  Please assist.

  Thank You

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  June 11, 2012 at 3:28 pm

  #1499545

  About all you can do is use your select statement you posted and tweak it a little to help build your sql.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lowell

  SSC Guru

  Points: 323526

  More actions

  June 11, 2012 at 7:34 pm

  #1499570

  you just need a basic example? here' s one writing two properties about a procedure, and reading them back;

  to do it for all of them, you build a select statment to build all the strings for you, and then execute that whopper group of statements.

  EXEC sys.sp_addextendedproperty

  @name = N'Version',

  @value = N'9.0.154.90',

  @level0type = N'SCHEMA', @level0name = 'dbo',

  @level1type = N'PROCEDURE', @level1name = 'sp_find';

  EXEC sys.sp_addextendedproperty

  @name = N'Purpose',

  @value = N'simple tool to find column or table names that are LIKE the inputed value',

  @level0type = N'SCHEMA', @level0name = 'dbo',

  @level1type = N'PROCEDURE', @level1name = 'sp_find';

  --show all extended properties

  SELECT objtype, objname, name, value

  FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);

  --get just the "Version" that i created:

  SELECT objtype, objname, name, value

  FROM fn_listextendedproperty ('Version', 'schema', 'dbo', 'PROCEDURE', 'sp_find', NULL, NULL);

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!