Generate script for extended properties

,

The stored procedure generates insert script for table and column extended properties in the current database. The generated script checks whether a property exists, if it exists, drop it first. The stored procedure assumes table owner is dbo. It can be easily changed to other users.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_SYS_GenerateExtendedProperties]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_SYS_GenerateExtendedProperties]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



-- exec dbo.usp_SYS_GenerateExtendedProperties

CREATE  PROCEDURE dbo.usp_SYS_GenerateExtendedProperties 
AS
-- The procedure generates extended proeprty insertion statements for tables and columns in the current database
-- Created: phe 2005-05-01

SET XACT_ABORT ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON
DECLARE @Tables TABLE (objid int NOT NULL, tblName nvarchar(255) NOT NULL PRIMARY KEY clustered (objid))
DECLARE @Columns TABLE (colid int NOT NULL, colName nvarchar(255) NOT NULL PRIMARY KEY clustered (colid))
DECLARE @Properties TABLE (PID int identity, Pname nvarchar(255) NOT NULL, Value nvarchar(1000) NOT NULL)
INSERT @Tables (objid,tblName) SELECT ID,name FROM sysobjects WHERE type='U' ORDER BY id

DECLARE @objid int
,	@tblName nvarchar(255)
,	@colName nvarchar(255)
,	@value nvarchar(1000)
,	@colID int
,	@Pname nvarchar(255)
,	@PID int

PRINT 'PRINT ''Generats script for extended properties'''+nchar(13)+'GO'+nchar(13)
PRINT 'SET NOCOUNT ON'
PRINT 'SET QUOTED_IDENTIFIER OFF'+nchar(13)+'GO'

SELECT @objid=MIN(objid) FROM @Tables
WHILE @objid is not NULL
BEGIN
	SELECT @tblName=tblName FROM @Tables WHERE objid=@objID
	DELETE FROM @Columns
	INSERT @Columns (colid, colName) SELECT colid, name from syscolumns where ID=@objid
	-- Script table properties
	DELETE FROM @Properties
	INSERT @Properties (Pname,Value) SELECT name,cast(value as nvarchar(1000)) FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @tblName, default, default)
	IF EXISTS(SELECT * FROM @Properties)
	BEGIN
		SELECT @PID=MIN(PID) FROM @Properties
		WHILE @PID IS NOT NULL
		BEGIN
			SELECT @Value=value,@Pname=Pname FROM @Properties WHERE PID=@PID
			SELECT @Pname=REPLACE(@Pname,'''',''''''),@Value=REPLACE(@Value,'''','''''')
			PRINT '
IF EXISTS(SELECT * FROM  ::fn_listextendedproperty ('''+@Pname+''', ''user'', ''dbo'', ''table'', '''+@tblName+''', default, default))
BEGIN
	exec sp_dropextendedproperty '''+@Pname+''', ''user'', ''dbo'', ''table'', '''+@tblName+''', default, default
END
exec sp_addextendedproperty '''+@Pname+''','''+@Value+''',''user'', ''dbo'', ''table'', '''+@tblName+''', default, default
GO'+nchar(13)
			SELECT @PID=MIN(PID) FROM @Properties WHERE PID>@PID
		END
	END
	-- Script column properties
	SELECT @colid=MIN(colid) FROM @Columns
	WHILE @colID IS NOT NULL
	BEGIN
		SELECT @ColName=ColName FROM @Columns WHERE colid=@colID
		DELETE FROM @Properties
		INSERT @Properties (Pname,Value) SELECT name,cast(value as nvarchar(1000)) FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', @tblName, 'column', @ColName)
		IF EXISTS(SELECT * FROM @Properties)
		BEGIN
			SELECT @PID=MIN(PID) FROM @Properties
			WHILE @PID IS NOT NULL
			BEGIN
				SELECT @Value=value,@Pname=Pname FROM @Properties WHERE PID=@PID
				SELECT @Pname=REPLACE(@Pname,'''',''''''),@Value=REPLACE(@Value,'''','''''')
				PRINT '
IF EXISTS(SELECT * FROM  ::fn_listextendedproperty ('''+@Pname+''', ''user'', ''dbo'', ''table'', '''+@tblName+''', ''column'', '''+@ColName+'''))
BEGIN
	exec sp_dropextendedproperty '''+@Pname+''', ''user'', ''dbo'', ''table'', '''+@tblName+''', ''column'', '''+@ColName+'''
END
exec sp_addextendedproperty '''+@Pname+''', '''+@Value+''',''user'', ''dbo'', ''table'', '''+@tblName+''', ''column'', '''+@ColName+'''
GO'+nchar(13)
				SELECT @PID=MIN(PID) FROM @Properties WHERE PID>@PID
			END
		END
		SELECT @colid=MIN(colid) FROM @Columns WHERE colid>@colid
	END
	SELECT @objid=MIN(objid) FROM @Tables WHERE objid>@objid
END





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

5 (1)

Share

Share

Rate

5 (1)