/**************************************************************************************************************
1、获取所有用户名:
islogin=\'1\'   :表示帐户
islogin=\'0\'   :表示角色
status=\'2\'   :表示用户帐户
status=\'0\'   :表示系统帐户
**************************************************************************************************************/
SELECT name
FROM   sysusers
WHERE  status = \'0\'
       AND islogin = \'1\'
/**************************************************************************************************************
2、获取所有数据库名:
**************************************************************************************************************/
SELECT name
FROM     master..sysdatabases
ORDER BY name
/**************************************************************************************************************
3、获取所有表名:
XType=\'U\'    :表示所有用户表;
XType=\'S\'    :表示所有系统表;
**************************************************************************************************************/
SELECT name
FROM     数据库名..sysobjects
WHERE    xtype = \'U\'
ORDER BY name
/**************************************************************************************************************
4、获取所有字段名:
**************************************************************************************************************/
SELECT name
FROM   syscolumns
WHERE  id = Object_id(\'表名\')
/**************************************************************************************************************
5、获取数据库所有类型:
**************************************************************************************************************/
SELECT name
FROM   systypes
/**************************************************************************************************************
6、获取主键字段:
**************************************************************************************************************/
SELECT name
FROM   syscolumns
WHERE  id = Object_id(\'表名\')
       AND colid = (SELECT TOP 1 keyno
                    FROM   sysindexkeys
                    WHERE  id = Object_id(\'表名\'))
/**************************************************************************************************************
7、获取表字段的基本信息:
a.
**************************************************************************************************************/
SELECT 字段名 = Rtrim(b.name)
       ,主键 = CASE 
               WHEN h.id IS NOT NULL THEN \'PK\'
               ELSE \'\'
             END
       ,字段类型 = Type_name(b.xusertype)
                + CASE 
                    WHEN b.colstat & 1 = 1 THEN \'[ID(\'
                                                 + CONVERT(VARCHAR,Ident_seed(a.name))
                                                 + \',\'
                                                 + CONVERT(VARCHAR,Ident_incr(a.name))
                                                 + \')]\'
                    ELSE \'\'
                  END
       ,长度 = b.length
       ,允许空 = CASE b.isnullable 
                WHEN 0 THEN \'N\'
                ELSE \'Y\'
              END
       ,默认值 = Isnull(e.TEXT,\'\')
       ,字段说明 = Isnull(c.VALUE,\'\')
FROM     sysobjects a
         INNER JOIN  sys.all_objects aa
           ON a.id=aa.object_id 
              AND  schema_name(schema_id)=\'dbo\'
         ,syscolumns b
         LEFT OUTER JOIN sys.extended_properties c
           ON b.id = c.major_id
              AND b.colid = c.minor_id
         LEFT OUTER JOIN syscomments e
           ON b.cdefault = e.id
         LEFT OUTER JOIN (SELECT g.id
                                 ,g.colid
                          FROM   sysindexes f
                                 ,sysindexkeys g
                          WHERE  (f.id = g.id)
                                 AND (f.indid = g.indid)
                                 AND (f.indid > 0)
                                 AND (f.indid < 255)
                                 AND (f.status & 2048) <> 0) h
           ON (b.id = h.id)
              AND (b.colid = h.colid)
WHERE    (a.id = b.id)
         AND (a.id = Object_id(\'表名\'))  --要查询的表改成你要查询表的名称
ORDER BY b.colid
/**************************************************************************************************************
b.
**************************************************************************************************************/
SELECT 表名 = CASE 
              WHEN a.colorder = 1 THEN d.name
              ELSE \'\'
            END
       ,表说明 = CASE 
                WHEN a.colorder = 1 THEN Isnull(f.VALUE,\'\')
                ELSE \'\'
              END
       ,字段序号 = a.colorder
       ,字段名 = a.name
       ,标识 = CASE 
               WHEN Columnproperty(a.id,a.name,\'IsIdentity\') = 1 THEN \'√\'
               ELSE \'\'
             END
       ,主键 = CASE 
               WHEN EXISTS (SELECT 1
                            FROM   sysobjects
                            WHERE  xtype = \'PK\'
                                   AND name IN (SELECT name
                                                FROM   sysindexes
                                                WHERE  indid IN (SELECT indid
                                                                 FROM   sysindexkeys
                                                                 WHERE  id = a.id
                                                                        AND colid = a.colid))) THEN \'√\'
               ELSE \'\'
             END
       ,类型 = b.name
       ,字段长度 = a.length
       ,占用字节数 = Columnproperty(a.id,a.name,\'PRECISION\')
       ,小数位数 = Isnull(Columnproperty(a.id,a.name,\'Scale\'),0)
       ,允许空 = CASE 
                WHEN a.isnullable = 1 THEN \'√\'
                ELSE \'\'
              END
       ,默认值 = Isnull(e.TEXT,\'\')
       ,字段说明 = Isnull(g.[value],\'\')
FROM     syscolumns a
         LEFT JOIN systypes b
           ON a.xusertype = b.xusertype
         INNER JOIN sysobjects d
           ON (a.id = d.id)
              AND (d.xtype = \'U\')
              AND (d.name <> \'dtproperties\') 
          INNER JOIN  sys.all_objects c
            ON d.id=c.object_id 
                AND  schema_name(schema_id)=\'dbo\'
         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)
         LEFT JOIN sys.extended_properties f
           ON (d.id = f.major_id)
              AND (f.minor_id = 0)
--where d.name=\'要查询的表\'         --如果只查询指定表,加上此条件
ORDER BY a.id
         ,a.colorder
/***************************************************************************************************************
c. 前两种不完整~
****************************************************************************************************************/
-- 資料庫中所有資料表：透過內定的Stored Procedure sp_tables
EXEC sp_tables @table_name = \'%\',@table_owner = \'dbo\',@table_qualifier = @DBName; 
--    取得資料表Schema
EXEC sp_columns @TableName 
--    取得欄位說明、備註(Extended Property)：這個是透過Sql內定的Function來處理
SELECT * FROM  ::fn_listextendedproperty(NULL, \'user\', \'dbo\', \'table\', @TableName, \'column\', default) 
--    取得主索引
EXEC sp_pkeys @TableName 
 


/**************************************************************************************************************
表及字段描述信息处理示例
**************************************************************************************************************/

--创建表
CREATE TABLE 表 (
  a1  VARCHAR(10)
  ,a2 CHAR(2))
--为表添加描述信息
EXECUTE Sp_addextendedproperty N\'MS_Description\' , \'人员信息表\' , N\'user\' , N\'dbo\' , N\'table\' , N\'表\' , NULL , NULL
EXEC sys.sp_addextendedproperty 
@name=N\'MS_Description\', @value=N\'人员信息表\' , 
@level0type=N\'SCHEMA\',@level0name=N\'dbo\', 
@level1type=N\'TABLE\',@level1name=N\'表\'
 


--为字段a1添加描述信息
EXECUTE Sp_addextendedproperty N\'MS_Description\' , \'姓名\' , N\'user\' , N\'dbo\' , N\'table\' , N\'表\' , N\'column\' , N\'a1\'
EXEC sys.sp_addextendedproperty 
@name=N\'MS_Description\', @value=N\'人员信息表\' , 
@level0type=N\'SCHEMA\',@level0name=N\'dbo\', 
@level1type=N\'TABLE\',@level1name=N\'表\', 
@level2type=N\'COLUMN\',@level2name=N\'a1\'

 


--为字段a2添加描述信息
EXECUTE Sp_addextendedproperty N\'MS_Description\' , \'性别\' , N\'user\' , N\'dbo\' , N\'table\' , N\'表\' , N\'column\' , N\'a2\'
--更新表中列a1的描述属性：
EXEC Sp_updateextendedproperty \'MS_Description\' , \'字段1\' , \'user\' , dbo , \'table\' , \'表\' , \'column\' , a1
--显示表的描述属性
SELECT *
FROM   ::fn_listextendedproperty(NULL,\'user\',\'dbo\',\'table\',\'表\',\'column\',NULL)
--删除表中列a1的描述属性：
EXEC Sp_dropextendedproperty \'MS_Description\' , \'user\' , dbo , \'table\' , \'表\' , \'column\' , a1
--删除测试
DROP TABLE 表