Set Extended Properties for Every Table
Extended properties are a neat feature in SQL Server 2000 that let you set meta data for an individual object. This stored procedure is a baseline sproc that lets you set the properties of every object in a DB to a given value. For example, if you baseline your database at 1.5.0, you can set each table to 1.5.0 as a baseline for that build.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--USPSetAllExtendedProperties 'Procedure', 'Version', '1.0.0.0'
USE MASTER
GO
CREATE Proc USPSetAllExtendedProperties
@objecttype varchar(10),
@propertyname varchar(50),
@propertyvalue varchar(7500)
as
/**************************************************************************
**Name: USPSetAllExtendedProperties
**Desc:
**Example: USPSetAllExtendedProperties 'table', 'Version', '1.5'
**
**
**Called by: DBAs
**
**Parameters:
**Input
**----------
**@objectype varchar(10) ('table', 'Procedure', 'function',
**'view' are valid values)
**
**Auth: Brian Knight
**Date: 2/28/05
***************************************************************************
**Change History
***************************************************************************
**Date:Author:Description:
**-------------------------------------------
**2/28/05Brian KnightInitial release
**************************************************************************/
DECLARE @objectshorttype char(1)
IF @objecttype = 'table'
BEGIN
SET @objectshorttype = 'U'
END
ELSE
IF @objecttype = 'Procedure'
BEGIN
SET @objectshorttype = 'P'
END
ELSE
IF @objecttype = 'view'
BEGIN
SET @objectshorttype = 'V'
END
ELSE
IF @objecttype = 'function'
BEGIN
SET @objectshorttype = 'F'
END
SET NOCOUNT ON
DECLARE extproperties CURSOR
READ_ONLY
FOR SELECT name FROM sysobjects where type=@objectshorttype and name not like 'dt_%'
DECLARE @name varchar(40)
OPEN extproperties
FETCH NEXT FROM extproperties INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF (SELECT COUNT(*) FROM ::FN_LISTEXTENDEDPROPERTY(@propertyname, 'User','dbo',@objecttype,@name, default, default)) = 0
BEGIN
EXEC sp_addextendedproperty @propertyname, @propertyvalue, 'user', dbo, @objecttype, @name
Print 'Added extended property on '+ @name
END
ELSE
BEGIN
EXEC sp_updateextendedproperty @propertyname, @propertyvalue, 'user', dbo, @objecttype, @name
Print 'Updated extended property on ' + @name
END
END
FETCH NEXT FROM extproperties INTO @name
END
CLOSE extproperties
DEALLOCATE extproperties
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO 
