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索引,字段说明
MySql 时间戳Timestamp字段类型的字段作为主键模拟用户并发操作
关于ORM实体类主键设置的几点建议(Tag：复合主键，ISID，PK)|C/S开发框架
关于ORM实体类主键设置的几点建议(Tag：复合主键，ISID，PK)
C#.NET 处理SQL特殊数据类型Geography/Geometry/Hierarchyid/XML
SqlDataAdapter.FillSchema(DataTable)方法填充数据表的主键和约束
SqlServer数据类型、DbType类型、.NET类型与达梦数据类型关系表|C/S开发框架
VS2022+.NET6：MySql时间戳(timestamp)类型对应EF Core类型注意事项
SQL Server 数据类型对应.NET类型的EF模型定义|C/S开发框架
C#.NET 查询MYSQL报错：值类型与列类型不匹配不能在列中存储。所需类型是MySqlDateTime
C#.NET给DataTable日期类型的列赋值报错：所需类型是 MySqlDateTime，值类型与列类型不匹配
MySQL数据类型(MySqlDbType)数据类型名称对应DbType类型
MsSQL数据类型(SqlDbType)名称对应DbType类型
MySql数据类型(MySqlDbType)名称对应.NET类型(Type)
MsSql数据类型(SqlDbType)名称对应.NET类型(Type)
MySql数据类型（MySqlDbType）与.NET类型(Type)对应关系