[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Help creating a ton of Extended Properties

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
wdbates
Asked:
wdbates
  • 5
  • 5
1 Solution
 
Scott PletcherSenior DBACommented:
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
 
wdbatesAuthor Commented:
I'll give it a go on Thursday and let you know.

Many thanks!
0
 
wdbatesAuthor Commented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
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
 
wdbatesAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
wdbatesAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
wdbatesAuthor Commented:
Thank you for the help!
0
 
Scott PletcherSenior DBACommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now