Extended properties

SQL server supports extented properties for tables and columns. The properties can be used to describe the table/column, and to generate database document.

You can easily insert property value for the default property "MS_Description" in Enterprise Manager. But there is a bug in the Enterprise Manager that you can only input 255chars atmost. Infact, the property value can be as long as 7500bytes. However you can insert the property by running the SP: sp_addextendedproperty, please check BOL for details.

For example, the following script inserts value of the default property "MS_Description" for table "Categories" and its column "CategoryName"

use Northwind

GO

exec sp_addextendedproperty ‘MS_Description’, ‘This table defines product categories’,’user’, ‘dbo’, ‘table’, ‘Categories’, default, default
GO
exec sp_addextendedproperty ‘MS_Description’, ‘It defines the name of the category’,’user’, ‘dbo’, ‘table’, ‘Categories’, ‘column’, ‘CategoryName’
GO

You can define your own property as well, e.g. to define a property of "Comment" to the table "Categories":

exec sp_addextendedproperty ‘Comment’, ‘This table table will not be supported in next release.’,’user’, ‘dbo’, ‘table’, ‘Categories’, default, default
GO

To list the existing properties and values, call the function ::fn_listextendedproperty. For example, to list the properties defined on the table "Categories":

SELECT name AS PropertyName,cast(value as nvarchar(3750)) as PropertyValue FROM ::fn_listextendedproperty (NULL, ‘user’, ‘dbo’, ‘table’, ‘Categories’, default, default)

When you generate table script in Enterprise Manager, there is one option "Include exetended properties" in the formatting tab.

Sometimes you just need to generate all extended properties without schema. Enterprise manager cannot do this. Another drawback is that the generated script does not check a property exist or not in the current database. So I created a SP to do this work. It generates the "property inserting TSQL statement" for all properties of all tables and columns in the current database.

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

 

CREATE  PROCEDURE usp_SYS_GenerateExtendedProperties
AS
–$ The procedure generates extended proeprty insertion statements for tables
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

 

Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s