^全^ 获取SQL SERVER2000/2005、MySql、Oracle元数据的SQL语句(2)

更新时间：2022-10-04 21:05:37

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 ^全^ 获取SQL SERVER2000/2005、MySql、Oracle元数据的SQL语句(2)
          AND INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsAutoStatistics') = 0 -- filter out statistics
          AND INDEXPROPERTY(OBJECT_ID(@tablename), i.name, N'IsHypothetical') = 0 -- filter out statistics ^全^ 获取SQL SERVER2000/2005、MySql、Oracle元数据的SQL语句(2)
        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

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语句是直接完全拷贝的源代码，SQLSERVER 2000大部分测试没有问题，其他的请自行测试！

ps: 本来是想把代码折叠一下的，但是这样方便拷贝:)

本文转自over140 51CTO博客，原文链接：http://blog.51cto.com/over140/586715，如需转载请自行联系原作者

上一篇 : ：ASP.NET Cookies简单应用 [ASP.NET | Cookies]下一篇 : AndEngine引擎学习之环境配置以及示例修改

^全^ 获取SQL SERVER2000/2005、MySql、Oracle元数据的SQL语句(2)

相关阅读

推荐文章