Building a schema report of one's database is a requirement for version control in any businesses CM system. Often developers will use the Description extended property of MS SQL server to detail the purpose of a column. When it's time to write their schema report they usually wind up having to retype or copy and paste each of these lines since there is no detailed way on retrieving this information programmatically.
Because the extended description property of a column is not returned in the SqlClient schema queries in ADO.net (Microsoft fix this bug!) or in SQL stored procedures like sp_columns_rowset one has to resort to other means on retrieving this simple field.
After some research I found an article where a query was developed to retrieve the description field. Below is my contribution to this code that also checks to see what the SQL Server version is and executes the appropriate query for SQL 2005. Enjoy!
DECLARE @TableName varchar(100)
SELECT @TableName = 'yourtablename'
-- This will determine if we're using version 9 (2005) of SQL Server, and execute code accordingly
IF CAST(SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar),1,1) as int) >= 9BEGIN
-- This is a SQL 2005 machine
SELECT
[Table Name] = OBJECT_NAME(c.object_id),
[Column Name] = c.name,
[Description] = ex.value
FROM
sys.columns c
LEFT OUTER JOIN
sys.extended_properties ex
ON
ex.major_id = c.object_id
AND ex.minor_id = c.column_id
AND ex.name = 'MS_Description'
WHERE
OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0
AND OBJECT_NAME(c.object_id) = @TableName
ORDER
BY OBJECT_NAME(c.object_id), c.column_id
END
ELSE
BEGIN
-- assume this is a SQL 2000
SELECT
[Table Name] = i_s.TABLE_NAME,
[Column Name] = i_s.COLUMN_NAME,
[Description] = s.value
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
OBJECTPROPERTY(OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME), 'IsMsShipped')=0
AND i_s.TABLE_NAME = @TableName
ORDER BY
i_s.TABLE_NAME, i_s.ORDINAL_POSITION
END
UPDATE: I wanted to write a follow up for this as well since I came across some system provided stored procedures and functions that will provide access to this information as well and involves A LOT less typing:
First is the fn_listextendedproperty function. It will return all the extended properties for the specified object types. An example of use is below:
SELECT objname,name,value FROM fn_listextendedproperty
(NULL,'user','dbo','table','Users',NULL,NULL)
This will return the extended properties for the Users table.
Now if I wanted just the Description of the table I would add a where clause:
SELECT objname,name,value FROM fn_listextendedproperty
(NULL,'user','dbo','table','Users',NULL,NULL)
WHERE name='MS_Description'
Now it will only return the description of the table.
For a list columns and their description, we can do the following:
SELECT objname,name,value FROM fn_listextendedproperty
(NULL,'user','dbo','table','Users','column',NULL)
Or for a specific column we can specify that in the last function parameter:
SELECT objname,name,value FROM fn_listextendedproperty
(NULL,'user','dbo','table','Users','column','UserName')
Note that there are also three system provided stored procedures that also allow you to set and drop extended properties. These are sp_addextendedproperty, sp_updateextendedproperty, and sp_dropextendedproperty. Quickly, to use these we simply do the following:
exec sp_addextendedproperty 'Format', '(###)###-####',
'user','dbo','table','Users','column','Phone'
This will add a new extended property called "Format" to the column Phone in my Users table. This may be useful for me to know how to format and store the data in that column.
To update that property I can do the following:
exec sp_updateextendedproperty 'Format', '(410)###-####',
'user','dbo','table','Users','column','Phone'
Now the formatting will show that the area code should be 410.
To remove this extended property, I simply call the following command:
exec sp_dropextendedproperty 'Format',
'schema','dbo','table','Users','column','Phone'
I think these stored procedures will be a lot easier for you to utilize that the query above, so I do recommend using them before building your own from the original query.
Enjoy!
Share this post: 
|
|
|
|
|
|
