June 11, 2012 at 3:16 pm
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
June 11, 2012 at 3:28 pm
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/
June 11, 2012 at 7:34 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply