表结构音讯查询数据库,SQL语句出自CodeSmith

2019-07-05 作者:数据库   |   浏览(153)

近年来项目必要做怎样数据字典,须要表结构音讯。在网络看了比较多关于表结构音讯的询问,以为都不怎么着。相对好一点正是《基于SQL贰零零柒SQL2010 表结构新闻查询进级版的详解(含外键新闻)》 ,不过此地有一点点小题目,缺乏七个过滤以至运维有好几小bug。在AdventureWorks二〇一二数据库中的Address表查询结果如图:
数据库 1
在询问过滤中大家增加以下消息就ok了:
AND g.class_desc = 'OBJECT_OR_COLUMN'
修改后的SQL如下:

 

复制代码 代码如下:

前言

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    dbo.sysindexes si
                                        INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                                              AND si.indid = sik.indid
                                        INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                                              AND sc.colid = sik.colid
                                        INNER JOIN dbo.sysobjects so ON so.name = so.name
                                                              AND so.xtype = 'PK'
                                WHERE   sc.id = a.id
                                        AND sc.colid = a.colid ) THEN '√'
                  ELSE ''
             END ,
        外键 = CASE WHEN tony.fkey IS NOT NULL
                       AND tony.fkey = a.colid THEN '√'
                  ELSE ''
             END ,
        外键表 = CASE WHEN tony.fkey IS NOT NULL
                        AND tony.fkey = a.colid THEN OBJECT_NAME(tony.fkeyid)
                   ELSE ''
              END ,
        外键字段 = CASE WHEN tony.fkey IS NOT NULL
                         AND tony.fkey = a.colid
                    THEN ( SELECT   name
                           FROM     syscolumns
                           WHERE    colid = tony.fkey
                                    AND id = tony.fkeyid
                         )
                    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], '') ,
        创设时间 = d.crdate ,
        更换时间 = CASE WHEN a.colorder = 1 THEN d.refdate
                    ELSE NULL
               END
FROM    dbo.syscolumns a
        LEFT JOIN dbo.systypes b ON a.xtype = b.xusertype
        INNER JOIN dbo.sysobjects d ON a.id = d.id
                                       AND d.xtype = 'U'
                                       AND d.status >= 0
        LEFT JOIN dbo.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
                                               AND g.class_desc = 'OBJECT_OR_COLUMN'
        LEFT JOIN sys.extended_properties f ON d.id = f.major_id
                                               AND f.minor_id = 0
        LEFT JOIN sysobjects htl ON htl.parent_obj = d.id
                                    AND htl.xtype = 'F'
        LEFT JOIN sysforeignkeys tony ON htl.id = tony.constid
WHERE   d.name = 'Address'  --这里输入包括表名称的基准
ORDER BY d.id ,
        a.colorder

     近些日子老赵发了一篇有关和煦社区,和煦技能:微软的命根子们,为啥富人的孩子就无法早当家?掀起的冲突于今令笔者心神不属,说得很在理!!那篇文章本计划放首页的,但是出于是拿来主义,作者认为那属于新手的出色特征之一(本来正是新手=_=),故安安分分的放新手区吧。心里相比实在一点,尽管见到的人非常少也没涉及,自个儿做个笔记 :)

运维结果如图:
数据库 2
本人不怎么喜欢它的“类型”信息,一般的varchar都会有长度新闻,还会有那个查询对于SQL 二零一一的新数据类型不帮衬,该SQL里面包车型大巴嵌套查询比比较多,于是笔者就自身再度写了三个SQL。
此处提示大家尽量用INFORMATION_SCHEMA.XXX视图而不去用sys.XXX视图。
新的SQL如下:

     有那个有爱人都比较关注代码自动生成,理当如此离不开元数据了,不过对于获得元数据的法子差别。由于近日自身也在写代码生成,对元数据的获得SQL语句并不完备,意外的想到了CodeSmith,它也足以依据元数据来生成三层的,于是乎习于旧贯性的去找她的源码了,果然未有失望,大家在SchemaProviders目录下能看到如下文件:

复制代码 代码如下:

SchemaExplorer.ADOXSchemaProvider.dll

SELECT
 --OBJECT_ID(a.TABLE_SCHEMA '.' a.TABLE_NAME) AS [object_id] ,
        CASE WHEN a.ORDINAL_POSITION = 1
             THEN a.TABLE_SCHEMA '.' a.TABLE_NAME
             ELSE ''
        END AS TABLE_NAME ,
        CASE WHEN ( a.ORDINAL_POSITION = 1
                    AND p1.value IS NOT NULL
                  ) THEN p1.value
             ELSE ''
        END AS TABLE_Description ,
        a.COLUMN_NAME ,
        CASE WHEN ( ( CHARINDEX('char', a.DATA_TYPE) > 0
                      OR CHARINDEX('binary', a.DATA_TYPE) > 0
                    )
                    AND a.CHARACTER_MAXIMUM_LENGTH <> -1
                  )
             THEN a.DATA_TYPE '('
                  CAST(a.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) ')'
             WHEN ( ( CHARINDEX('CHAR', a.DATA_TYPE) > 0
                      OR CHARINDEX('binary', a.DATA_TYPE) > 0
                    )
                    AND a.CHARACTER_MAXIMUM_LENGTH = -1
                  ) THEN a.DATA_TYPE '(max)'
             WHEN ( CHARINDEX('numeric', a.DATA_TYPE) > 0 )
             THEN a.DATA_TYPE '(' CAST(a.NUMERIC_PRECISION AS VARCHAR(4))
                  ',' CAST(a.NUMERIC_SCALE AS VARCHAR(4)) ')'
             ELSE a.DATA_TYPE
        END AS COLUMN_TYPE ,
        CASE WHEN c.IS_IDENTITY = 1 THEN 'YES'
             ELSE 'NO'
        END AS IS_IDENTITY ,
        a.IS_NULLABLE ,
        CASE WHEN a.COLUMN_DEFAULT IS NULL THEN ''
             ELSE a.COLUMN_DEFAULT
        END AS Default_Value ,
        CASE WHEN p.value IS NULL THEN ''
             ELSE p.value
        END AS [COLUMN_Description] ,
        CASE WHEN o.name IS  NULL THEN ''
             ELSE '√'
        END AS Is_PrimaryKey ,
        CASE WHEN f.parent_column_id IS NULL THEN ''
             ELSE '√'
        END AS Is_Foreignkeys ,
        CASE WHEN referenced_object_id IS NULL THEN ''
             ELSE OBJECT_NAME(referenced_object_id)
        END AS Foreign_Table ,
        CASE WHEN referenced_object_id IS NULL THEN ''
             ELSE ( SELECT  name
                    FROM    sys.columns
                    WHERE   object_id = f.referenced_object_id
                            AND column_id = f.referenced_column_id
                  )
        END AS Foreign_key
FROM    INFORMATION_SCHEMA.COLUMNS a
        INNER JOIN sys.columns c ON OBJECT_ID(a.TABLE_SCHEMA '.'
                                              a.TABLE_NAME) = c.OBJECT_ID
                                    AND a.COLUMN_NAME = c.NAME
        LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
                                                           AND a.TABLE_NAME = b.TABLE_NAME
                                                           AND a.COLUMN_NAME = b.COLUMN_NAME
        LEFT JOIN sys.sysobjects o ON o.name = b.CONSTRAINT_NAME
                                      AND o.xtype = 'PK'
        LEFT JOIN sys.extended_properties p ON OBJECT_ID(a.TABLE_SCHEMA '.'
                                                         a.TABLE_NAME) = p.major_id
                                               AND a.Ordinal_position = p.minor_id
                                               AND p.class_desc = 'OBJECT_OR_COLUMN'
        LEFT JOIN sys.extended_properties p1 ON OBJECT_ID(a.TABLE_SCHEMA '.'
                                                          a.TABLE_NAME) = p1.major_id
                                                AND p1.minor_id = 0
        LEFT JOIN SYS.foreign_key_columns f ON OBJECT_ID(a.TABLE_SCHEMA '.'
                                                         a.TABLE_NAME) = f.parent_object_id
                                               AND a.ORDINAL_POSITION = f.parent_column_id
WHERE   a.TABLE_NAME = 'Address'
-- a.TABLE_NAME IN (SELECT name FROM sys.tables)
ORDER BY a.TABLE_SCHEMA,a.TABLE_NAME, a.ORDINAL_POSITION

SchemaExplorer.MySQLSchemaProvider.dll

运作效果如图:
数据库 3
有狼狈的地方还请大家拍砖!多谢!

SchemaExplorer.OracleSchemaProvider.dll

基于SQL200...

SchemaExplorer.SqlSchemaProvider.dll

尚无加密!然则SchemaExplorer.SqlSchemaProvider.dll混淆了!用Reflector查看源代码照旧能观看SQL语句,于是乎拷贝出来,替换tn,弄了本身四个小时才完,后天一搜,原本有源码-

!!一级郁闷!!大伙就别走笔者的弯路了呢!源码在SamplesProjects目录下。上边起先Ctrl C,然后Ctrl V,贴上来给未有下载CodeSmith的相恋的人收藏一下呢:)

 

版本

     CodeSmithProfessional 4.1

 

正文

     1.     闲话少说,直接贴SQL2000/2005取得元数据的SQL语句了,我们一看就能够领悟的。

#region SQL Templates
        private const string SQL_GetDatabaseName = "SELECT db_name()";

        private const string SQL2005_GetTables = @"
          SELECT
              object_name(so.id) AS OBJECT_NAME,
              schema_name(so.uid) AS USER_NAME,
              so.type AS TYPE,
              so.crdate AS DATE_CREATED,
              fg.file_group AS FILE_GROUP,
              so.id as OBJECT_ID
          FROM 
              dbo.sysobjects so
          LEFT JOIN (
              SELECT 
                  s.groupname AS file_group,
                  i.id        AS id
              FROM dbo.sysfilegroups s
              INNER JOIN dbo.sysindexes i
                  ON i.groupid = s.groupid 
              WHERE i.indid < 2                           
          ) AS fg
              ON so.id = fg.id
          WHERE
              so.type = N'U'
              AND permissions(so.id) & 4096 <> 0
              AND ObjectProperty(so.id, N'IsMSShipped') = 0
              AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = so.id AND name = 'microsoft_database_tools_support' AND value = 1)
          ORDER BY schema_name(so.uid), object_name(so.id)";

        private const string SQL2000_GetTables = @"
          SELECT
              object_name(so.id) AS OBJECT_NAME,
              user_name(so.uid)  AS USER_NAME,
              so.type            AS TYPE,
              so.crdate          AS DATE_CREATED,
              fg.file_group      AS FILE_GROUP,
              so.id              AS OBJECT_ID
          FROM 
              dbo.sysobjects so
          LEFT JOIN (
                SELECT 
                    s.groupname AS file_group,
                    i.id        AS id
                FROM dbo.sysfilegroups s
                INNER JOIN dbo.sysindexes i
                    ON i.groupid = s.groupid 
                WHERE i.indid < 2                           
              ) AS fg
              ON so.id = fg.id
          WHERE
              so.type = N'U'
              AND permissions(so.id) & 4096 <> 0
              AND ObjectProperty(so.id, N'IsMSShipped') = 0
          ORDER BY user_name(so.uid), object_name(so.id)";

        private const string SQL_GetTables = @"
            SELECT
              object_name(id) AS OBJECT_NAME,
              user_name(uid) AS USER_NAME,
              type AS TYPE,
              crdate AS DATE_CREATED,
              '' AS FILE_GROUP,
              id as OBJECT_ID
            FROM
              sysobjects
            WHERE
              type = N'U'
              AND permissions(id) & 4096 <> 0
              AND ObjectProperty(id, N'IsMSShipped') = 0
            ORDER BY user_name(uid), object_name(id)";

        private const string SQL2005_GetTableColumns = @"
             SELECT
              clmns.[name] AS [Name],
              usrt.[name] AS [DataType],
              ISNULL(baset.[name], N'') AS [SystemType],
              CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS int) AS [Length],
              CAST(clmns.xprec AS tinyint) AS [NumericPrecision],
              CAST(clmns.xscale AS int) AS [NumericScale],
              CASE CAST(clmns.isnullable AS bit) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nullable],
              defaults.text AS [DefaultValue],
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS int) AS [Identity],
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS int) AS IsRowGuid,
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS int) AS IsComputed,
              CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS int) AS IsDeterministic,
              CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(SCHEMA_NAME(tbl.uid))   '.'   QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentitySeed],
              CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(SCHEMA_NAME(tbl.uid))   '.'   QUOTENAME(tbl.[name])) ELSE 0 END AS nvarchar(40)) AS [IdentityIncrement],
              cdef.[text] AS ComputedDefinition,
              clmns.[collation] AS Collation,
              CAST(clmns.colid AS int) AS ObjectId
            FROM
              dbo.sysobjects AS tbl
              INNER JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
              LEFT JOIN dbo.systypes AS usrt ON usrt.xusertype = clmns.xusertype
              LEFT JOIN dbo.sysusers AS sclmns ON sclmns.uid = usrt.uid
              LEFT JOIN dbo.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
              LEFT JOIN dbo.syscomments AS defaults ON defaults.id = clmns.cdefault
              LEFT JOIN dbo.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
            WHERE
              (tbl.[type] = 'U' OR tbl.[type] = 'S')
              AND SCHEMA_NAME(tbl.uid) = @SchemaName
              AND tbl.[name] = @TableName
            ORDER BY
              clmns.colorder";

        private const string SQL2000_GetTableColumns = @"
              SELECT
                clmns.[name] AS [Name],
                usrt.[name] AS [DataType],
                ISNULL(baset.[name], N'') AS [SystemType],
                CAST(CASE WHEN baset.[name] IN (N'char', N'varchar', N'binary', N'varbinary', N'nchar', N'nvarchar') THEN clmns.prec ELSE clmns.length END AS INT) AS [Length],
                CAST(clmns.xprec AS TINYINT) AS [NumericPrecision],
                CAST(clmns.xscale AS INT) AS [NumericScale],
                CASE CAST(clmns.isnullable AS BIT) WHEN 1 THEN 'YES' ELSE 'NO' END AS [Nullable],
                defaults.text AS [DefaultValue],
                CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') AS INT) AS [Identity],
                CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsRowGuidCol') AS INT) AS IsRowGuid,
                CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsComputed') AS INT) AS IsComputed,
                CAST(COLUMNPROPERTY(clmns.id, clmns.[name], N'IsDeterministic') AS INT) AS IsDeterministic,
                CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_SEED(QUOTENAME(stbl.[name])   '.'   QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)) AS [IdentitySeed],
                CAST(CASE COLUMNPROPERTY(clmns.id, clmns.[name], N'IsIdentity') WHEN 1 THEN IDENT_INCR(QUOTENAME(stbl.[name])   '.'   QUOTENAME(tbl.[name])) ELSE 0 END AS NVARCHAR(40)) AS [IdentityIncrement],
                cdef.[text] AS ComputedDefinition,
                clmns.[collation] AS Collation,
                CAST(clmns.colid AS int) AS ObjectId
              FROM
                dbo.sysobjects AS tbl
                INNER JOIN dbo.sysusers AS stbl ON stbl.[uid] = tbl.[uid]
                INNER JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
                LEFT JOIN dbo.systypes AS usrt ON usrt.xusertype = clmns.xusertype
                LEFT JOIN dbo.sysusers AS sclmns ON sclmns.uid = usrt.uid
                LEFT JOIN dbo.systypes AS baset ON baset.xusertype = clmns.xtype and baset.xusertype = baset.xtype
                LEFT JOIN dbo.syscomments AS defaults ON defaults.id = clmns.cdefault
                LEFT JOIN dbo.syscomments AS cdef ON cdef.id = clmns.id AND cdef.number = clmns.colid
              WHERE
                (tbl.[type] = 'U' OR tbl.[type] = 'S') 
                AND stbl.[name] = @SchemaName
                AND tbl.[name] = @TableName
              ORDER BY
                  clmns.colorder";

        private const string SQL_GetTableColumns = @"
                  SELECT
                      cols.COLUMN_NAME,
                      CASE
                          WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME
                          ELSE cols.DATA_TYPE
                      END
                      AS DATA_TYPE,
                      cols.DATA_TYPE AS UNDERLYING_TYPE,
                      CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int),
                      cols.NUMERIC_PRECISION,
                      cols.NUMERIC_SCALE,
                      cols.IS_NULLABLE,
                      cols.COLUMN_DEFAULT,
                      COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @TableName   N']'),cols.COLUMN_NAME,'IsIdentity') AS IS_IDENTITY,
                      COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @TableName   N']'),cols.COLUMN_NAME,'IsRowGuidCol') AS IS_ROW_GUID_COL,
                      COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @TableName   N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,
                      COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @TableName   N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC,
                      CASE WHEN (COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @TableName   N']'), cols.COLUMN_NAME, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_seed(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @TableName   N']')) else null end AS IDENTITY_SEED,
                      CASE WHEN (COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @TableName   N']'), cols.COLUMN_NAME, N'IsIdentity') <> 0) then CONVERT(nvarchar(40), ident_incr(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @TableName   N']')) else null end AS IDENTITY_INCREMENT,
                    NULL AS COMPUTED_DEFINITION,
                    NULL AS [collation],
                    CAST(0 AS int) AS ObjectId
                  FROM
                      INFORMATION_SCHEMA.COLUMNS cols
                  WHERE
                      cols.TABLE_CATALOG = @DatabaseName
                      AND cols.TABLE_SCHEMA = @OwnerName
                      AND cols.TABLE_NAME = @TableName
                  ORDER BY
                      cols.ORDINAL_POSITION";

        private const string SQL2005_GetViews = @"
                  SELECT
                      object_name(id) AS OBJECT_NAME,
                      schema_name(uid) AS USER_NAME,
                      type AS TYPE,
                      crdate AS DATE_CREATED,
                      id as OBJECT_ID
                  FROM
                      sysobjects
                  WHERE
                      type = N'V'
                      AND permissions(id) & 4096 <> 0
                      AND ObjectProperty(id, N'IsMSShipped') = 0
                      AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = id AND name = 'microsoft_database_tools_support' AND value = 1)
                  ORDER BY object_name(id)";

        private const string SQL_GetViews = @"
                  SELECT
                      object_name(id) AS OBJECT_NAME,
                      user_name(uid) AS USER_NAME,
                      type AS TYPE,
                      crdate AS DATE_CREATED,
                      id as OBJECT_ID
                  FROM
                      sysobjects
                  WHERE
                      type = N'V'
                      AND permissions(id) & 4096 <> 0
                      AND ObjectProperty(id, N'IsMSShipped') = 0
          ORDER BY object_name(id)";

        private const string SQL2000_GetViewColumns = @"
                  SELECT
                      cols.COLUMN_NAME,
                      CASE
                          WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME COLLATE Latin1_General_BIN
                          ELSE cols.DATA_TYPE
                      END
                      AS DATA_TYPE,
                      cols.DATA_TYPE AS UNDERLYING_TYPE,
                      CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int),
                      cols.NUMERIC_PRECISION,
                      cols.NUMERIC_SCALE,
                      cols.IS_NULLABLE,
                      COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @ViewName   N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,
                      COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @ViewName   N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC
                  FROM
                      INFORMATION_SCHEMA.COLUMNS cols
                  WHERE
                      cols.TABLE_CATALOG = @DatabaseName
                      AND cols.TABLE_SCHEMA = @OwnerName
                      AND cols.TABLE_NAME = @ViewName
                  ORDER BY
                      cols.ORDINAL_POSITION";

        private const string SQL_GetViewColumns = @"
                  SELECT
                      cols.COLUMN_NAME,
                      CASE
                          WHEN cols.DOMAIN_NAME IS NOT NULL THEN cols.DOMAIN_NAME
                          ELSE cols.DATA_TYPE
                      END
                      AS DATA_TYPE,
                      cols.DATA_TYPE AS UNDERLYING_TYPE,
                      CAST(cols.CHARACTER_MAXIMUM_LENGTH AS int),
                      cols.NUMERIC_PRECISION,
                      cols.NUMERIC_SCALE,
                      cols.IS_NULLABLE,
                      COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @ViewName   N']'),cols.COLUMN_NAME,'IsComputed') AS IS_COMPUTED,
                      COLUMNPROPERTY(OBJECT_ID(N'['   @DatabaseName   N'].['   @OwnerName   N'].['   @ViewName   N']'),cols.COLUMN_NAME,'IsDeterministic') AS IS_DETERMINISTIC
                  FROM
                      INFORMATION_SCHEMA.COLUMNS cols
                  WHERE
                      cols.TABLE_CATALOG = @DatabaseName
                      AND cols.TABLE_SCHEMA = @OwnerName
                      AND cols.TABLE_NAME = @ViewName
                  ORDER BY
                      cols.ORDINAL_POSITION";

        private const string SQL_GetTablePrimaryKey = "EXEC sp_MStablekeys @tablename";

        private const string SQL_GetTableIndexes = @"
        SELECT 
          i.name, 
          i.status, 
          i.indid, 
          i.OrigFillFactor,
          IndCol1  = INDEX_COL(@tablename, i.indid, 1),
          IndCol2  = INDEX_COL(@tablename, i.indid, 2),
          IndCol3  = INDEX_COL(@tablename, i.indid, 3),
          IndCol4  = INDEX_COL(@tablename, i.indid, 4),
          IndCol5  = INDEX_COL(@tablename, i.indid, 5),
          IndCol6  = INDEX_COL(@tablename, i.indid, 6),
          IndCol7  = INDEX_COL(@tablename, i.indid, 7),
          IndCol8  = INDEX_COL(@tablename, i.indid, 8),
          IndCol9  = INDEX_COL(@tablename, i.indid, 9),
          IndCol10 = INDEX_COL(@tablename, i.indid, 10),
          IndCol11 = INDEX_COL(@tablename, i.indid, 11),
          IndCol12 = INDEX_COL(@tablename, i.indid, 12),
          IndCol13 = INDEX_COL(@tablename, i.indid, 13),
          IndCol14 = INDEX_COL(@tablename, i.indid, 14),
          IndCol15 = INDEX_COL(@tablename, i.indid, 15),
          IndCol16 = INDEX_COL(@tablename, i.indid, 16),    
          IsDescCol1  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 1,  N'isdescending'),
          IsDescCol2  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 2,  N'isdescending'),
          IsDescCol3  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 3,  N'isdescending'),
          IsDescCol4  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 4,  N'isdescending'),
          IsDescCol5  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 5,  N'isdescending'),
          IsDescCol6  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 6,  N'isdescending'),
          IsDescCol7  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 7,  N'isdescending'),
          IsDescCol8  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 8,  N'isdescending'),
          IsDescCol9  = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 9,  N'isdescending'),
          IsDescCol10 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 10, N'isdescending'),
          IsDescCol11 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 11, N'isdescending'),
          IsDescCol12 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 12, N'isdescending'),
          IsDescCol13 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 13, N'isdescending'),
          IsDescCol14 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 14, N'isdescending'),
          IsDescCol15 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 15, N'isdescending'),
          IsDescCol16 = INDEXKEY_PROPERTY(OBJECT_ID(@tablename), i.indid, 16, N'isdescending'),    
          IsCompCol1  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 1),  N'IsComputed'),
          IsCompCol2  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 2),  N'IsComputed'),
          IsCompCol3  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 3),  N'IsComputed'),
          IsCompCol4  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 4),  N'IsComputed'),
          IsCompCol5  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 5),  N'IsComputed'),
          IsCompCol6  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 6),  N'IsComputed'),
          IsCompCol7  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 7),  N'IsComputed'),
          IsCompCol8  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 8),  N'IsComputed'),
          IsCompCol9  = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 9),  N'IsComputed'),
          IsCompCol10 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 10), N'IsComputed'),
          IsCompCol11 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 11), N'IsComputed'),
          IsCompCol12 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 12), N'IsComputed'),
          IsCompCol13 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 13), N'IsComputed'),
          IsCompCol14 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 14), N'IsComputed'),
          IsCompCol15 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 15), N'IsComputed'),
          IsCompCol16 = COLUMNPROPERTY(OBJECT_ID(@tablename), INDEX_COL(@tablename, i.indid, 16), N'IsComputed'),    
          SegName          = s.groupname,
          IsFullTextKey    = INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsFulltextKey'),
          IsTable          = OBJECTPROPERTY(OBJECT_ID(@tablename), N'IsTable'),
          IsStatistics     = INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsStatistics'),
          IsAutoStatistics = INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsAutoStatistics'),
          IsHypothetical   = INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsHypothetical'),
          IsConstraint     = CASE WHEN c.constid IS NOT NULL THEN 1 ELSE 0 END    
        FROM
          dbo.sysindexes i 
          INNER JOIN  dbo.sysfilegroups s ON i.groupid = s.groupid
          LEFT OUTER JOIN dbo.sysconstraints c ON c.[id] = OBJECT_ID(@tablename) AND i.name = OBJECT_NAME(c.constid)
        WHERE
          i.id = OBJECT_ID(@tablename) 
          AND i.indid > 0 
          AND i.indid < 255 
          AND INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsStatistics') = 0 -- filter out statistics数据库 4
          AND INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsAutoStatistics') = 0 -- filter out statistics数据库 5
          AND INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsHypothetical') = 0 -- filter out statistics数据库 6
        ORDER BY
          i.indid";

        private const string SQL_GetTableKeys = "EXEC sp_MStablerefs @tablename, N'actualtables', N'both', null";

        private const string SQL_GetObjectData = "SELECT * FROM [{0}].[{1}]";

        private const string SQL_GetObjectSource = "EXEC sp_helptext @objectname";

        private const string SQL2005_GetColumnConstraints = @"
            SELECT
              object_name(const.constid) AS ConstraintName,
              CASE
                WHEN const.status & 5 = 5 THEN 'DEFAULT'
                WHEN const.status & 4 = 4 THEN 'CHECK'
                ELSE ''
              END AS ConstraintType,
              constdef.text AS ConstraintDef
            FROM
              dbo.sysobjects AS tbl
              INNER JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
              INNER JOIN dbo.sysconstraints const ON clmns.id = const.id and clmns.colid = const.colid
              LEFT OUTER JOIN dbo.syscomments constdef ON const.constid = constdef.id
            WHERE
              SCHEMA_NAME(tbl.uid) = @SchemaName
              AND tbl.[name] = @TableName
              AND clmns.name = @ColumnName
              AND (const.status & 4 = 4 OR const.status & 5 = 5)";

        private const string SQL2000_GetColumnConstraints = @"
            SELECT
              object_name(const.constid) AS ConstraintName,
              CASE
                WHEN const.status & 5 = 5 THEN 'DEFAULT'
                WHEN const.status & 4 = 4 THEN 'CHECK'
                ELSE ''
              END AS ConstraintType,
              constdef.text AS ConstraintDef
            FROM
              dbo.sysobjects AS tbl
              INNER JOIN dbo.sysusers AS stbl ON stbl.[uid] = tbl.[uid]
              INNER JOIN dbo.syscolumns AS clmns ON clmns.id=tbl.id
              INNER JOIN dbo.sysconstraints const ON clmns.id = const.id and clmns.colid = const.colid
              LEFT OUTER JOIN dbo.syscomments constdef ON const.constid = constdef.id
            WHERE
              stbl.[name] = @SchemaName
              AND tbl.[name] = @TableName
              AND clmns.name = @ColumnName
              AND (const.status & 4 = 4 OR const.status & 5 = 5)";

        private const string SQL2005_GetCommands = @"
                  SELECT
                      object_name(id) AS OBJECT_NAME,
                      schema_name(uid) AS USER_NAME,
                      crdate AS DATE_CREATED,
                      id as OBJECT_ID
                  FROM
                      sysobjects
                  WHERE
                      type = N'P'
                      AND permissions(id) & 32 <> 0 
                      AND ObjectProperty(id, N'IsMSShipped') = 0
                      AND NOT EXISTS (SELECT * FROM sys.extended_properties WHERE major_id = id AND name = 'microsoft_database_tools_support' AND value = 1)
                  ORDER BY object_name(id)";

        private const string SQL_GetCommands = @"
                  SELECT
                      object_name(id) AS OBJECT_NAME,
                      user_name(uid) AS USER_NAME,
                      crdate AS DATE_CREATED,
                      id as OBJECT_ID
                  FROM
                      sysobjects
                  WHERE
                      type = N'P'
                      AND permissions(id) & 32 <> 0 
                      AND ObjectProperty(id, N'IsMSShipped') = 0
          ORDER BY object_name(id)";

        private const string SQL_GetCommandParameters = @"EXEC sp_procedure_params_rowset @CommandName, 1, @SchemaName, NULL";

        private const string SQL2005_GetCommandParameters = @"
            SELECT
                DB_NAME() AS [PROCEDURE_CATALOG],
                @SchemaName AS [PROCEDURE_SCHEMA],
                NULL AS [PROCEDURE_NAME],
                '@RETURN_VALUE' AS [PARAMETER_NAME],
                0 AS [ORDINAL_POSITION],
                CAST(4 AS smallint) AS [PARAMETER_TYPE],
                0 AS [PARAMETER_HASDEFAULT],
                NULL AS [PARAMETER_DEFAULT],
                CAST(0 AS bit) AS [IS_NULLABLE],
                0 AS [DATA_TYPE],
                NULL AS [CHARACTER_MAXIMUM_LENGTH],
                NULL AS [CHARACTER_OCTET_LENGTH],
                CAST(10 AS smallint) AS [NUMERIC_PRECISION],
                CAST(NULL AS smallint) AS [NUMERIC_SCALE],
                NULL AS [DESCRIPTION],
                'int' AS [TYPE_NAME],
                'int' AS [LOCAL_TYPE_NAME]
            UNION ALL
            SELECT
                DB_NAME() AS [PROCEDURE_CATALOG],
                SCHEMA_NAME(sp.schema_id) AS [PROCEDURE_SCHEMA],
                NULL AS [PROCEDURE_NAME],
                param.name AS [PARAMETER_NAME],
                param.parameter_id AS [ORDINAL_POSITION],
                CAST(CASE WHEN param.is_output = 1 THEN 2 ELSE 1 END AS smallint) AS [PARAMETER_TYPE],
                0 AS [PARAMETER_HASDEFAULT],
                NULL AS [PARAMETER_DEFAULT],
                CAST(1 AS bit) AS [IS_NULLABLE],
                0 AS [DATA_TYPE],
                CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [CHARACTER_MAXIMUM_LENGTH],
                NULL AS [CHARACTER_OCTET_LENGTH],
                CAST(param.precision AS smallint) AS [NUMERIC_PRECISION],
                CAST(param.scale AS smallint) AS [NUMERIC_SCALE],
                NULL AS [DESCRIPTION],
                ISNULL(baset.name, N'') AS [TYPE_NAME],
                ISNULL(baset.name, N'') AS [LOCAL_TYPE_NAME]
            FROM
                sys.all_objects AS sp
                INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
                LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id
            WHERE
                (sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')and(sp.name=@CommandName and SCHEMA_NAME(sp.schema_id)=@SchemaName)
            ORDER BY
                5 ASC";

        private const string SQL_GetExtendedProperties = @"
            SELECT
                p.name AS PROPERTY_NAME,
                p.value AS PROPERTY_VALUE,
                SQL_VARIANT_PROPERTY(p.value,'BaseType') AS UNDERLYING_TYPE,
                SQL_VARIANT_PROPERTY(p.value,'MaxLength') AS CHARACTER_MAXIMUM_LENGTH,
                SQL_VARIANT_PROPERTY(p.value,'Precision') AS NUMERIC_PRECISION,
                SQL_VARIANT_PROPERTY(p.value,'Scale') AS NUMERIC_SCALE
            FROM
                ::fn_listextendedproperty(NULL, @level0type, @level0name, @level1type, @level1name, @level2type, @level2name) p";

        private const string SQL_GetSqlServerVersion = "EXEC master.dbo.xp_msver ProductVersion";
        #endregion

     

     2.     MySql

               2.1     GetTables

"SELECT TABLE_NAME, '' OWNER, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_TYPE = 'BASE TABLE' ORDER BY 1"

               2.2     GetTableColumns

"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION,"
                  " NUMERIC_SCALE, CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE, COLUMN_TYPE"
                  " FROM INFORMATION_SCHEMA.COLUMNS"
                  " WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"
                  " ORDER BY ORDINAL_POSITION"

               2.3     GetViews

"SELECT TABLE_NAME, '' OWNER, CREATE_TIME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{0}' AND TABLE_TYPE = 'VIEW' ORDER BY 1"

               2.4     GetViewColumns

"SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION,"
                  " NUMERIC_SCALE, CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE, COLUMN_TYPE"
                  " FROM INFORMATION_SCHEMA.COLUMNS "
                  "WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"
                  "ORDER BY ORDINAL_POSITION"

               2.5     GetTablePrimaryKey

"SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME"
                  " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1"
                  "  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2"
                  "  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA"
                  "  AND t2.TABLE_NAME = t1.TABLE_NAME"
                  "  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME"
                  " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}'"
                  " AND t2.CONSTRAINT_TYPE = 'PRIMARY KEY'"
                  " ORDER BY t1.ORDINAL_POSITION"

               2.6     GetTableIndexes

"SELECT INDEX_NAME, COUNT(*) AS COLUMN_COUNT, MAX(NON_UNIQUE) NON_UNIQUE,"
                  " CASE INDEX_NAME WHEN 'PRIMARY' THEN 1 ELSE 0 END IS_PRIMARY"
                  " FROM INFORMATION_SCHEMA.STATISTICS"
                  " WHERE  TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"
                  " GROUP BY INDEX_NAME"
                  " ORDER BY INDEX_NAME;"

                  " SELECT INDEX_NAME, COLUMN_NAME"
                  " FROM INFORMATION_SCHEMA.STATISTICS"
                  " WHERE  TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"
                  " ORDER BY INDEX_NAME, SEQ_IN_INDEX;"

               2.7     GetTableKeys 注意这里分别调用 2.7.1和2.7.2才干一切取到

                    2.7.1     GetMyTableKeys

"SELECT CONSTRAINT_NAME"
                  " FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1"
                  " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}'"
                  "  AND CONSTRAINT_TYPE = 'FOREIGN KEY';"

                  " SELECT t1.CONSTRAINT_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT,"
                  "  t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME"
                  " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1"
                  "  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2"
                  "  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA"
                  "  AND t2.TABLE_NAME = t1.TABLE_NAME"
                  "  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME"
                  " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.TABLE_NAME = '{1}'"
                  "  AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY'"
                  " ORDER BY t1.CONSTRAINT_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT"

                    2.7.2     GetOthersTableKeys

"SELECT DISTINCT CONSTRAINT_NAME"
                  " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1"
                  " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.REFERENCED_TABLE_NAME = '{1}';"

                  " SELECT t1.CONSTRAINT_NAME, t1.TABLE_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT,"
                  "  t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME"
                  " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1"
                  "  INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2"
                  "  ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA"
                  "  AND t2.TABLE_NAME = t1.TABLE_NAME"
                  "  AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME"
                  " WHERE t1.TABLE_SCHEMA = '{0}' AND t1.REFERENCED_TABLE_NAME = '{1}'"
                  "  AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY'"
                  " ORDER BY t1.CONSTRAINT_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT"

          2.8     GetTableData     "SELECT * FROM {0}"          

          2.9     GetViewData     "SELECT * FROM {0}"

          2.10     GetViewText

"SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"

          2.11     GetCommands     string.Format参数:数据库名

"SELECT ROUTINE_NAME, '' OWNER, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_TYPE = 'PROCEDURE' ORDER BY 1"

          2.12     GetCommandParameters     >_<  ,未有提供,呈现:throw new NotSupportedException("GetCommandParameters() is not supported in this release.");

          2.13     GetCommandText

"SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '{0}' AND ROUTINE_NAME = '{1}'"

 

     3.     Oracle

               3.1     GetTables

"SELECT owner, object_name, created FROM all_objects WHERE (owner in ( select USERNAME from user_users  )) AND object_type = 'TABLE'      ORDER BY owner,    object_name"

               3.2     GetTableColumns

@"select cols.column_name, 
                             cols.data_type, 
                             cols.data_length,
                             cols.data_precision, 
                             cols.data_scale,
                             cols.nullable,        
                             cmts.comments
                      from  all_tab_columns cols, 
                            all_col_comments cmts 
                      where 
                            cols.owner = '{0}'
                        and cols.table_name = '{1}'
                        and cols.owner = cmts.owner 
                        and cols.table_name = cmts.table_name 
                        and cols.column_name = cmts.column_name
                        order by column_id"

               3.3     GetViews

@"select 
                    v.owner, v.view_name, o.created
                from all_views   v,
                    all_objects o 
                where v.view_name = o.object_name 
                and o.object_type = 'VIEW' 
                and (v.owner in ( select USERNAME from user_users  ))
                order by v.owner, v.view_name"

                3.4     GetViewColumns

@"select cols.column_name, 
                             cols.data_type, 
                             cols.data_length,
                             cols.data_precision, 
                             cols.data_scale,
                             cols.nullable,        
                             cmts.comments
                      from  all_tab_columns cols, 
                            all_col_comments cmts 
                      where 
                            cols.owner = '{0}'
                        and cols.table_name = '{1}'
                        and cols.owner = cmts.owner 
                        and cols.table_name = cmts.table_name 
                        and cols.column_name = cmts.column_name
                        order by column_id"

               3.5     GetTablePrimaryKey

@"
                    select 
                        cols.constraint_name, 
                        cols.column_name, 
                        cols.position 
                    from
                        all_constraints     cons,
                        all_cons_columns    cols
                    where 
                        cons.OWNER = '{0}'
                        and cons.table_name = '{1}'
                        and cons.constraint_type='P'
                        and cols.owner = cons.owner
                        and cols.table_name = cons.table_name   
                        and cols.constraint_name = cons.constraint_name 
                    order by cons.constraint_name, cols.position"

               3.6     GetTableIndexes

@"
                select        idx.owner, idx.uniqueness, con.constraint_type, idx.table_type, col.*
                from        all_ind_columns col,
                            all_indexes idx,
                            all_constraints con
                where        idx.table_owner = '{0}'
                            AND idx.table_name = '{1}'
                            AND idx.owner = col.index_owner
                            AND idx.index_name = col.index_name
                            AND idx.owner = con.owner ( )
                            AND idx.table_name = con.table_name( )
                            AND idx.index_name = con.constraint_name( )"

               3.7     GetTableKeys

@"select 
                    cols.constraint_name, 
                    cols.column_name, 
                    cols.position, 
                    r_cons.table_name related_table_name, 
                    r_cols.column_name related_column_name 
                from
                    all_constraints     cons,
                    all_cons_columns    cols,
                    all_constraints     r_cons,
                    all_cons_columns    r_cols
                where cons.OWNER = '{0}'
                  and cons.table_name = '{1}'
                  and cons.constraint_type='R'
                  and cols.owner = cons.owner
                  and cols.table_name = cons.table_name   
                  and cols.constraint_name = cons.constraint_name 
                  and r_cols.owner = cons.r_owner 
                  and r_cols.constraint_name = cons.r_constraint_name 
                  and r_cons.owner = r_cols.owner 
                  and r_cons.table_name = r_cols.table_name 
                  and r_cons.constraint_name = r_cols.constraint_name 
                order by cons.constraint_name, cols.position"

               3.8     GetTableData     "SELECT * FROM {0}.{1}"               

               3.9     GetViewData     "SELECT * FROM {0}.{1}"

               3.10     GetViewText     

@"select        text
                from        all_views
                where        owner = '{0}'
                            and view_name = '{1}'"

               3.11     GetCommands

@"    select methods.owner, 
                            methods.package_name, 
                            methods.object_name, 
                            methods.overload,
                            ao.object_type,
                            ao.created,
                            ao.status,
                            ao.object_id
                        from
                        (select distinct owner, package_name, object_name, overload, object_id from ALL_ARGUMENTS 
                            where (owner in ( select USERNAME from user_users  ))
                            ) methods,
                            all_objects ao
                        where ao.object_id = methods.object_id    
                        order by methods.owner, methods.package_name, methods.object_name"

               3.12     GetCommandParameters

@"select 
                        ARGUMENT_NAME, 
                        POSITION, 
                        SEQUENCE, 
                        DATA_LEVEL, 
                        DATA_TYPE, 
                        IN_OUT, 
                        DATA_LENGTH, 
                        DATA_PRECISION, 
                        DATA_SCALE  
                    from ALL_ARGUMENTS 
                    where object_ID={0}
                    and object_name = '{1}'
                    and {2}
                    order by position"

                    备注:{2}参数 源码是:overload > 0 ? "overload = " overload : "overload is null",由于本身对Oracle并不纯熟,并且翻了几许素材,得知这一个是超重参数的安装,熟习的人温馨来配啊,有相通之人劳烦告知一下此处暗中认可语句该怎么铺排。

               3.13     GetCommandText  >_< 未有提供!音讯如下:throw new NotImplementedException("Retrieval of command text has not yet been implemented.");

 

结束

     对于元数据的收获,用获得数据库结构的奥义......(无码,完全版,未删节)的稿子也不易,不过没法获得暗中同意值,所以我们依据本人的渴求选拔获取的法门就行了。由于处在Ctrl C和Ctrl V,难免脑袋有些发麻,发掘破绽百出的情状请速报,以便及时更新: )

 

注意

     本文的SQL语句是从来完全拷贝的源代码,SQLSE奥迪Q3VER 3000许多测量检验没不时,别的的请自行测量检验 !

 

ps:     本来是想把代码折叠一下的,不过那样方便拷贝:)

本文由www.bifa365365.com发布于数据库,转载请注明出处:表结构音讯查询数据库,SQL语句出自CodeSmith

关键词: www.bifa3653