troubleshooting Question

Help creating a ton of Extended Properties

Avatar of wdbates
wdbatesFlag for United States of America asked on
Microsoft SQL ServerMicrosoft ApplicationsMicrosoft SQL Server 2008
10 Comments1 Solution454 ViewsLast Modified:
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?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros