^全^ 获取SQL SERVER2000/2005、MySql、Oracle元数据的SQL语句(2)
FROMdbo.sysindexes i
INNER JOINdbo.sysfilegroups s ON i.groupid = s.groupid
LEFT OUTER JOIN dbo.sysconstraints c ON c. = 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
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
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. = @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. = 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
stbl. = @SchemaName
AND tbl. = @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) & 320
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) & 320
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 ,
@SchemaName AS ,
NULL AS ,
'@RETURN_VALUE' AS ,
0 AS ,
CAST(4 AS smallint) AS ,
0 AS ,
NULL AS ,
CAST(0 AS bit) AS ,
0 AS ,
NULL AS ,
NULL AS ,
CAST(10 AS smallint) AS ,
CAST(NULL AS smallint) AS ,
NULL AS ,
'int' AS ,
'int' AS
UNION ALL
SELECT
DB_NAME() AS ,
SCHEMA_NAME(sp.schema_id) AS ,
NULL AS ,
param.name AS ,
param.parameter_id AS ,
CAST(CASE WHEN param.is_output = 1 THEN 2 ELSE 1 END AS smallint) AS ,
0 AS ,
NULL AS ,
CAST(1 AS bit) AS ,
0 AS ,
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 ,
NULL AS ,
CAST(param.precision AS smallint) AS ,
CAST(param.scale AS smallint) AS ,
NULL AS ,
ISNULL(baset.name, N'') AS ,
ISNULL(baset.name, N'') AS
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"+ " WHERETABLE_SCHEMA = '{0}' AND TABLE_NAME = '{1}'"+ " GROUP BY INDEX_NAME"+ " ORDER BY INDEX_NAME;"+ " SELECT INDEX_NAME, COLUMN_NAME"+ " FROM INFORMATION_SCHEMA.STATISTICS"+ " WHERETABLE_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> 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
fromall_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
fromall_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语句是直接完全拷贝的源代码，SQLSERVER 2000大部分测试没有问题，其他的请自行测试 ！
ps: 本来是想把代码折叠一下的，但是这样方便拷贝:)
页:
[1]