//与数据库结构有关的一些函数
//1、动态改变字段名称
uses ComObj;
//Access
//TableName: 表名; OldColName: 原字段名; NewColName: 新字段名;
procedure RenameField(const TableName, OldColName, NewColName: string);
var
DB, Col: OleVariant;
begin
DB := CreateOleObject('ADOX.Catalog');
DB.ActiveConnection := ADOConnection1.ConnectionObject;
Col := CreateOleObject('ADOX.Column');
Col := DB.Tables[TableName].Columns[OldColName];
Col.Name := NewColName;
end;
//SQLServer
procedure RenameField(const TableName, OldColName, NewColName: string);
begin
with ADOCommand1 do
begin
CommandText := 'EXEC sp_rename ''' + TableName + '.' + OldColName +
''',''' + NewColName + ''',''COLUMN'';';
Excute;
end;
end;
2、取得 Access 库中的表结构
type
TTableDef = record
Name,
DateCreated,
LastUpdated,
Description: string;
end;
TTableDefs = array of TTableDef;
procedure GetTableDefs(const DBName: string; out TableDefs: TTableDefs);
var
DBEngine, DB: OleVariant;
I: Longint;
begin
try
DBEngine := CreateOleObject('DAO.DBEngine.36');
DB := DBEngine.OpenDatabase(DBName);
SetLength(TableDefs, Longint(DB.TableDefs.Count));
for I := Low(TableDefs) to High(TableDefs) do
begin
TableDefs[I].Name := DB.TableDefs[I].Name;
TableDefs[I].DateCreated := DB.TableDefs[I].DateCreated;
TableDefs[I].LastUpdated := DB.TableDefs[I].LastUpdated;
try
TableDefs[I].Description := DB.TableDefs[I].Properties['Description'].Value;
except
TableDefs[I].Description := '';
end;
end;
finally
DB := Unassigned;
DBEngine := Unassigned;
end;
end;
3、取得 Access 表中的字段结构
type
TFieldDef = record
Name: string;
Types,
Size: Longint;
Description: string;
end;
TFieldDefs = array of TFieldDef;
procedure GetFieldDefs(const DBName, TableName: string; out FieldDefs: TFieldDefs);
var
DBEngine, DB: OleVariant;
I: Longint;
begin
try
DBEngine := CreateOleObject('DAO.DBEngine.36');
DB := DBEngine.OpenDatabase(DBName);
SetLength(FieldDefs, Longint(DB.TableDefs[TableName].Fields.Count));
for I := Low(FieldDefs) to High(FieldDefs) do
begin
FieldDefs[I].Name := DB.TableDefs[TableName].Fields[I].Name;
FieldDefs[I].Types := DB.TableDefs[TableName].Fields[I].Type;
FieldDefs[I].Size := DB.TableDefs[TableName].Fields[I].Size;
try
FieldDefs[I].Description := DB.TableDefs[TableName].Fields[I].Properties['Description'].Value;
except
FieldDefs[I].Description := '';
end;
end;
finally
DB := Unassigned;
DBEngine := Unassigned;
end;
end;
//1、动态改变字段名称
uses ComObj;
//Access
//TableName: 表名; OldColName: 原字段名; NewColName: 新字段名;
procedure RenameField(const TableName, OldColName, NewColName: string);
var
DB, Col: OleVariant;
begin
DB := CreateOleObject('ADOX.Catalog');
DB.ActiveConnection := ADOConnection1.ConnectionObject;
Col := CreateOleObject('ADOX.Column');
Col := DB.Tables[TableName].Columns[OldColName];
Col.Name := NewColName;
end;
//SQLServer
procedure RenameField(const TableName, OldColName, NewColName: string);
begin
with ADOCommand1 do
begin
CommandText := 'EXEC sp_rename ''' + TableName + '.' + OldColName +
''',''' + NewColName + ''',''COLUMN'';';
Excute;
end;
end;
2、取得 Access 库中的表结构
type
TTableDef = record
Name,
DateCreated,
LastUpdated,
Description: string;
end;
TTableDefs = array of TTableDef;
procedure GetTableDefs(const DBName: string; out TableDefs: TTableDefs);
var
DBEngine, DB: OleVariant;
I: Longint;
begin
try
DBEngine := CreateOleObject('DAO.DBEngine.36');
DB := DBEngine.OpenDatabase(DBName);
SetLength(TableDefs, Longint(DB.TableDefs.Count));
for I := Low(TableDefs) to High(TableDefs) do
begin
TableDefs[I].Name := DB.TableDefs[I].Name;
TableDefs[I].DateCreated := DB.TableDefs[I].DateCreated;
TableDefs[I].LastUpdated := DB.TableDefs[I].LastUpdated;
try
TableDefs[I].Description := DB.TableDefs[I].Properties['Description'].Value;
except
TableDefs[I].Description := '';
end;
end;
finally
DB := Unassigned;
DBEngine := Unassigned;
end;
end;
3、取得 Access 表中的字段结构
type
TFieldDef = record
Name: string;
Types,
Size: Longint;
Description: string;
end;
TFieldDefs = array of TFieldDef;
procedure GetFieldDefs(const DBName, TableName: string; out FieldDefs: TFieldDefs);
var
DBEngine, DB: OleVariant;
I: Longint;
begin
try
DBEngine := CreateOleObject('DAO.DBEngine.36');
DB := DBEngine.OpenDatabase(DBName);
SetLength(FieldDefs, Longint(DB.TableDefs[TableName].Fields.Count));
for I := Low(FieldDefs) to High(FieldDefs) do
begin
FieldDefs[I].Name := DB.TableDefs[TableName].Fields[I].Name;
FieldDefs[I].Types := DB.TableDefs[TableName].Fields[I].Type;
FieldDefs[I].Size := DB.TableDefs[TableName].Fields[I].Size;
try
FieldDefs[I].Description := DB.TableDefs[TableName].Fields[I].Properties['Description'].Value;
except
FieldDefs[I].Description := '';
end;
end;
finally
DB := Unassigned;
DBEngine := Unassigned;
end;
end;