［收藏］取得数据库字典的sql语句

--MS SQL SERVER 2000 取得所有表和视图的名称、字段名、字段类型、说明、长度等
SELECT sysobjects.name AS TableName, sysproperties.[value] AS 表说明,
      syscolumns.name AS FieldName, properties.[value] AS 字段说明, systypes.name AS FieldType,
      syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,
      'Scale'), 0) AS 小数位数, syscolumns.isnullable AS isnull,
      CASE WHEN syscomments.text IS NULL
      THEN '' ELSE syscomments.text END AS [Default],
      CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')
      = 1 THEN '1' ELSE '' END AS 标识, 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 = syscolumns.id AND colid = syscolumns.colid)))
      THEN '1' ELSE '' END AS 主键
FROM syscolumns INNER JOIN
      sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
      systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
      sysproperties properties ON syscolumns.id = properties.id AND
      syscolumns.colid = properties.smallid LEFT OUTER JOIN
      sysproperties ON sysobjects.id = sysproperties.id AND
      sysproperties.smallid = 0 LEFT OUTER JOIN
      syscomments ON syscolumns.cdefault = syscomments.id
WHERE (sysobjects.xtype = 'U' OR sysobjects.xtype = 'V')

--MS SQL SERVER 2000 取得所有表和视图的名称、字段名、字段类型
SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS FieldName,
      dbo.systypes.name AS FieldType, '' AS FieldDesc
FROM dbo.syscolumns INNER JOIN
      dbo.sysobjects ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
      dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE ((dbo.sysobjects.xtype = 'U') OR (dbo.sysobjects.xtype = 'V')) and dbo.systypes.name <> 'sysname'



--FireBird 取得所有表和视图的名称、字段名、字段类型等
SELECT T2.RDB$RELATION_NAME AS TABLENAME,
       T2.RDB$FIELD_NAME AS FIELDNAME,
       CASE T1.RDB$FIELD_TYPE WHEN 7 THEN 'SMALLINT'
                              WHEN 8 THEN 'INTEGER'
                              WHEN 10 THEN 'FLOAT'
                              WHEN 12 THEN 'DATE'
                              WHEN 13 THEN 'TIME'
                              WHEN 14 THEN 'CHAR'
                              WHEN 16 THEN 'DECIMAL'
                              WHEN 27 THEN 'DOUBLE'
                              WHEN 35 THEN 'TIMESTAMP'
                              WHEN 37 THEN 'VARCHAR'
                              WHEN 261 THEN 'BLOB'
                              ELSE 'UNKNOW'
       END AS FIELDTYPE
FROM RDB$FIELDS T1, RDB$RELATION_FIELDS T2
WHERE T1.RDB$SYSTEM_FLAG = 0  AND T1.RDB$FIELD_NAME = T2.RDB$FIELD_SOURCE

--Oracle取得所有表和视图的名称、字段名、字段类型：
select TABLE_NAME as TableName, COLUMN_NAME AS FieldName, DATA_TYPE AS FieldType
from user_tab_columns

--PostgreSQL取得所有表和视图的名称、字段名、字段类型：

SELECT TABLE_NAME as TableName, COLUMN_NAME AS FieldName, DATA_TYPE AS FieldType
FROM information_schema.columns
WHERE table_schema='public'