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

---

扫一扫加微信