SQL获取表结构的表名,字段名称,类型,Identity,PK主键,FK外键,IDX索引,字段说明等资料
作者:C/S框架网|www.cscode.ne  发布日期:2021/03/10 19:46:50
  SQL获取表结构的表名,字段名称,类型,Identity,PK主键,FK外键,IDX索引,字段说明等资料


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执行结果:






C/S框架网|原创精神.创造价值.打造精品

扫一扫加微信
C/S框架网作者微信 C/S框架网|原创作品.质量保障.竭诚为您服务


上一篇 下一篇