获得表中字段说明
SELECT
ID = newid(),
字段名= convert(varchar(100), a.name),
表名= convert(varchar(50), d.name ),
库名= 'WEPM_OA',
字段说明=convert(varchar(50), isnull(g.[value],'')),
字段说明=convert(varchar(50), isnull(g.[value],''))
FROM dbo.syscolumns a
left join dbo.systypes b on a.xusertype=b.xusertype
inner join dbo.sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join dbo.syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
where d.name ='XsChapter'
-------------------------------
SQL Server表描述信息
drop table test
--创建表及描述信息
create table test(a1 varchar(10),a2 char(2))
--为表添加描述信息
EXECUTE sp_addextendedproperty N'MS_Description', '人员信息表', N'user', N'dbo', N'table', N'test', NULL, NULL
--为字段a1添加描述信息
EXECUTE sp_addextendedproperty N'MS_Description', '姓名 ', N'user', N'dbo', N'table', N'test', N'column', N'a1'
--为字段a2添加描述信息
EXECUTE sp_addextendedproperty N'MS_Description', '性别 ', N'user', N'dbo', N'table', N'test', N'column', N'a2'
--更新表中列a1的描述属性：
EXEC sp_updateextendedproperty N'MS_Description','字段2', N'user', N'dbo', N'table', N'test', N'column', N'a1'
--删除表中列a1的描述属性：
EXEC sp_dropextendedproperty 'MS_Description','user','dbo','table','test','column',a1
select * from test
select * from sys.extended_properties where major_id=OBJECT_ID('test')
添加扩展属性后，sql server studio要把数据库收起来，刷新后，再通过“对象资源管理器”，“数据库”，“具体的数据库”“表”，“具体的表”的属性（或者“具体的列”的属性）来查看才能看得到。
SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '表名', 'column', default)
select * from sys.all_objects
SELECT * FROM SYS.fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', null, null, null)
--查询表列描述
select col.Name,msdesc.value,col.colid from
(select lower(name) as Name,colid from syscolumns where id=object_id('table_name')) col
left join (select minor_id,value from sys.extended_properties
where major_id=object_id('table_name') and minor_id>0) msdesc on col.colid=msdesc.minor_id
order by col.colid
生成存储过程表字段参数：
declare @table varchar(100)
set @table = 'com_baseinfo'--表名字
select '@i_'+col.name+' '+st.name+(case when st.name in ('decimal','numeric','varbinary','varchar','binary','char','nvarchar','nchar')
then '('+convert(varchar(10),(case when st.name='nchar' or st.name='nvarchar' then col.[length]/2 when st.name='decimal' or st.name='decimal' then col.xprec else col.[length] end))
+(case when st.name='decimal' or st.name='decimal' then ','+convert(varchar(10),col.scale) else '' end)+')' else '' end)+' = '+(case when st.name='bit' or st.name in ('int','smallint','bigint','tinyint') then '0' else 'null' end)+','
+(case when cp.[value] is null then '' else '--'+convert(nvarchar(1000),cp.value) end)
from
(select * from syscolumns where id = object_id(@table)) col
left join fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @table, 'column', default) cp on col.name=cp.objname COLLATE Chinese_PRC_CI_AS
inner join systypes st on col.xusertype=st.xusertype
--生成程序调用存储过程参数
declare @table varchar(100),@entity varchar(50)
select @table = 'Com_BaseInfo',@entity='baseinfo'
SELECT ' '+PARA FROM(
SELECT IDX,replace(para,'${INDEX}',CONVERT(VARCHAR(10),IDX-1)) AS PARA FROM
(select row_number() over(order by col.colid asc) as IDX,'new SqlParameter("@i_'+col.name+'", '+map.code+(case when st.name in ('varbinary','varchar','binary','char','nvarchar','nchar')
then ', '+convert(varchar(10),(case when st.name='nchar' or st.name='nvarchar' then col.[length]/2 else col.[length] end)) else '' end)
+') { Value = '+@entity+'[${INDEX}]'
+(case when st.name in ('numeric','decimal') then ',Precision='+convert(varchar(10),col.xprec)+',Scale='+convert(varchar(10),col.xscale) else '' end)+'},'
+(case when cp.Value is null then '' else '//'+convert(nvarchar(1000),cp.value) end) as para
from
(select * from syscolumns where id = object_id(@table) and name not in ('Contactor','CompanyName')) col
left join fn_listextendedproperty(NULL, 'user', 'dbo', 'table', @table, 'column', default) cp
on col.name=cp.objname COLLATE Chinese_PRC_CI_AS
inner join systypes st on col.xusertype=st.xusertype
inner join (select 'bigint' as xtype,'SqlDbType.BigInt' as code union
select 'binary','SqlDbType.Binary' union
select 'bit','SqlDbType.Bit' union
select 'char','SqlDbType.Char' union
select 'datetime','SqlDbType.DateTime' union
select 'decimal','SqlDbType.Decimal' union
select 'float','SqlDbType.Float' union
select 'image','SqlDbType.Image' union
select 'int','SqlDbType.Int' union
select 'money','SqlDbType.Money' union
select 'nchar','SqlDbType.NChar' union
select 'ntext','SqlDbType.NText' union
select 'numeric','SqlDbType.Real' union
select 'nvarchar','SqlDbType.NVarChar' union
select 'real','SqlDbType.Real' union
select 'smalldatetime','SqlDbType.SmallDateTime' union
select 'smallint','SqlDbType.SmallInt' union
select 'smallmoney','SqlDbType.SmallMoney' union
select 'text','SqlDbType.Text' union
select 'timestamp','SqlDbType.Timestamp' union
select 'tinyint','SqlDbType.TinyInt' union
select 'uniqueidentifier','SqlDbType.UniqueIdentifier' union
select 'varbinary','SqlDbType.VarBinary' union
select 'varchar','SqlDbType.VarChar' union
select 'xml','SqlDbType.Xml') map on st.name=map.xtype) t
union
select 997,'new SqlParameter("@i_flag",SqlDbType.Int){Value=0},//操作类型' union
select 998,'new SqlParameter("@o_errCode",SqlDbType.VarChar,20){Direction = ParameterDirection.Output},//错误编码' union
select 999,'new SqlParameter("@o_errMsg",SqlDbType.NVarChar,100){Direction = ParameterDirection.Output}//错误信息') rst
order by idx
生成数据库字典：
declare @html nvarchar(max)
set @html = '<html><head><title>数据字典</title><style type="text/css">body{margin:0 auto;}table{margin-top:20px;width:100%;}th,td{}}</style></head><body>'
select @html=@html+'<table><thead><tr><th colspan="12">'+t.name+isnull(':'+convert(nvarchar(100),(select value from sys.extended_properties
where major_id=t.id and minor_id=0)),'')+'</th></tr><tr><th>序号</th><th>列名</th><th>标识列</th><th>主键</th><th>外键</th><th>数据类型</th><th>字节长度</th><th>长度</th><th>小数位</th><th>可空</th><th>默认值</th><th>说明</th></tr></thead><tbody>'+
convert(nvarchar(max),(SELECT 1 as Tag,null as Parent,
convert(varchar(10),a.colorder) as [tr!1!td!element],--序号
a.name as [tr!1!td!element],--列名
case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√' else '' end as [tr!1!td!element],--标识列
case when (SELECT count(1) FROM sysobjects
WHERE (name in
(SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns
WHERE (id = a.id) AND (name = a.name))
)))))
AND (xtype = 'PK' ))>0 then '√' else '' end as [tr!1