Step 1: Create a table
CREATE TABLE [dbo].[AppUser](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
[Password] [nvarchar](47) COLLATE Latin1_General_CI_AS NOT NULL,
[EMail] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_AppUser] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Step 2: Adding meta data using DDL
//Adding metadata to the column ‘Id’
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’User”s unique identifier’ ,@level0type=N’SCHEMA’,
@level0name=N’dbo’, @level1type=N’TABLE’, @level1name=N’AppUser’, @level2type=N’COLUMN’, @level2name=N’Id’
GO
//Adding metadata to the column ‘Name’
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’User”s login name’ ,@level0type=N’SCHEMA’,
@level0name=N’dbo’, @level1type=N’TABLE’, @level1name=N’AppUser’, @level2type=N’COLUMN’, @level2name=N’Name’
GO
// Adding metadata to the column ‘Password’
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’this field contains hash value of the user”s password’ ,
@level0type=N’SCHEMA’, @level0name=N’dbo’, @level1type=N’TABLE’, @level1name=N’AppUser’, @level2type=N’COLUMN’, @level2name=N’Password’
GO
//Adding metadata to the column ‘Email’
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’User”s email’ ,@level0type=N’SCHEMA’,
@level0name=N’dbo’, @level1type=N’TABLE’, @level1name=N’AppUser’, @level2type=N’COLUMN’, @level2name=N’EMail’
GO
//Adding metadata to the table ‘AppUser’
EXEC sys.sp_addextendedproperty @name=N’MS_Description’, @value=N’This table contains application users’ ,
@level0type=N’SCHEMA’, @level0name=N’dbo’, @level1type=N’TABLE’, @level1name=N’AppUser’
Note : sys.sp_addextendedproperty procedure is system stored proc which adds the extending info to Db objects based on provided heirarchy.
Here @level_ _ desides the heirarchy of the db objects.
We have following system stored procs to manage Extended propeties.
sys.sp_addextendedproperty
sys.sp_updateextendedproperty
sys.sp_dropextendedproperty
Step 3: Retrieving the extra meta info
SELECT OBJECT_NAME(EXP.major_id) AS TableName,
C.name AS ColumnName,
EXP.name AS PropertyName,
EXP.value AS PropertyValue
FROM sys.extended_properties AS EXP
LEFT OUTER JOIN sys.columns AS C
ON C.object_id = EXP.major_id
AND C.column_id = EXP.minor_id
WHERE EXP.class_desc = ‘OBJECT_OR_COLUMN’
AND OBJECT_NAME(EXP.major_id) LIKE ‘AppUser’
We extend the metadata for not only tables/columns but also for most of the DB objects.