Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help creating a ton of Extended Properties

Posted on 2013-12-23
10
Medium Priority
?
388 Views
Last Modified: 2013-12-26
Hello all in ExpertExchange;
I need to add Extended Properties to hundreds of columns for many tables and would like to create a script of some kind.  I have a table with 3 columns, Table_Name, Column_Name and Caption_Name and would like the output to look something like this:

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
GO

PRINT N'Creating Extended Properties for Table OnTrack'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'Caption' , N'SCHEMA',N'dbo', N'TABLE',N'OnTrack', N'COLUMN',N'ON_TRACK_ID'))
EXEC sys.sp_addextendedproperty @name = N'Caption',
                            @value = 'Database Identifier column.',
                            @level0type = N'Schema', @level0name = 'dbo',
                            @level1type = N'Table',  @level1name = 'OnTrack',
                              @level2type = N'Column', @level2name = 'ON_TRACK_ID';
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
.
.
.
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The update to the database was succeeded.'
COMMIT TRANSACTION
END
ELSE PRINT 'The update to the database failed.'
GO
DROP TABLE #tmpErrors
GO

Would this be a good place for a cursor?
0
Comment
Question by:wdbates
  • 5
  • 5
10 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39736901
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
0
 

Author Comment

by:wdbates
ID: 39737941
I'll give it a go on Thursday and let you know.

Many thanks!
0
 

Author Comment

by:wdbates
ID: 39738252
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
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39738381
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('')
)
0
 

Author Comment

by:wdbates
ID: 39738477
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.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39738492
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 )
0
 

Author Comment

by:wdbates
ID: 39738556
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)
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1600 total points
ID: 39738560
Sorry, I left one "hard" CR in there, which XML doesn't like.

...
PRINT N'Creating Extended Properties for Specified Tables'

SELECT @sql = (SELECT '  EXEC sys.sp_addextendedproperty ' +
       '@name       = N''Caption'', ' + '@value      = ''' + [c].[Caption_Name] + ''', ' +

...
0
 

Author Closing Comment

by:wdbates
ID: 39740206
Thank you for the help!
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39740246
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question