SQL Server结构查询SQL

/*表清单*/
SELECT
      B.name AS username,
      A.name,
      A.id,
      A.deltrig,
      A.instrig,
      A.updtrig,
      A.seltrig
 FROM sysobjects AS A,
      sysusers AS B
 WHERE A.uid=B.uid
   AND A.type='U'
 ORDER BY username, A.name

/*字段
status字段为位图，各位含义：
0x08 = 列允许空值。
0x10 = 当添加 varchar 或 varbinary 列时，ANSI 填充生效。保留 varchar 列的尾随空格，保留 varbinary 列的尾随零。
0x40 = 参数为 OUTPUT 参数。
0x80 = 列为标识列。
*/
SELECT
      A.colid,
      A.name,
      A.xusertype,
      A.typename,                          
      A.length, 
      A.xprec,
      A.xscale,                   
      (A.status & 0x08) AS is_null,
      (A.status & 0x10) AS ansi_fill,
      (A.status & 0x40) AS for_ouput,
      (A.status & 0x80) AS is_identify,
      B.text AS default_text             
 FROM (SELECT
             Y.colid,
             Y.name,
             Y.xusertype,
             Z.name AS typename,            
             Y.length, 
             Y.xprec,
             Y.xscale,              
             Y.status,
             Y.cdefault                    
        FROM  syscolumns AS Y,                         
              systypes AS Z                       
        WHERE Y.xusertype=Z.xusertype
          AND Y.id='<表/视图编号>'          
       ) AS A                                    
 LEFT JOIN syscomments AS B                 
 ON A.cdefault=B.id                         
 ORDER BY A.colid

/*主键*/
DECLARE @pk_name VARCHAR(512)
DECLARE @table_name VARCHAR(50)
SET @table_name='<表名>'

SELECT @pk_name=name
 FROM sysobjects
 WHERE parent_obj=OBJECT_ID(@table_name)
   AND xtype='PK'
  
SELECT
      I.indid,
      I.name,
      INDEX_COL(@table_name, I.indid, 1) AS field1,
      INDEX_COL(@table_name, I.indid, 2) AS field2,
      INDEX_COL(@table_name, I.indid, 3) AS field3,
      INDEX_COL(@table_name, I.indid, 4) AS field4,
      INDEX_COL(@table_name, I.indid, 5) AS field5,
      INDEX_COL(@table_name, I.indid, 6) AS field6,
      INDEX_COL(@table_name, I.indid, 7) AS field7,
      INDEX_COL(@table_name, I.indid, 8) AS field8,
      INDEX_COL(@table_name, I.indid, 9) AS field9,
      INDEX_COL(@table_name, I.indid, 10) AS field10,
      INDEX_COL(@table_name, I.indid, 11) AS field11,
      INDEX_COL(@table_name, I.indid, 12) AS field12,
      INDEX_COL(@table_name, I.indid, 13) AS field13,
      INDEX_COL(@table_name, I.indid, 14) AS field14,
      INDEX_COL(@table_name, I.indid, 15) AS field15,
      INDEX_COL(@table_name, I.indid, 16) AS field16
 FROM sysindexes I
 WHERE I.name=@pk_name


/*唯一键*/
DECLARE @uq_name VARCHAR(512)
DECLARE @table_name VARCHAR(50)
SET @table_name='<表名>'

SELECT  @uq_name=name
 FROM sysobjects
 WHERE parent_obj=OBJECT_ID(@table_name)
   AND xtype='UQ'
  
SELECT
      I.indid,
      I.name,
      INDEX_COL(@table_name, I.indid, 1) AS field1,
      INDEX_COL(@table_name, I.indid, 2) AS field2,
      INDEX_COL(@table_name, I.indid, 3) AS field3,
      INDEX_COL(@table_name, I.indid, 4) AS field4,
      INDEX_COL(@table_name, I.indid, 5) AS field5,
      INDEX_COL(@table_name, I.indid, 6) AS field6,
      INDEX_COL(@table_name, I.indid, 7) AS field7,
      INDEX_COL(@table_name, I.indid, 8) AS field8,
      INDEX_COL(@table_name, I.indid, 9) AS field9,
      INDEX_COL(@table_name, I.indid, 10) AS field10,
      INDEX_COL(@table_name, I.indid, 11) AS field11,
      INDEX_COL(@table_name, I.indid, 12) AS field12,
      INDEX_COL(@table_name, I.indid, 13) AS field13,
      INDEX_COL(@table_name, I.indid, 14) AS field14,
      INDEX_COL(@table_name, I.indid, 15) AS field15,
      INDEX_COL(@table_name, I.indid, 16) AS field16
 FROM sysindexes I
 WHERE I.name=@uq_name

/*外键*/
SELECT name, id, xtype
 FROM sysobjects
 WHERE parent_obj='<表编号>'
   AND xtype='F'
  
SELECT
      Y.fkeyTableName,
      Y.fkeyColName,
      Z.rkeyUserName,
      Z.rkeyTableName,
      Z.rkeyColName
 FROM (SELECT
             B.name AS fkeyTableName,
             C.name AS fkeyColName,
             A.keyno
         FROM sysforeignkeys AS A,
              sysobjects AS B,
              syscolumns AS C
         WHERE A.constid='<外键编号>'
              AND A.fkeyid=B.id
              AND A.fkeyid=C.id
              AND A.fkey=C.colid
      ) AS Y,
      (SELECT
             D.name AS rkeyUserName,
             B.name AS rkeyTableName,
             C.name AS rkeyColName,
             A.keyno
         FROM sysforeignkeys AS A,
              sysobjects AS B,
              syscolumns AS C,
              sysusers AS D
         WHERE A.constid='<外键编号>'
              AND A.rkeyid=B.id
              AND A.rkeyid=C.id
              AND A.rkey=C.colid
              AND B.uid=d.uid
     ) AS Z
 WHERE Y.keyno=Z.keyno
 ORDER BY Y.keyno

/*约束*/
SELECT
      a.id,
      a.xtype,
      a.name,
      b.text,
      b.encrypted,
      b.compressed
FROM sysobjects AS a,
     syscomments AS b
WHERE a.xtype='C'
  AND a.parent_obj='<表编号>'
  AND a.id=b.id


/*索引*/
DECLARE @table_name VARCHAR(50)
SET @table_name='<表名>'

SELECT
      I.indid,
      I.name,
      INDEX_COL(@table_name, I.indid, 1) AS field1,
      INDEX_COL(@table_name, I.indid, 2) AS field2,
      INDEX_COL(@table_name, I.indid, 3) AS field3,
      INDEX_COL(@table_name, I.indid, 4) AS field4,
      INDEX_COL(@table_name, I.indid, 5) AS field5,
      INDEX_COL(@table_name, I.indid, 6) AS field6,
      INDEX_COL(@table_name, I.indid, 7) AS field7,
      INDEX_COL(@table_name, I.indid, 8) AS field8,
      INDEX_COL(@table_name, I.indid, 9) AS field9,
      INDEX_COL(@table_name, I.indid, 10) AS field10,
      INDEX_COL(@table_name, I.indid, 11) AS field11,
      INDEX_COL(@table_name, I.indid, 12) AS field12,
      INDEX_COL(@table_name, I.indid, 13) AS field13,
      INDEX_COL(@table_name, I.indid, 14) AS field14,
      INDEX_COL(@table_name, I.indid, 15) AS field15,
      INDEX_COL(@table_name, I.indid, 16) AS field16
 FROM sysindexes I
 WHERE I.id = OBJECT_ID(@table_name)
   AND I.indid > 0
   AND I.indid < 255
   AND INDEXPROPERTY(I.id, I.name, N'IsStatistics') = 0
   AND INDEXPROPERTY(I.id, I.name, N'IsHypothetical') = 0
   AND I.name NOT IN
    (SELECT O.name
      FROM sysobjects O
      WHERE O.parent_obj = I.id
        AND OBJECTPROPERTY(O.id, N'isConstraint') = 1
    )

/*触发器*/
SELECT
      a.id, 
      a.name, 
      b.text,
      b.encrypted,
      b.compressed
 FROM sysobjects AS a,
      syscomments AS b
 WHERE a.xtype='TR'
   AND a.parent_obj='<表编号>'
   AND a.id=b.id


/*存储过程*/
SELECT
      B.name AS username,
      A.name,
      A.id, 
      C.text,
      C.encrypted,
      C.compressed
 FROM sysobjects AS A,
      sysusers AS B,
      syscomments AS C
 WHERE A.uid=B.uid
   AND A.id=C.id
   AND A.type='P'
 ORDER BY username, A.name
 
/*视图*/
SELECT
      B.name AS username,
      A.name,
      A.id, 
      C.text,
      C.encrypted,
      C.compressed
 FROM sysobjects AS A,
      sysusers AS B,
      syscomments AS C
 WHERE A.uid=B.uid
   AND A.id=C.id
   AND A.type='V'
 ORDER BY username, A.name

/*用户函数*/
SELECT
      B.name AS username,
      A.name,
      A.id, 
      C.text,
      C.encrypted,
      C.compressed
 FROM sysobjects AS A,
      sysusers AS B,
      syscomments AS C
 WHERE A.uid=B.uid
   AND A.id=C.id
   AND A.type='FN'
 ORDER BY username, A.name
 
/*系统中所有的类型*/
SELECT
      A.xtype,
      A.xusertype,
      A.length,
      A.xprec,
      A.xscale,
      B.name AS username,
      A.name,
      A.allownulls
 FROM systypes AS A,
      sysusers AS B
 WHERE A.uid=B.uid
 ORDER BY A.xtype
 
/*创建用户自定义类型*/
sp_addtype '<类型名>', '<定义>', '{NULL | NONULL}', '<用户>'

/*读取表描述信息*/
SELECT
      objname,
      value
 FROM ::fn_listextendedproperty ('MS_Description', 'USER', '<用户名>', 'table', '<表名>', NULL, NULL)

/*读取字段描述信息*/
SELECT
      objname,
      value
 FROM ::fn_listextendedproperty ('MS_Description', 'USER', '<用户名>', 'table', '<表名>', 'column', NULL)