^全^ 获取SQL SERVER2000/2005、MySql、Oracle元数据的SQL语句 [SQL语句来自CodeSmith]-白红宇

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

本文转自博客园农民伯伯的博客，原文链接：^全^ 获取SQL SERVER2000/2005、MySql、Oracle元数据的SQL语句 [SQL语句来自CodeSmith]，如需转载请自行联系原博主。