Solved

Help creating a ton of Extended Properties

Posted on 2013-12-23
10
357 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 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
I'll give it a go on Thursday and let you know.

Many thanks!
0
 

Author Comment

by:wdbates
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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:
ScottPletcher earned 400 total points
Comment Utility
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
Comment Utility
Thank you for the help!
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now