June 14, 2012 at 2:17 pm
Hello All,
I am newbie to sql server programming. I have a script that will add the extended properties and its values for stored procedure/function.I need to twaek this to get results as if the extended properties already exists for a certain stored procedure/function and if the user executes the below script it should add only the values for the extended properties as the properties are already defined.If they are not present for certain object then it should add the properties and the values.
Here is my script
ALTER PROCEDURE [dbo].[TestAddExtendedProperties]
(
@ObjType varchar(25), @ObjName varchar(75),@ObjOwner varchar(35) = NULL, @AppgrpValue varchar(35),@AuthorValue varchar(20),
@DescriptionValue varchar(70),@ReturnsValue varchar(30)
)
AS
BEGIN TRY
BEGIN TRAN
EXEC sys.sp_addextendedproperty
@name='ApplicationGroup', -- Name of the new property
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)@level1name=@ObjName --SP/FN Name
EXEC sys.sp_addextendedproperty
@name='Author', -- Name of the new property
@value=@AuthorValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
EXEC sys.sp_addextendedproperty
@name='Description', -- Name of the new property
@value=@DescriptionValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
EXEC sys.sp_addextendedproperty
@name='Returns', -- Name of the new property
@value=@ReturnsValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType, -- Object Type (Procedure, Function)
@level1name=@ObjName --SP/FN Name
COMMIT TRAN
SELECT 'Extended properties added successfully to [' + @ObjOwner + '].' + @ObjNameas [Message]
END TRY
BEGIN CATCH
ROLLBACK TRAN
EXEC site.dbo.GetErrorInfo
END CATCH
Any suggestions please.
Thank You
June 14, 2012 at 2:47 pm
if it exists at all, even with old values?
this example would use IF EXISTS (i think!) to check if it's already there:
IF NOT EXISTS(SELECT 1
FROM fn_listextendedproperty ('ApplicationGroup',
@AppgrpValue,
'SCHEMA',
@ObjOwner,
@ObjType,
NULL,
NULL) )
EXEC sys.sp_addextendedproperty
@name='ApplicationGroup', -- Name of the new property
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType -- Object Type (Procedure, Function)@level1name=@ObjName --SP/FN Name
Lowell
June 14, 2012 at 2:58 pm
Right now we have some stored procedures and functions with properties but no values.The problem is suppose I am executing the above mentioned stored procedure to add the values for the properties to one of the stored procedure which has extended properties defined but no values like this
EXEC TestAddExtendedProperties 'Procedure', 'RefreshData','dbo','Database API','AGubba','Procedure used for Nightly refresh','None'
When I execute this ..My result will be The property 'Application Group' Already exists and can't add.
Also in future if anyone creates a stored procedure/function they also need to execute the above mentioned script with values so that for the newly created stored procedure they can add the extended properties with the values.
Bottom line is first I need to check whether the object has extended properties or not..if it has properties but no values then it should add values..If it doesn't have properties and values then it should add both.
Hope it is clear. Sorry if I confused you.
Thank you
June 14, 2012 at 7:36 pm
If you have all the descriptions ready so you can script them; I would drop if exists and re add themback no matter what forget if the values are there with the right or wrong descriptions just add them.
Lowell
June 17, 2012 at 8:32 pm
There are no descriptions right now. If the user wants to add the values to the extended properties (if already there but no values) to existing stored procedure/function then they should be able to execute just the above mentioned extended properties stored procedure to add the values. If the users create any new stored procedure/function then they should execute the same extended properties stored procedure to add both the extended properties and the values.
The bottom line is the extended properties stored procedure first should check whether the object has extended properties with values or not.If they dont have the values defined but there are properties then the users should be able to add the values with this extended properties stored procedure.If the object (SP/Fn) doesn't have both extended properties and the values then the users should be able to add these both with above mentioned extended properties stored procedure.
Please help me with any suggestions.
Thank You
June 17, 2012 at 9:15 pm
The script you provided
IF NOT EXISTS(SELECT 1
FROM fn_listextendedproperty ('ApplicationGroup',
@AppgrpValue,
'SCHEMA',
@ObjOwner,
@ObjType,
NULL,
NULL) )
EXEC sys.sp_addextendedproperty
@name='ApplicationGroup', -- Name of the new property
@value=@AppgrpValue , -- Value of the new property
@level0type=N'SCHEMA',
@level0name=@ObjOwner, --Schema Name
@level1type=@ObjType -- Object Type (Procedure, Function)@level1name=@ObjName --SP/FN N
will help me when the object doesn't have the properties.But it will skip if the properties exists but no values.I want something if the properties exists, this procedure should help users to add the values.
June 19, 2012 at 10:08 am
Here is what I use. It isn't pretty, but it gets the job done. Just set the context to the desired database and run this.
You can change the name of the extended property to use for each, just change the code.
/* ==================================================================================================================== */
/* Create Object Documentation Settings */
/* By: VikingDBA 05/13/2012 */
/* ==================================================================================================================== */
DECLARE @spstatint
DECLARE @errmsgvarchar(200)
DECLARE @recnint
DECLARE @numrecs int
DECLARE @SNvarchar(50)
DECLARE @TN varchar(50)
DECLARE @tt varchar(100)
DECLARE @de varchar(2000)
DECLARE @de2 varchar(2000)
DECLARE @currsnvarchar(50)
DECLARE @currtnvarchar(50)
DECLARE @ev varchar(1000)
DECLARE @servname varchar(128)
DECLARE @dbname varchar(128)
DECLARE @currdt varchar(30)
DECLARE @currdtlen int
DECLARE @servnamelen int
DECLARE @dbnamelen int
DECLARE @linelen int
DECLARE @typetousevarchar(128)
DECLARE @includeviewsbit
DECLARE @includeTablesbit
DECLARE @includeFunctionsbit
DECLARE @includeSPROCsbit
SET NOCOUNT ON
/* ======================================================================================= */
/* User settings */
SET @includeTables = 1
SET @includeviews = 1-- whether to include views
SET @includeFunctions = 1
SET @includeSPROCs = 1
/* ======================================================================================= */
SET @linelen = 130
SET @spstat = 1 -- go ahead and set to ok
SET @errmsg = '' -- go ahead and set to ok
SET @recn = 0 -- go ahead and set to ok
BEGIN TRY
/* ======================================================================================================== */
SELECT @currdt = CONVERT(varchar(30),GETDATE(),113)
SELECT @servname=@@servername
SELECT TOP 1 @dbname=TABLE_CATALOG FROM INFORMATION_SCHEMA.COLUMNS
SET @currdtlen = LEN(@currdt)
SET @servnamelen = LEN(@servname)
SET @dbnamelen = LEN(@dbname)
PRINT '/* ' + REPLICATE('=',@linelen) + ' */'
PRINT '/* ' + REPLICATE(' ',20) + 'Object Documentation for Server: ' + @servname + REPLICATE(' ',15) + 'Database: ' + @dbname + REPLICATE(' ',@linelen-20-33-@servnamelen-15-11-@dbnamelen) + ' */'
PRINT '/* ' + REPLICATE(' ',20) + 'For Date: ' + @currdt + REPLICATE(' ',@linelen-20-11-@currdtlen) + ' */'
PRINT '/* ' + REPLICATE('=',@linelen) + ' */'
PRINT ''
PRINT ''
PRINT '/* ' + REPLICATE(' ',30) + 'Object Definitions' + REPLICATE(' ',30) + ' */'
PRINT ' '
PRINT ' '
/* =============================================================================================================================== */
/* TABLES */
if @includeTables = 1
BEGIN
SELECT TABLE_SCHEMA AS SchemaName, TABLE_NAME AS TableName, CASE TABLE_TYPE WHEN 'BASE TABLE' THEN CONVERT(varchar(100),'TABLE')
WHEN 'VIEW' THEN CONVERT(varchar(100),'(VIEW)')
END as TableType,
CONVERT(varchar(2000),'') as Comment
INTO #tabledoc2
FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW'
ORDER BY TABLE_NAME
UPDATE #tabledoc2
SET Comment = ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('TABLEDOC',
'Schema', [SchemaName],
'Table', [TableName],
NULL,NULL)),'')
WHERE TableType <> '(VIEW)'
UPDATE #tabledoc2
SET Comment = ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('TABLEDOC',
'Schema', [SchemaName],
'View', [TableName],
NULL,NULL)),'')
WHERE TableType = '(VIEW)'
if @includeviews = 0
DELETE FROM #tabledoc2 WHERE TableType='(VIEW)'
DECLARE backupFiles CURSOR FOR
SELECT SchemaName, TableName, TableType, Comment
FROM #tabledoc2
ORDER BY SchemaName, TableName
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @SN, @TN, @tt, @de
--set the initial values so will print the heading
SET @currsn = ''
SET @currtn = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @de2 = REPLACE(@de,'''','''''')
--do the heading for a new table
if @SN <> @currsn or @TN <> @currtn
BEGIN
PRINT '/* ================================================================================================================================ */'
PRINT '/* Documentation for Schema: ' + @SN + ' Table: ' + @TN + ' Table Type: ' + @tt + ' */'
PRINT ''
if @SN <> @currsn
SET @currsn = @SN
if @TN <> @currtn
SET @currtn = @TN
END
PRINT ''
if @tt = '(VIEW)'
BEGIN
if (@de = '') or (@de IS NULL)
BEGIN
PRINT '--Setting the description of the table (how is it used, what data does it hold)'
PRINT 'EXEC sp_addextendedproperty @name = ''TABLEDOC'', @value = '''','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''View'', @level1name = ''' + @TN + ''''
PRINT 'GO'
END
else
BEGIN
PRINT '--Setting the description of the table (how is it used, what data does it hold) '
PRINT 'EXEC sp_updateextendedproperty @name = ''TABLEDOC'', @value = ''' + @de2 + ''','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''View'', @level1name = ''' + @TN + ''''
PRINT 'GO'
END
END
else
BEGIN
if (@de = '') or (@de IS NULL)
BEGIN
PRINT '--Setting the description of the table (how is it used, what data does it hold)'
PRINT 'EXEC sp_addextendedproperty @name = ''TABLEDOC'', @value = '''','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Table'', @level1name = ''' + @TN + ''''
PRINT 'GO'
END
else
BEGIN
PRINT '--Setting the description of the table (how is it used, what data does it hold) '
PRINT 'EXEC sp_updateextendedproperty @name = ''TABLEDOC'', @value = ''' + @de2 + ''','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Table'', @level1name = ''' + @TN + ''''
PRINT 'GO'
END
END
FETCH NEXT FROM backupFiles INTO @SN, @TN, @tt, @de
END
CLOSE backupFiles
DEALLOCATE backupFiles
DROP TABLE #tabledoc2
END
/* =============================================================================================================================== */
/* SPROCs */
if @includeSPROCs = 1
BEGIN
SELECT
'Procedure' AS PropertyType
,SCH.name AS SchemaName
,PRC.name AS ProcedureName
,CONVERT(varchar(2000),'') AS Comment
INTO #tabledoc3
FROM sys.procedures PRC
INNER JOIN sys.schemas SCH
ON PRC.schema_id = SCH.schema_id
WHERE PRC.is_ms_shipped = 0
ORDER BY SCH.name, PRC.name
UPDATE #tabledoc3
SET Comment = ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('PROCDOC',
'Schema', [SchemaName],
'Procedure', [ProcedureName],
NULL,NULL)),'')
DECLARE backupFiles CURSOR FOR
SELECT SchemaName, ProcedureName, Comment
FROM #tabledoc3
ORDER BY SchemaName, ProcedureName
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @SN, @TN, @de
--set the initial values so will print the heading
SET @currsn = ''
SET @currtn = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @de2 = REPLACE(@de,'''','''''')
--do the heading for a new table
if @SN <> @currsn or @TN <> @currtn
BEGIN
PRINT '/* ================================================================================================================================ */'
PRINT '/* Documentation for Schema: ' + @SN + ' Procedure: ' + @TN + ' */'
PRINT ''
if @SN <> @currsn
SET @currsn = @SN
if @TN <> @currtn
SET @currtn = @TN
END
PRINT ''
if (@de = '') or (@de IS NULL)
BEGIN
PRINT '--Setting the description of the SPROC (how is it used, purpose, etc.)'
PRINT 'EXEC sp_addextendedproperty @name = ''PROCDOC'', @value = '''','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Procedure'', @level1name = ''' + @TN + ''''
PRINT 'GO'
END
else
BEGIN
PRINT '--Setting the description of the SPROC (how is it used, purpose, etc.) '
PRINT 'EXEC sp_updateextendedproperty @name = ''PROCDOC'', @value = ''' + @de2 + ''','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Procedure'', @level1name = ''' + @TN + ''''
PRINT 'GO'
END
FETCH NEXT FROM backupFiles INTO @SN, @TN, @de
END
CLOSE backupFiles
DEALLOCATE backupFiles
DROP TABLE #tabledoc3
END
/* =============================================================================================================================== */
/* Function */
if @includeFunctions = 1
BEGIN
SELECT
'Function' AS PropertyType
,SCH.name AS SchemaName
,SOB.name AS FunctionName
,CONVERT(varchar(2000),'') AS Comment
INTO #tabledoc4
FROM sys.objects SOB
JOIN sys.schemas SCH
ON SOB.schema_id = SCH.schema_id
WHERE SOB.type_desc LIKE N'%FUNCTION%'
AND SOB.is_ms_shipped = 0
ORDER BY SchemaName, FunctionName
UPDATE #tabledoc4
SET Comment = ISNULL((SELECT CONVERT(varchar(2000),value)
FROM fn_listExtendedProperty('FUNCDOC',
'Schema', [SchemaName],
'Function', [FunctionName],
NULL,NULL)),'')
DECLARE backupFiles CURSOR FOR
SELECT SchemaName, FunctionName, Comment
FROM #tabledoc4
ORDER BY SchemaName, FunctionName
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @SN, @TN, @de
--set the initial values so will print the heading
SET @currsn = ''
SET @currtn = ''
WHILE @@FETCH_STATUS = 0
BEGIN
SET @de2 = REPLACE(@de,'''','''''')
--do the heading for a new table
if @SN <> @currsn or @TN <> @currtn
BEGIN
PRINT '/* ================================================================================================================================ */'
PRINT '/* Documentation for Schema: ' + @SN + ' Function: ' + @TN + ' */'
PRINT ''
if @SN <> @currsn
SET @currsn = @SN
if @TN <> @currtn
SET @currtn = @TN
END
PRINT ''
if (@de = '') or (@de IS NULL)
BEGIN
PRINT '--Setting the description of the Function (how is it used, purpose, etc.)'
PRINT 'EXEC sp_addextendedproperty @name = ''FUNCDOC'', @value = '''','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Function'', @level1name = ''' + @TN + ''''
PRINT 'GO'
END
else
BEGIN
PRINT '--Setting the description of the Function (how is it used, purpose, etc.) '
PRINT 'EXEC sp_updateextendedproperty @name = ''FUNCDOC'', @value = ''' + @de2 + ''','
PRINT '@level0type = ''Schema'', @level0name = ''' + @SN + ''','
PRINT '@level1type = ''Function'', @level1name = ''' + @TN + ''''
PRINT 'GO'
END
FETCH NEXT FROM backupFiles INTO @SN, @TN, @de
END
CLOSE backupFiles
DEALLOCATE backupFiles
DROP TABLE #tabledoc4
END
/* ======================================================================================================== */
SET @numrecs = @@rowcount
if @numrecs=0
BEGIN
SET @spstat = -1
SET @errmsg = 'No record selected'
SET @recn = 0
END
END TRY
BEGIN CATCH
DECLARE@ErrorNoint,
@Severityint,
@Stateint,
@LineNoint,
@Messagevarchar(1000)
SELECT@ErrorNo = ERROR_NUMBER(),
@Severity = ERROR_SEVERITY(),
@State = ERROR_STATE(),
@LineNo = ERROR_LINE(),
@Message = ERROR_MESSAGE()
SET @errmsg = CONVERT(varchar(200), @Message)
SET @spstat = 0
PRINT 'Error occured: ' + @Message
END CATCH
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply