前言
有很多朋友都比較關心代碼自動生成，理所當然離不開中繼資料了，但是對于擷取中繼資料的方法不一。由于最近我也在寫代碼生成，對中繼資料的擷取sql語句并不齊全，意外的想到了codesmith，它也可以根據中繼資料來生成三層的，于是乎習慣性的去找他的源碼了，果然沒有失望，我們在schemaproviders目錄下能看到如下檔案：
schemaexplorer.adoxschemaprovider.dll
schemaexplorer.mysqlschemaprovider.dll
schemaexplorer.oracleschemaprovider.dll
schemaexplorer.sqlschemaprovider.dll
沒有加密！但是schemaexplorer.sqlschemaprovider.dll混淆了！用reflector檢視源代碼仍然能看到sql語句，于是乎拷貝出來，替換\t\n，弄了我兩個小時才完，今天一搜，原來有源碼- - ！！超級郁悶！！大夥就别走我的彎路了吧！源碼在samples\projects\目錄下。下面開始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 = @"
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
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
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
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
(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 = @"
object_name(id) as object_name,
schema_name(uid) as user_name,
type as type,
crdate as date_created,
id as object_id
sysobjects
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 = @"
user_name(uid) as user_name,
order by object_name(id)";
private const string sql2000_getviewcolumns = @"
when cols.domain_name is not null then cols.domain_name collate latin1_general_bin
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
and cols.table_name = @viewname
private const string sql_getviewcolumns = @"
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
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 = @"
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
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
schema_name(tbl.uid) = @schemaname
and clmns.name = @columnname
and (const.status & 4 = 4 or const.status & 5 = 5)";
private const string sql2000_getcolumnconstraints = @"
inner join dbo.sysusers as stbl on stbl.[uid] = tbl.[uid]
stbl.[name] = @schemaname
private const string sql2005_getcommands = @"
type = n'p'
and permissions(id) & 32 <> 0
private const string sql_getcommands = @"
private const string sql_getcommandparameters = @"exec sp_procedure_params_rowset @commandname, 1, @schemaname, null";
private const string sql2005_getcommandparameters = @"
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
schema_name(sp.schema_id) as [procedure_schema],
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],
cast(1 as bit) as [is_nullable],
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],
cast(param.precision as smallint) as [numeric_precision],
cast(param.scale as smallint) as [numeric_scale],
isnull(baset.name, n'') as [type_name],
isnull(baset.name, n'') as [local_type_name]
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
(sp.type = n'p' or sp.type = n'rf' or sp.type='pc')and(sp.name=@commandname and schema_name(sp.schema_id)=@schemaname)
5 asc";
private const string sql_getextendedproperties = @"
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
::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
+ " 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"
+ " 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"
+ " and constraint_type = 'foreign key';"
+ " select t1.constraint_name, t1.column_name, t1.position_in_unique_constraint,"
+ " t1.referenced_table_name, referenced_column_name"
+ " and t2.constraint_type = 'foreign key'"
+ " order by t1.constraint_name, t1.position_in_unique_constraint"
2.7.2 getotherstablekeys
"select distinct constraint_name"
+ " 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,"
+ " where t1.table_schema = '{0}' and t1.referenced_table_name = '{1}'"
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
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
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.");
結束
注意
本文的sql語句是直接完全拷貝的源代碼，sqlserver 2000大部分測試沒有問題，其他的請自行測試 ！
ps: 本來是想把代碼折疊一下的，但是這樣友善拷貝:)
轉載：http://www.cnblogs.com/over140/archive/2008/12/23/1360150.html