SQL获取数据库名，表名，列名，说明等信息 转自汉城节度使 http://www.cnblogs.com/tuqun/p/3724000.html
/**************************************************************************************************************1、获取所有用户名:islogin=‘1‘   :表示帐户islogin=‘0‘   :表示角色status=‘2‘   :表示用户帐户status=‘0‘   :表示系统帐户**************************************************************************************************************/SELECT nameFROM   sysusersWHERE  status = ‘0‘       AND islogin = ‘1‘/**************************************************************************************************************2、获取所有数据库名:**************************************************************************************************************/SELECT nameFROM     master..sysdatabasesORDER BY name/**************************************************************************************************************3、获取所有表名:XType=‘U‘    :表示所有用户表;XType=‘S‘    :表示所有系统表;**************************************************************************************************************/SELECT nameFROM     数据库名..sysobjectsWHERE    xtype = ‘U‘ORDER BY name/**************************************************************************************************************4、获取所有字段名:**************************************************************************************************************/SELECT nameFROM   syscolumnsWHERE  id = Object_id(‘表名‘)/**************************************************************************************************************5、获取数据库所有类型:**************************************************************************************************************/SELECT nameFROM   systypes/**************************************************************************************************************6、获取主键字段:**************************************************************************************************************/SELECT nameFROM   syscolumnsWHERE  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_tablesEXEC sp_tables @table_name = ‘%‘,@table_owner = ‘dbo‘,@table_qualifier = @DBName; --    取得資料表SchemaEXEC 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 , NULLEXEC sys.sp_addextendedproperty @name=N‘MS_Description‘, @value=http://www.mamicode.com/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=http://www.mamicode.com/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 表