SQL获取表结构的表名,字段名称,类型,Identity,PK主键,FK外键,IDX索引,字段说明等资料|C/S框架网

SQL获取表结构的表名,字段名称,类型,Identity,PK主键,FK外键,IDX索引,字段说明等资料

作者:C/S框架网|www.cscode.ne 发布日期:2021-03-10 19:46:50

SQL获取表结构的表名,字段名称,类型,Identity,PK主键,FK外键,IDX索引,字段说明等资料

SQL Code:

ALTER PROCEDURE [dbo].[sp_sys_GetTableFieldDef]
@TableName VARCHAR(100) --表名，如：tb_MyUser
AS
BEGIN
/************************************************************************************
说明：获取表结构
程序：www.csframework.com C/S框架网

sp_sys_GetTableFieldDef 'tb_MyUser'
sp_sys_GetTableFieldDef 'tb_POs'
sp_sys_GetTableFieldDef 'vw_INs'

*************************************************************************************/
SELECT
TableName=d.name,
FieldOrder=a.colorder,
FieldName=a.name,
IsIdentity=CASE WHEN COLUMNPROPERTY(a.id,a.name, 'IsIdentity')=1 THEN 'Y' ELSE 'N' END,
PK=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 'Y' ELSE 'N' END,
FK=CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype= 'F' 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 'Y' ELSE 'N' END,
IDX=CASE WHEN EXISTS(
SELECT TOP 1 dd.name FROM sysindexes aa JOIN sysindexkeys bb ON aa.id = bb.id AND aa.indid = bb.indid
JOIN sysobjects cc ON bb.id = cc.id
JOIN syscolumns dd ON bb.id = dd.id AND bb.colid = dd.colid
WHERE dd.name=a.name AND dd.id=a.id AND cc.id=d.id AND aa.indid NOT IN ( 0 , 255 ) ) THEN 'Y' ELSE 'N' END,
FieldType=b.name,
FieldLength=a.length,
Prec=COLUMNPROPERTY(a.id,a.name, 'PRECISION '),
Scale=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale '),0),
AllowNull=CASE WHEN a.isnullable=1 THEN 'Y' ELSE 'N' END,
DefaultValue=isnull(e.text, ' '),
FieldCaption=CASE WHEN ISNULL(g.[value], '')='' THEN a.name ELSE g.[value] END
FROM syscolumns a
LEFT JOIN systypes b on a.xusertype=b.xusertype
INNER JOIN sysobjects d on a.id=d.id and d.xtype IN ('U','V') 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 --sql2005 改为 sysproperties表
WHERE d.name= @TableName --如果只查询指定表,加上此条件

END

//来源:C/S框架网 | www.csframework.com | QQ:23404761

SQL执行结果：

扫一扫加微信

参考文档：

sp_sys_GetTableFieldDef存过：SQL获取表结构的字段名称,类型,Identity,PK主键,FK外键,IDX索引,字段说明
SQL数据库备份历史记录表结构sys_BackupHistory
关于开发框架字段名管理功能（frmFieldNameMgr类）
ORM_FieldAttribute类详解：字段特性定义，框架内核根据此特性自动生成SQL脚本
关于ORM实体类主键设置的几点建议(Tag：复合主键，ISID，PK)
SqlDataAdapter.FillSchema(DataTable)方法填充数据表的主键和约束
ISID字段介绍，能直接用ISID自增字段作为主键使用吗？
With语法实现SQL树结构数据查询(转)
SQLExpress数据库类型与AttachDbFilename用法