protected override IEnumerable <ViewColumn> GetViewColumns(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS T JOIN INFORMATION_SCHEMA.VIEWS V ON V.TABLE_NAME = T.TABLE_NAME WHERE (T.TABLE_SCHEMA = '{connpar.Database}') AND (T.TABLE_NAME = ?TABLENAME OR ?TABLENAME IS NULL) AND (T.COLUMN_NAME = ?COLUMNNAME OR ?COLUMNNAME IS NULL) ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(ViewColumn.ViewName)) .Parameterize(parameters, "COLUMNNAME", nameof(ViewColumn.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ViewColumn { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), ViewName = wrapper.GetString(reader, 2), Name = wrapper.GetString(reader, 3) })); }
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_TYPE, C.TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES T JOIN ( SELECT RELNAME AS TABLE_NAME,CAST(OBJ_DESCRIPTION(RELFILENODE, 'pg_class') AS VARCHAR) AS TABLE_COMMENT FROM PG_CLASS C WHERE RELKIND = 'r' AND RELNAME NOT LIKE 'pg_%' AND RELNAME NOT LIKE 'sql_%' ) C ON T.TABLE_NAME = C.TABLE_NAME WHERE (TABLE_CATALOG = '{connpar.Database}') AND (T.TABLE_NAME = :NAME OR :NAME IS NULL) ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME"; restrictionValues .Parameterize(parameters, "NAME", nameof(Table.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Table { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), Type = wrapper.GetString(reader, 3) == "BASE TABLE" ? TableType.BaseTable : TableType.SystemTable, Description = wrapper.GetString(reader, 4) })); }
protected override IEnumerable <Procedure> GetProcedures(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT SPECIFIC_NAME, ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE (ROUTINE_SCHEMA = '{connpar.Database}') AND (ROUTINE_NAME = @NAME OR (@NAME IS NULL)) AND (ROUTINE_TYPE = @TYPE OR (@TYPE IS NULL)) ORDER BY ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME"; restrictionValues .Parameterize(parameters, "NAME", nameof(Procedure.Name)) .Parameterize(parameters, "TYPE", nameof(Procedure.Type)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Procedure { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), Type = wrapper.GetString(reader, 6) })); }
protected override IEnumerable <Index> GetIndexs(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT DISTINCT DB_NAME() AS CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA = USER_NAME(O.UID), CONSTRAINT_NAME = X.NAME, TABLE_CATALOG = DB_NAME(), TABLE_SCHEMA = USER_NAME(O.UID), TABLE_NAME = O.NAME, INDEX_NAME = X.NAME FROM SYSOBJECTS O, SYSINDEXES X, SYSINDEXKEYS XK WHERE O.TYPE IN ('U') AND X.ID = O.ID AND O.ID = XK.ID AND X.INDID = XK.INDID AND XK.KEYNO < = X.KEYCNT AND (DB_NAME() = '{connpar.Database}') AND (O.NAME = @TABLE OR (@TABLE IS NULL)) AND (X.NAME = @NAME OR (@NAME IS NULL)) ORDER BY TABLE_NAME, INDEX_NAME"; restrictionValues .Parameterize(parameters, "TABLE", nameof(Index.TableName)) .Parameterize(parameters, "NAME", nameof(Index.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Index { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), TableName = wrapper.GetString(reader, 5), Name = wrapper.GetString(reader, 6) })); }
protected override IEnumerable <View> GetViews(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, (SELECT VALUE FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description','user',t.TABLE_SCHEMA,'view',T.TABLE_NAME,NULL,NULL)) COMMENTS FROM INFORMATION_SCHEMA.TABLES T WHERE TABLE_TYPE = 'view' AND (T.TABLE_CATALOG = '{connpar.Database}') AND (T.TABLE_NAME = @NAME OR (@NAME IS NULL)) ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME"; restrictionValues.Parameterize(parameters, "NAME", nameof(View.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new View { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), Description = wrapper.GetString(reader, 3) })); }
protected override IEnumerable <ViewColumn> GetViewColumns(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE (VIEW_CATALOG = '{connpar.Database}') AND (VIEW_NAME = @TABLE OR (@TABLE IS NULL)) AND (COLUMN_NAME = @COLUMN OR (@COLUMN IS NULL)) ORDER BY VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME"; restrictionValues .Parameterize(parameters, "TABLE", nameof(ViewColumn.ViewName)) .Parameterize(parameters, "COLUMN", nameof(ViewColumn.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ViewColumn { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), ViewName = wrapper.GetString(reader, 2), Name = wrapper.GetString(reader, 6) })); }
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES T WHERE (T.TABLE_SCHEMA = '{connpar.Database}') AND T.TABLE_TYPE <> 'VIEW' AND (T.TABLE_NAME = ?NAME OR ?NAME IS NULL) AND ((T.TABLE_TYPE = 'BASE TABLE' AND (@TABLETYPE IS NULL OR @TABLETYPE = 0)) OR (T.TABLE_TYPE = 'SYSTEM TABLE' AND @TABLETYPE = 1)) ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME"; restrictionValues .Parameterize(parameters, "NAME", nameof(Table.Name)) .Parameterize(parameters, "TABLETYPE", nameof(Table.Type)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Table { Schema = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), Type = wrapper.GetString(reader, 3) == "BASE TABLE" ? TableType.BaseTable : TableType.SystemTable, Description = wrapper.GetString(reader, 4) })); }
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.TABLE_TYPE, (SELECT VALUE FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description','user',T.TABLE_SCHEMA,'table',T.TABLE_NAME,NULL,NULL)) COMMENTS FROM INFORMATION_SCHEMA.TABLES T WHERE TABLE_TYPE <> 'view' AND (T.TABLE_CATALOG = '{connpar.Database}') AND (T.TABLE_NAME = @NAME OR (@NAME IS NULL)) AND ((T.TABLE_TYPE = 'BASE TABLE' AND (@TABLETYPE IS NULL OR @TABLETYPE = 0)) OR (T.TABLE_TYPE = 'SYSTEM TABLE' AND @TABLETYPE = 1)) ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME"; restrictionValues .Parameterize(parameters, "NAME", nameof(Table.Name)) .Parameterize(parameters, "TABLETYPE", nameof(Table.Type)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Table { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), Type = wrapper.GetString(reader, 3) == "BASE TABLE" ? TableType.BaseTable : TableType.SystemTable, Description = wrapper.GetString(reader, 4) })); }
protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COL.ORDINAL_POSITION, COL.COLUMN_NAME, COL.DATA_TYPE, COL.CHARACTER_MAXIMUM_LENGTH, COL.NUMERIC_PRECISION, COL.NUMERIC_SCALE, COL.IS_NULLABLE, COL.COLUMN_DEFAULT , DES.DESCRIPTION, (CASE WHEN COL.ORDINAL_POSITION = CON.CONKEY[1] THEN 'YES' ELSE 'NO' END) IS_KEY FROM INFORMATION_SCHEMA.COLUMNS COL JOIN ( SELECT RELNAME AS TABLE_NAME,CAST(OBJ_DESCRIPTION(RELFILENODE, 'pg_class') AS VARCHAR) AS TABLE_COMMENT FROM PG_CLASS C WHERE RELKIND = 'r' AND RELNAME NOT LIKE 'pg_%' AND RELNAME NOT LIKE 'sql_%' ) C ON col.TABLE_NAME = C.TABLE_NAME LEFT JOIN PG_CLASS PCL ON PCL.RELNAME = COL.TABLE_NAME LEFT JOIN PG_DESCRIPTION DES ON PCL.OID = DES.OBJOID AND COL.ORDINAL_POSITION = PCL.OID LEFT JOIN PG_CONSTRAINT CON ON CON.CONRELID = DES.OBJOID WHERE (COL.TABLE_CATALOG = '{connpar.Database}') AND (COL.TABLE_NAME = :TABLENAME OR :TABLENAME IS NULL) AND (COL.COLUMN_NAME = :COLUMNNAME OR :COLUMNNAME IS NULL) ORDER BY COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COL.ORDINAL_POSITION"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(Column.TableName)) .Parameterize(parameters, "COLUMNNAME", nameof(Column.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Column { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), TableName = wrapper.GetString(reader, 2), Name = wrapper.GetString(reader, 4), DataType = wrapper.GetString(reader, 5), Length = wrapper.GetInt64(reader, 6), NumericPrecision = wrapper.GetInt32(reader, 7), NumericScale = wrapper.GetInt32(reader, 8), IsNullable = wrapper.GetString(reader, 9) == "YES", IsPrimaryKey = wrapper.GetString(reader, 12) == "YES", Default = wrapper.GetString(reader, 10), Description = wrapper.GetString(reader, 11), })); }
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT * FROM ( SELECT T.OWNER, T.TABLE_NAME, DECODE(T.OWNER, 'SYS', 'SYSTEM', 'SYSTEM', 'SYSTEM', 'SYSMAN', 'SYSTEM', 'CTXSYS', 'SYSTEM', 'MDSYS', 'SYSTEM', 'OLAPSYS', 'SYSTEM', 'ORDSYS', 'SYSTEM', 'OUTLN', 'SYSTEM', 'WKSYS', 'SYSTEM', 'WMSYS', 'SYSTEM', 'XDB', 'SYSTEM', 'ORDPLUGINS', 'SYSTEM', 'USER') AS TYPE, C.COMMENTS FROM ALL_TABLES T JOIN ALL_TAB_COMMENTS C ON T.OWNER = C.OWNER AND T.TABLE_NAME = C.TABLE_NAME ) T WHERE (T.OWNER = '{connpar.UserId.ToUpper()}') AND (T.TABLE_NAME = :TABLENAME OR (:TABLENAME IS NULL)) AND ((T.TYPE = 'USER' AND (:TABLETYPE IS NULL OR :TABLETYPE = 0)) OR (T.TYPE = 'SYSTEM' AND :TABLETYPE = 1)) ORDER BY OWNER, TABLE_NAME"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(Table.Name)) .Parameterize(parameters, "TABLETYPE", nameof(Table.Type)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Table { Schema = wrapper.GetString(reader, 0), Name = wrapper.GetString(reader, 1), Type = wrapper.GetString(reader, 2) == "USER" ? TableType.BaseTable : TableType.SystemTable, Description = wrapper.GetString(reader, 3) })); }
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT PKCON.CONSTRAINT_NAME AS PRIMARY_KEY_CONSTRAINT_NAME, PKCON.OWNER AS PRIMARY_KEY_OWNER, PKCON.TABLE_NAME AS PRIMARY_KEY_TABLE_NAME, FKCON.OWNER AS FOREIGN_KEY_OWNER, FKCON.CONSTRAINT_NAME AS FOREIGN_KEY_CONSTRAINT_NAME, FKCON.TABLE_NAME AS FOREIGN_KEY_TABLE_NAME, FKCON.SEARCH_CONDITION, FKCON.R_OWNER, FKCON.R_CONSTRAINT_NAME, FKCON.DELETE_RULE, FKCON.STATUS, (SELECT cu.COLUMN_NAME FROM ALL_CONS_COLUMNS CU, ALL_CONSTRAINTS AU WHERE CU.OWNER = AU.OWNER AND CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = 'P' and au.constraint_name = FKCON.r_constraint_name and FKCON.owner = au.OWNER and rownum = 1) PRIMARY_KEY_COLUMN_NAME, (SELECT cu.COLUMN_NAME FROM ALL_CONS_COLUMNS CU, ALL_CONSTRAINTS AU WHERE CU.OWNER = AU.OWNER AND CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = 'R' and au.constraint_name = FKCON.CONSTRAINT_NAME and PKCON.owner = au.OWNER and rownum = 1) FOREIGN_KEY_COLUMN_NAME FROM ALL_CONSTRAINTS FKCON, ALL_CONSTRAINTS PKCON WHERE PKCON.OWNER = FKCON.R_OWNER AND PKCON.CONSTRAINT_NAME = FKCON.R_CONSTRAINT_NAME AND FKCON.CONSTRAINT_TYPE = 'R' and (FKCON.OWNER = '{connpar.UserId.ToUpper()}') AND (FKCON.TABLE_NAME = :TABLENAME OR :TABLENAME is null) AND (FKCON.CONSTRAINT_NAME = :CONSTRAINTNAME OR :CONSTRAINTNAME is null)"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName)) .Parameterize(parameters, "CONSTRAINTNAME", nameof(ForeignKey.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ForeignKey { Schema = reader["FOREIGN_KEY_OWNER"].ToString(), Name = reader["FOREIGN_KEY_CONSTRAINT_NAME"].ToString(), TableName = reader["FOREIGN_KEY_TABLE_NAME"].ToString().Replace("\"", ""), ColumnName = reader["FOREIGN_KEY_COLUMN_NAME"].ToString(), PKTable = reader["PRIMARY_KEY_TABLE_NAME"].ToString(), PKColumn = reader["PRIMARY_KEY_COLUMN_NAME"].ToString() })); }
protected override IEnumerable <ProcedureParameter> GetProcedureParameters(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, PARAMETER_NAME, CASE WHEN DATA_TYPE IS NULL THEN USER_DEFINED_TYPE_NAME WHEN DATA_TYPE = 'TABLE TYPE' THEN USER_DEFINED_TYPE_NAME ELSE DATA_TYPE END AS DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, INTERVAL_PRECISION FROM INFORMATION_SCHEMA.PARAMETERS WHERE (SPECIFIC_CATALOG = '{connpar.Database}') AND (SPECIFIC_NAME = @NAME OR (@NAME IS NULL)) AND (PARAMETER_NAME = @PARAMETER OR (@PARAMETER IS NULL)) ORDER BY SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_NAME"; restrictionValues .Parameterize(parameters, "NAME", nameof(ProcedureParameter.ProcedureName)) .Parameterize(parameters, "PARAMETER", nameof(ProcedureParameter.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ProcedureParameter { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), ProcedureName = wrapper.GetString(reader, 2), Name = wrapper.GetString(reader, 7), Direction = wrapper.GetString(reader, 5) == "YES" ? ParameterDirection.ReturnValue : (wrapper.GetString(reader, 4) == "IN" ? ParameterDirection.Input : ParameterDirection.Output), NumericPrecision = wrapper.GetInt32(reader, 17), NumericScale = wrapper.GetInt32(reader, 19), DataType = wrapper.GetString(reader, 8), Length = wrapper.GetInt64(reader, 9) })); }
protected override IEnumerable <User> GetUsers(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); SqlCommand sql = "SELECT HOST, USER FROM MYSQL.USER WHERE (NAME = ?NAME OR ?NAME IS NULL)"; restrictionValues.Parameterize(parameters, "NAME", nameof(User.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new User { Name = wrapper.GetString(reader, 1) })); }
protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.COLUMN_NAME, T.DATA_TYPE AS DATATYPE, T.CHARACTER_MAXIMUM_LENGTH AS LENGTH, T.NUMERIC_PRECISION AS PRECISION, T.NUMERIC_SCALE AS SCALE, T.IS_NULLABLE AS NULLABLE, (SELECT COUNT(1) FROM SYSCOLUMNS A JOIN SYSINDEXKEYS B ON A.ID=B.ID AND A.COLID=B.COLID AND A.ID=OBJECT_ID(T.TABLE_NAME) JOIN SYSINDEXES C ON A.ID=C.ID AND B.INDID=C.INDID JOIN SYSOBJECTS D ON C.NAME=D.NAME AND D.XTYPE= 'PK' WHERE A.NAME = T.COLUMN_NAME) COLUMN_IS_PK, T.COLUMN_DEFAULT, (SELECT VALUE FROM ::FN_LISTEXTENDEDPROPERTY('MS_Description','user',T.TABLE_SCHEMA,'table',T.TABLE_NAME,'column',T.COLUMN_NAME)) COMMENTS, (SELECT C.COLSTAT FROM SYSCOLUMNS C LEFT JOIN SYSOBJECTS O ON C.ID = O.ID WHERE O.XTYPE='U' AND O.NAME = T.TABLE_NAME AND C.NAME = T.COLUMN_NAME) AUTOINC FROM INFORMATION_SCHEMA.COLUMNS T JOIN INFORMATION_SCHEMA.TABLES O ON O.TABLE_CATALOG = T.TABLE_CATALOG AND O.TABLE_SCHEMA = T.TABLE_SCHEMA AND T.TABLE_NAME = O.TABLE_NAME WHERE (O.TABLE_TYPE <> 'view' AND T.TABLE_CATALOG = '{connpar.Database}') AND (T.TABLE_NAME = @TABLENAME OR (@TABLENAME IS NULL)) AND (T.COLUMN_NAME = @COLUMNNAME OR (@COLUMNNAME IS NULL)) ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(Column.TableName)) .Parameterize(parameters, "COLUMNNAME", nameof(Column.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Column { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), TableName = wrapper.GetString(reader, 2), Name = wrapper.GetString(reader, 3), DataType = wrapper.GetString(reader, 4), Length = wrapper.GetInt64(reader, 5), NumericPrecision = wrapper.GetInt32(reader, 6), NumericScale = wrapper.GetInt32(reader, 7), IsNullable = wrapper.GetString(reader, 8) == "YES", IsPrimaryKey = wrapper.GetInt32(reader, 9) > 0, Default = wrapper.GetString(reader, 10), Description = wrapper.GetString(reader, 11), Autoincrement = wrapper.GetInt32(reader, 12) == 1 })); }
protected override IEnumerable <Database> GetDatabases(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); SqlCommand sql = @" SELECT DATNAME FROM PG_DATABASE WHERE (DATNAME = @NAME OR (@NAME IS NULL))"; restrictionValues.Parameterize(parameters, "NAME", nameof(Database.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Database { Name = wrapper.GetString(reader, 0) })); }
protected override IEnumerable <Database> GetDatabases(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); SqlCommand sql = @" SELECT NAME AS DATABASE_NAME, CRDATE AS CREATE_DATE FROM MASTER..SYSDATABASES WHERE (NAME = @NAME OR (@NAME IS NULL))"; restrictionValues.Parameterize(parameters, "NAME", nameof(Database.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Database { Name = wrapper.GetString(reader, 0), CreateDate = wrapper.GetDateTime(reader, 1) })); }
protected override IEnumerable <User> GetUsers(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); SqlCommand sql = @" SELECT UID, NAME AS USER_NAME, CREATEDATE, UPDATEDATE FROM SYSUSERS WHERE (NAME = @NAME OR (@NAME IS NULL))"; restrictionValues.Parameterize(parameters, "NAME", nameof(User.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new User { Name = wrapper.GetString(reader, 1), CreateDate = wrapper.GetDateTime(reader, 2) })); }
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT TC.CONSTRAINT_CATALOG, TC.CONSTRAINT_SCHEMA, TC.CONSTRAINT_NAME, TC.TABLE_NAME, C.COLUMN_NAME, FKCU.TABLE_NAME REFERENCED_TABLE_NAME, FKCU.COLUMN_NAME AS REFERENCED_COLUMN_NAME FROM [INFORMATION_SCHEMA].[COLUMNS] C LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] KCU ON KCU.TABLE_SCHEMA = C.TABLE_SCHEMA AND KCU.TABLE_NAME = C.TABLE_NAME AND KCU.COLUMN_NAME = C.COLUMN_NAME LEFT JOIN [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] TC ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME LEFT JOIN [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] FC ON KCU.CONSTRAINT_SCHEMA = FC.CONSTRAINT_SCHEMA AND KCU.CONSTRAINT_NAME = FC.CONSTRAINT_NAME LEFT JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] FKCU ON FKCU.CONSTRAINT_SCHEMA = FC.UNIQUE_CONSTRAINT_SCHEMA AND FKCU.CONSTRAINT_NAME = FC.UNIQUE_CONSTRAINT_NAME WHERE TC.CONSTRAINT_TYPE = 'FOREIGN KEY' AND (TC.CONSTRAINT_CATALOG = '{connpar.Database}') AND (TC.TABLE_NAME = @TABLENAME OR @TABLENAME IS NULL) AND (TC.CONSTRAINT_NAME = @NAME OR @NAME IS NULL)"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName)) .Parameterize(parameters, "NAME", nameof(ForeignKey.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ForeignKey { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), TableName = wrapper.GetString(reader, 3), ColumnName = wrapper.GetString(reader, 4), PKTable = wrapper.GetString(reader, 5), PKColumn = wrapper.GetString(reader, 6), })); }
protected override IEnumerable <ViewColumn> GetViewColumns(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.COLUMN_NAME, T.DATA_TYPE, T.CHARACTER_MAXIMUM_LENGTH, T.NUMERIC_PRECISION, T.NUMERIC_SCALE, T.IS_NULLABLE, T.COLUMN_KEY, T.COLUMN_DEFAULT, T.COLUMN_COMMENT, T.EXTRA FROM INFORMATION_SCHEMA.COLUMNS T JOIN INFORMATION_SCHEMA.VIEWS O ON O.TABLE_SCHEMA = T.TABLE_SCHEMA AND O.TABLE_NAME = T.TABLE_NAME WHERE (T.TABLE_SCHEMA = '{connpar.Database}') AND (T.TABLE_NAME = ?TABLENAME OR ?TABLENAME IS NULL) AND (T.COLUMN_NAME = ?COLUMNNAME OR ?COLUMNNAME IS NULL) ORDER BY T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(ViewColumn.ViewName)) .Parameterize(parameters, "COLUMNNAME", nameof(ViewColumn.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ViewColumn { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), ViewName = wrapper.GetString(reader, 2), Name = wrapper.GetString(reader, 3), DataType = wrapper.GetString(reader, 4), Length = wrapper.GetInt64(reader, 5), NumericPrecision = wrapper.GetInt32(reader, 6), NumericScale = wrapper.GetInt32(reader, 7), IsNullable = wrapper.GetString(reader, 8) == "YES", IsPrimaryKey = wrapper.GetString(reader, 9) == "PRI", Default = wrapper.GetString(reader, 10), Description = wrapper.GetString(reader, 11), Autoincrement = false })); }
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); SqlCommand sql = @" SELECT null AS CONSTRAINT_CATALOG, null AS CONSTRAINT_SCHEMA, trim(co.rdb$constraint_name) AS CONSTRAINT_NAME, null AS TABLE_CATALOG, null AS TABLE_SCHEMA, trim(co.rdb$relation_name) AS TABLE_NAME, trim(coidxseg.rdb$field_name) AS COLUMN_NAME, null as REFERENCED_TABLE_CATALOG, null as REFERENCED_TABLE_SCHEMA, trim(refidx.rdb$relation_name) as REFERENCED_TABLE_NAME, trim(refidxseg.rdb$field_name) AS REFERENCED_COLUMN_NAME, ref.rdb$update_rule AS UPDATE_RULE, ref.rdb$delete_rule AS DELETE_RULE FROM rdb$relation_constraints co INNER JOIN rdb$ref_constraints ref ON co.rdb$constraint_name = ref.rdb$constraint_name INNER JOIN rdb$indices tempidx ON co.rdb$index_name = tempidx.rdb$index_name INNER JOIN rdb$index_segments coidxseg ON co.rdb$index_name = coidxseg.rdb$index_name INNER JOIN rdb$indices refidx ON refidx.rdb$index_name = tempidx.rdb$foreign_key INNER JOIN rdb$index_segments refidxseg ON refidxseg.rdb$index_name = refidx.rdb$index_name AND refidxseg.rdb$field_position = coidxseg.rdb$field_position where co.rdb$constraint_type = 'FOREIGN KEY' AND (co.rdb$relation_name = @TABLENAME OR (@TABLENAME IS NULL)) AND (co.rdb$constraint_name = @NAME OR (@NAME IS NULL))"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName)) .Parameterize(parameters, "NAME", nameof(ForeignKey.Name)); var table = database.ExecuteDataTable(sql, parameters: parameters); foreach (DataRow row in table.Rows) { yield return(new ForeignKey { Schema = row["CONSTRAINT_SCHEMA"].ToString(), Name = row["CONSTRAINT_NAME"].ToString(), TableName = row["TABLE_NAME"].ToString().Replace("\"", ""), PKTable = row["REFERENCED_TABLE_NAME"].ToString(), ColumnName = row["COLUMN_NAME"].ToString(), PKColumn = row["REFERENCED_COLUMN_NAME"].ToString(), }); } }
protected override IEnumerable <View> GetViews(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); SqlCommand sql = $@" SELECT name, type FROM main.sqlite_master WHERE type LIKE 'view' AND (name = @NAME OR @NAME IS NULL)"; restrictionValues.Parameterize(parameters, "NAME", nameof(View.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new View { Catalog = "main", Name = wrapper.GetString(reader, 0) })); }
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT TC.CONSTRAINT_CATALOG, TC.CONSTRAINT_SCHEMA, TC.CONSTRAINT_NAME, TC.TABLE_NAME, KCU.COLUMN_NAME, CCU.TABLE_NAME AS FOREIGN_TABLE_NAME, CCU.COLUMN_NAME AS FOREIGN_COLUMN_NAME, TC.IS_DEFERRABLE, TC.INITIALLY_DEFERRED FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME WHERE TC.CONSTRAINT_CATALOG = '{connpar.Database}' AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND (TC.TABLE_NAME = @TABLENAME OR @TABLENAME IS NULL) AND (TC.CONSTRAINT_NAME = @NAME OR @NAME IS NULL)"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName)) .Parameterize(parameters, "NAME", nameof(ForeignKey.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ForeignKey { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), TableName = wrapper.GetString(reader, 3), ColumnName = wrapper.GetString(reader, 4), PKTable = wrapper.GetString(reader, 5), PKColumn = wrapper.GetString(reader, 6), })); }
protected override IEnumerable <ProcedureParameter> GetProcedureParameters(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE WHERE (SPECIFIC_SCHEMA = '{connpar.Database}') AND (SPECIFIC_NAME = @NAME OR (@NAME IS NULL)) AND (PARAMETER_NAME = @PARAMETER OR (@PARAMETER IS NULL)) ORDER BY SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_NAME"; restrictionValues .Parameterize(parameters, "NAME", nameof(ProcedureParameter.ProcedureName)) .Parameterize(parameters, "PARAMETER", nameof(ProcedureParameter.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ProcedureParameter { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), ProcedureName = wrapper.GetString(reader, 2), Name = wrapper.GetString(reader, 5), Direction = wrapper.GetString(reader, 4) == "IN" ? ParameterDirection.Input : ParameterDirection.Output, NumericPrecision = wrapper.GetInt32(reader, 8), NumericScale = wrapper.GetInt32(reader, 9), DataType = wrapper.GetString(reader, 6), Length = wrapper.GetInt64(reader, 7) })); }
protected override IEnumerable <IndexColumn> GetIndexColumns(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT DISTINCT DB_NAME() AS CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA = USER_NAME(O.UID), CONSTRAINT_NAME = X.NAME, TABLE_CATALOG = DB_NAME(), TABLE_SCHEMA = USER_NAME(O.UID), TABLE_NAME = O.NAME, COLUMN_NAME = C.NAME, ORDINAL_POSITION = CONVERT(INT, XK.KEYNO), KEYTYPE = C.XTYPE, INDEX_NAME = X.NAME FROM SYSOBJECTS O, SYSINDEXES X, SYSCOLUMNS C, SYSINDEXKEYS XK WHERE O.TYPE IN ('U') AND X.ID = O.ID AND O.ID = C.ID AND O.ID = XK.ID AND X.INDID = XK.INDID AND C.COLID = XK.COLID AND XK.KEYNO <= X.KEYCNT AND PERMISSIONS(O.ID, C.NAME) <> 0 AND (DB_NAME() = '{connpar.Database}') AND (USER_NAME()= @OWNER OR (@OWNER IS NULL)) AND (X.NAME = @CONSTRAINTNAME OR (@CONSTRAINTNAME IS NULL)) AND (C.NAME = @COLUMN OR (@COLUMN IS NULL)) ORDER BY TABLE_NAME, INDEX_NAME"; restrictionValues .Parameterize(parameters, "TABLE", nameof(IndexColumn.TableName)) .Parameterize(parameters, "CONSTRAINTNAME", nameof(IndexColumn.IndexName)) .Parameterize(parameters, "COLUMN", nameof(IndexColumn.ColumnName)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new IndexColumn { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), TableName = wrapper.GetString(reader, 5), IndexName = wrapper.GetString(reader, 2), ColumnName = wrapper.GetString(reader, 6) })); }
protected override IEnumerable <Table> GetTables(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); SqlCommand sql = @" SELECT null AS TABLE_CATALOG, null AS TABLE_SCHEMA, trim(rdb$relation_name) AS TABLE_NAME, (case when rdb$system_flag = 1 then 'SYSTEM_TABLE' else 'TABLE' end) AS TABLE_TYPE, rdb$owner_name AS OWNER_NAME, rdb$description AS DESCRIPTION, rdb$view_source AS VIEW_SOURCE FROM rdb$relations WHERE (rdb$relation_name = @NAME OR (@NAME IS NULL)) AND ((@TABLETYPE = 1 and rdb$system_flag = 1) OR ((@TABLETYPE = 0 or @TABLETYPE IS NULL) and rdb$system_flag = 0)) ORDER BY rdb$system_flag, rdb$owner_name, rdb$relation_name"; restrictionValues .Parameterize(parameters, "NAME", nameof(Table.Name)) .Parameterize(parameters, "TABLETYPE", nameof(Table.Type)); var table = database.ExecuteDataTable(sql, parameters: parameters); foreach (DataRow row in table.Rows) { yield return(new Table { Catalog = row["TABLE_CATALOG"].ToString(), Schema = row["TABLE_SCHEMA"].ToString(), Name = row["TABLE_NAME"].ToString(), Description = row["DESCRIPTION"].ToStringSafely(), Type = row["TABLE_TYPE"].ToString() == "TABLE" ? TableType.BaseTable : TableType.SystemTable }); } }
protected override IEnumerable <ForeignKey> GetForeignKeys(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT T.CONSTRAINT_CATALOG, T.CONSTRAINT_SCHEMA, T.CONSTRAINT_NAME, T.TABLE_NAME, T.COLUMN_NAME, T.REFERENCED_TABLE_NAME, T.REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE T WHERE (T.CONSTRAINT_SCHEMA = '{connpar.Database}') AND (T.TABLE_NAME = ?TABLENAME OR ?TABLENAME IS NULL) AND (T.CONSTRAINT_NAME = ?NAME OR ?NAME IS NULL) AND REFERENCED_TABLE_NAME IS NOT NULL"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(ForeignKey.TableName)) .Parameterize(parameters, "NAME", nameof(ForeignKey.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new ForeignKey { Catalog = wrapper.GetString(reader, 0), Schema = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), TableName = wrapper.GetString(reader, 3), ColumnName = wrapper.GetString(reader, 4), PKTable = wrapper.GetString(reader, 5), PKColumn = wrapper.GetString(reader, 6), })); }
protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); var connpar = GetConnectionParameter(database); SqlCommand sql = $@" SELECT T.OWNER, T.TABLE_NAME, T.COLUMN_NAME, T.DATA_TYPE AS DATATYPE, T.DATA_LENGTH AS LENGTH, T.DATA_PRECISION AS PRECISION, T.DATA_SCALE AS SCALE, T.NULLABLE AS NULLABLE, (CASE WHEN P.OWNER IS NULL THEN 'N' ELSE 'Y' END) PK, D.DATA_DEFAULT, C.COMMENTS FROM ALL_TAB_COLUMNS T LEFT JOIN ALL_COL_COMMENTS C ON T.OWNER = C.OWNER AND T.TABLE_NAME = C.TABLE_NAME AND T.COLUMN_NAME = C.COLUMN_NAME LEFT JOIN ALL_TAB_COLUMNS D ON T.OWNER = D.OWNER AND T.TABLE_NAME = D.TABLE_NAME AND T.COLUMN_NAME = D.COLUMN_NAME LEFT JOIN (SELECT AU.OWNER, AU.TABLE_NAME, CU.COLUMN_NAME FROM ALL_CONS_COLUMNS CU, ALL_CONSTRAINTS AU WHERE CU.OWNER = AU.OWNER AND CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME AND AU.CONSTRAINT_TYPE = 'P') P ON T.OWNER = P.OWNER AND T.TABLE_NAME =P.TABLE_NAME AND T.COLUMN_NAME = P.COLUMN_NAME WHERE (T.OWNER = '{connpar.UserId.ToUpper()}') AND (T.TABLE_NAME = :TABLENAME OR :TABLENAME IS NULL) AND (T.COLUMN_NAME = :COLUMNNAME OR :COLUMNNAME IS NULL) ORDER BY T.OWNER, T.TABLE_NAME, T.COLUMN_ID"; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(Column.TableName)) .Parameterize(parameters, "COLUMNNAME", nameof(Column.Name)); return(ExecuteAndParseMetadata(database, sql, parameters, (wrapper, reader) => new Column { Schema = wrapper.GetString(reader, 0), TableName = wrapper.GetString(reader, 1), Name = wrapper.GetString(reader, 2), DataType = wrapper.GetString(reader, 3), Length = wrapper.GetInt32(reader, 4), NumericPrecision = wrapper.GetInt32(reader, 5), NumericScale = wrapper.GetInt32(reader, 6), IsNullable = wrapper.GetString(reader, 7) == "Y", IsPrimaryKey = wrapper.GetString(reader, 8) == "Y", Default = wrapper.GetString(reader, 9), Description = wrapper.GetString(reader, 10), })); }
protected override IEnumerable <Column> GetColumns(IDatabase database, RestrictionDictionary restrictionValues) { var parameters = new ParameterCollection(); SqlCommand sql = @" SELECT null AS TABLE_CATALOG, null AS TABLE_SCHEMA, rfr.rdb$relation_name AS TABLE_NAME, trim(rfr.rdb$field_name) AS COLUMN_NAME, null AS COLUMN_DATA_TYPE, fld.rdb$field_sub_type AS COLUMN_SUB_TYPE, CAST(fld.rdb$field_length AS integer) AS COLUMN_SIZE, CAST(fld.rdb$field_precision AS integer) AS NUMERIC_PRECISION, CAST(fld.rdb$field_scale AS integer) AS NUMERIC_SCALE, CAST(fld.rdb$character_length AS integer) AS CHARACTER_MAX_LENGTH, CAST(fld.rdb$field_length AS integer) AS CHARACTER_OCTET_LENGTH, rfr.rdb$field_position AS ORDINAL_POSITION, rfr.rdb$default_source AS COLUMN_DEFAULT, coalesce(fld.rdb$null_flag, rfr.rdb$null_flag) AS COLUMN_NULLABLE, fld.rdb$field_type AS FIELD_TYPE, rfr.rdb$description AS DESCRIPTION, (select count(1) FROM RDB$RELATION_CONSTRAINTS RC LEFT JOIN RDB$INDICES I ON (I.RDB$INDEX_NAME = RC.RDB$INDEX_NAME) LEFT JOIN RDB$INDEX_SEGMENTS S ON (S.RDB$INDEX_NAME = I.RDB$INDEX_NAME) WHERE (RC.RDB$CONSTRAINT_TYPE = 'PRIMARY KEY') and rc.RDB$RELATION_NAME = rfr.rdb$relation_name and RDB$FIELD_NAME = rfr.rdb$field_name) as COLUMN_IS_PK FROM rdb$relation_fields rfr LEFT JOIN rdb$fields fld ON rfr.rdb$field_source = fld.rdb$field_name LEFT JOIN rdb$character_sets cs ON cs.rdb$character_set_id = fld.rdb$character_set_id LEFT JOIN rdb$collations coll ON (coll.rdb$collation_id = fld.rdb$collation_id AND coll.rdb$character_set_id = fld.rdb$character_set_id) WHERE (rfr.rdb$relation_name = @TABLENAME OR (@TABLENAME IS NULL)) AND (rfr.rdb$field_name = @COLUMNNAME OR (@COLUMNNAME IS NULL)) ORDER BY rfr.rdb$relation_name, rfr.rdb$field_position "; restrictionValues .Parameterize(parameters, "TABLENAME", nameof(Column.TableName)) .Parameterize(parameters, "COLUMNNAME", nameof(Column.Name)); var table = database.ExecuteDataTable(sql, parameters: parameters); foreach (DataRow row in table.Rows) { var subtype = 0; int scale = 0; if (row["COLUMN_SUB_TYPE"] != DBNull.Value) { subtype = row["COLUMN_SUB_TYPE"].To <int>(); } if (row["NUMERIC_SCALE"] != DBNull.Value) { scale = row["NUMERIC_SCALE"].To <int>(); } var ftype = row["FIELD_TYPE"].To <int>(); yield return(new Column { Catalog = row["TABLE_CATALOG"].ToString(), Schema = row["TABLE_SCHEMA"].ToString(), TableName = row["TABLE_NAME"].ToString(), Name = row["COLUMN_NAME"].ToString(), Default = row["COLUMN_DEFAULT"].ToString(), DataType = GetDbDataType(ftype, subtype, scale), NumericPrecision = row["NUMERIC_PRECISION"].To <int>(), NumericScale = scale, Description = row["DESCRIPTION"].ToString(), IsNullable = row["COLUMN_NULLABLE"].To <bool>(), Length = row["COLUMN_SIZE"].To <long>(), Position = row["ORDINAL_POSITION"].To <int>(), IsPrimaryKey = row["COLUMN_IS_PK"].To <bool>() }); } }