Solved

Help creating a ton of Extended Properties

Posted on 2013-12-23
10
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 69

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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 69

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 69

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 69

Accepted Solution

by:
Scott Pletcher earned 400 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 69

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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