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
