Help creating a ton of Extended Properties

I'd generate it for all columns in one big clob, like below.
Naturally uncomment the "EXEC(@sql)" when the code looks good enough to you to run :-) .


SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRY
BEGIN TRANSACTION
DECLARE @sql nvarchar(max)
PRINT N'Creating Extended Properties for Table OnTrack'

SELECT @sql = (SELECT '
EXEC sys.sp_addextendedproperty @name = N''Caption'', ' +
    '@value = ''' + c.caption + ''', ' +
    '@level0type = N''Schema'', @level0name = ''dbo'', ' +
    '@level1type = N''Table'',  @level1name = ''OnTrack'', ' +
    '@level2type = N''Column'', @level2name = ''' + c.column_name + ''';'
    FROM dbo.captions c
    WHERE
        c.table_name = 'OnTrack' AND
        NOT EXISTS (
            SELECT 1        
            FROM ::fn_listextendedproperty(N'Caption', N'SCHEMA',N'dbo', N'TABLE',N'OnTrack', N'$column$',c.column_name )
        )
    ORDER BY c.column_name
    FOR XML PATH('')
)

PRINT @sql
--EXEC(@sql)

COMMIT TRANSACTION
PRINT 'The update to the database succeeded.'
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION
PRINT 'The update to the database failed.'
SELECT ERROR_NUMBER() AS ErrorNumber
      ,ERROR_SEVERITY() AS ErrorSeverity
      ,ERROR_STATE() AS ErrorState
      ,ERROR_PROCEDURE() AS ErrorProcedure
      ,ERROR_LINE() AS ErrorLine
      ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

I'll give it a go on Thursday and let you know.

Many thanks!

While my wife is driving I am able to look at the query and I see a problem with it.  There are multible tables in the temp table I created; Table_Name, Column_Name and Caption_Name.  I have noticed that I also needed to add and additional column Schema_Name.

Can you help me out?  I will check at the next Hot Spot.

Thanks

SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRY
BEGIN TRANSACTION
DECLARE @sql nvarchar(max)
PRINT N'Creating Extended Properties for Specified Tables'

SELECT @sql = (SELECT '
EXEC sys.sp_addextendedproperty @name = N''Caption'', ' +
    '@value = ''' + c.caption + ''', ' +
    '@level0type = N''Schema'', @level0name = ''' + c.schema_name + ''', ' +
    '@level1type = N''Table'',  @level1name = ''' + c.table_name + ''', ' +
    '@level2type = N''Column'', @level2name = ''' + c.column_name + ''';'
    FROM dbo.captions c
    WHERE
        c.table_name = 'OnTrack' AND
        NOT EXISTS (
            SELECT 1        
            FROM fn_listextendedproperty(N'Caption', N'SCHEMA',c.schema_name, N'TABLE',c.table_name, N'$column$',c.column_name )
        )
    ORDER BY c.column_name
    FOR XML PATH('')
)

I noticed the WHERE contains the condition c.table_name = 'OnTrack'.  How can I loop through all the SCHEMAS and TABLES listed in the temp table?  The temp table contains 4 columns; Schema_Name, Table_Name, Column_Name and Caption_Name.

Thanks for your help.

On to the next rest station.

Sure; you can just comment out that condition:

WHERE
        /*c.table_name = 'OnTrack' AND*/
        NOT EXISTS (
            SELECT 1        
            FROM fn_listextendedproperty(N'Caption', N'SCHEMA',c.schema_name, N'TABLE',c.table_name, N'$column$',c.column_name )

I receive this error:

ErrorNumber      ErrorSeverity      ErrorState      ErrorProcedure      ErrorLine      ErrorMessage
102      15      1      NULL      1      Incorrect syntax near '&'.

My code:

SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRY
BEGIN TRANSACTION
DECLARE @sql nvarchar(max)
PRINT N'Creating Extended Properties for Specified Tables'

SELECT @sql = (SELECT '
EXEC sys.sp_addextendedproperty
       @name       = N''Caption'', ' + '@value      = ''' + [c].[Caption_Name] + ''', ' +
    '@level0type = N''Schema'',       @level0name = ''' + [c].[schema_name] + ''', ' +
    '@level1type = N''Table'',        @level1name = ''' + [c].[table_name] + ''', ' +
    '@level2type = N''Column'',       @level2name = ''' + [c].[column_name] + ''';'
    FROM [dbo].[Extended_P] c
    WHERE
        --c.table_name = 'OnTrack' AND
        NOT EXISTS (
            SELECT 1        
            FROM fn_listextendedproperty(N'Caption', N'SCHEMA',c.schema_name, N'TABLE',c.table_name, N'$column$',c.column_name )
        )
    ORDER BY [c].[Schema_Name], [c].[Table_Name], [c].[Column_Name]
    FOR XML PATH('')
)
PRINT @sql
EXEC(@sql)

COMMIT TRANSACTION
PRINT 'The update to the database succeeded.'
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION
PRINT 'The update to the database failed.'
SELECT ERROR_NUMBER() AS ErrorNumber
      ,ERROR_SEVERITY() AS ErrorSeverity
      ,ERROR_STATE() AS ErrorState
      ,ERROR_PROCEDURE() AS ErrorProcedure
      ,ERROR_LINE() AS ErrorLine
      ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Output from execution:

Creating Extended Properties for Specified Tables
&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Computed Column based on Complete Date.  If Complete Date is null, then Complete is 0.  If Complete Date is not null, then Complete is 1.  ', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'Completed';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Date the student completed the learning track.  API functions automatically update this column during processing.  ', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'CompleteDate';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'The sequence of the attempt for the trainee taking the learning track.  This value is defaulted to 1 and incremented each time the trainee is re-enrolled in the learning track.', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'CourseAttempt';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Unique Identifier assigned by the database', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'EnrollmentID';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Determines which TMS item number will be completed in TMS.  Computed Column that is automatically updated by the API functions.', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'EstTimeToComplete';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'TMS Item number from the LearningTrack table', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'ItemID';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Date the student last accessed the learning track.  TDMSS launch process updates this column.', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'LastAccessDate';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Level Identifier from the LearningTrack table', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'LevelID';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Learning Track Identifier from the LearningTrack table', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'LTID';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Student’s main bookmark for the learning track which is used when the student re-launches the learning track.  Updated when the student exits the learning track. ', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'MainBookMark';&#x0D;
EXEC sys.sp_addextendedproperty &#x0D;
       @name       = N'Caption', @value      = 'Auxiliary field that can be used for course-specific information.', @level0type = N'Schema',       @level0name = 'sCDSDATA', @level1type = N'Table',        @level1name = 'Enrollments', @level2type = N'Column',       @level2name = 'MenuFla
The update to the database failed.

(1 row(s) affected)

Thank you for the help!

You're welcome!

Btw, the PRINT statement will only print a certain number of bytes, so the output may be truncated; but all the full commands will still be in the string and run when the EXEC() is done.

If you want, you could a loop to SELECT the SQL statement in chunks so you could see it all prior to EXECution.