Sql Server 2008获取表的注释，字段名，字段类型，字段描述等，代码自动生成必备

发布：2016/8/24 0:36:57作者：管理员来源：本站浏览次数：1179

SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
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='tab_user' --如果只查询指定表,加上此条件
order by
a.id,a.colorder

================================

SELECT
tableName = case when a.colorder=1 then d.name else '' end,
tableNameDescribe = case when a.colorder=1 then isnull(f.value,'') else '' end,
fieldNo = a.colorder,
fieldName = a.name,
isKey = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
isKeyMain = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
fieldType = b.name,
fieldByteLength = a.length,
fieldLength = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
pointDigits = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
allowEmpty = case when a.isnullable=1 then '√'else '' end,
fieldDefault = isnull(e.text,''),
fieldDescript = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
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='ElearningUser'
order by
a.id,a.colorder

　关于AJAX跨域调用ASP.NET MVC或者WebAPI服务的问题及解决方案

导出Excel--不用安装office　

outsystems构建sql语句查询过程 sql server 时间转字符串 Sql Server 生成连续时间段 Sql Server 生成连续的日期列表 SQL 生成日期范围间的日期 sql server 获取guid sql server 2014 配置分发 SQL Server删除distribution数据库 sql2014针对FILESTREAM功能被禁用 sql 禁止修改表某个字段的内容,但可以使用程序修改字段内容

柏港建站平台

柏港 Mssql

Sql Server 2008获取表的注释，字段名，字段类型，字段描述等，代码自动生成必备

柏港为您推荐了一些相关资讯