📄 database.pas
字号:
begin
result:=false;
b:= TryStrToFloat(AFieldName,F);
if b then exit;
b:=false;
for j:=0 to length(AFieldName)-1 do
if AFieldName[j] in ['''','+','-','*','/','%','@'] then
//有运算符号，或者是字符串常数
begin
B:=true;
break;//
end;
result:=not b;
end;
//************************************
function GetTableDef(ADOConn: TADOConnection;Const ATableName:string;
var ATableInfo: TTableInfo;
const fk_index:bool=false //是否返回外键和索引信息
):boolean;//返回表/试图的结构 ;结果在ATableInfo中
var SQLStr:String;
ViewScript:string;//试图脚本
ViewFields:TCodeValueList; //
ViewTables:string;//如果是视图，用了哪些基础表
TempS,FKPK:string;
P,R,I,J:integer;
S:string;//临时变量
// F:Extended;//临时变量
B:boolean;//临时变量
OwnerName,ObjName :String;
begin
with ATableInfo do //清空
begin
TableName:='';
Table_Description:='';//表描述
setLength(FieldInfoList,0);
setLength(pkeysList,0);// 主键字段列表
setLength(fkeysList,0);// 外键字段列表
setLength(IndexsList,0); // 索引键字段列表
XType:='';//U=table V=View
AllFieldSelected:=false;
//PK:='';//主键字段
end;
with TADOQuery.Create(nil) do
try
Connection :=ADOConn;
if Database.OpenConn(ADOConn) then
begin
P:=POS('.',ATableName);
OwnerName:=copy(ATableName,1,p-1);
ObjName:=copy(ATableName,P+1,length(ATableName));
ATableInfo.TableName :=OwnerName +'.[' + ObjName + ']';
//判断类型
close; // OwnerName,ObjName :String;
SQLStr:='select type from sysobjects where name = N'+QuotedStr(ObjName) +' and user_name(uid) = N'''+OwnerName+'''';
SQL.Text :=SQLStr ;
open;
TempS:=FieldByName('type').AsString ;
ATableInfo.XType :=trim(TempS);
SQLStr:=
'select cast(xp.[value] as nvarchar(4000)) from ::::fn_listextendedproperty '
+ ' (NULL, N''user'', N'+QuotedStr(ObjName)+', N''table'', N'+QuotedStr(ObjName)+', null,null) xp where xp.name'
+' = N''MS_Description''';
close;
SQL.Clear;
SQL.Add(SQLStr) ;
open;
if Recordcount>0 then
ATableInfo.Table_Description:=Fields[0].AsString ;
//**************
Close;
SQLStr:='exec sp_MShelpcolumns N''' + QuotedObjName(ATableName)+''', null, ''id'', 1';
SQL.Text:=SQLStr ;
try
Open ;
result:=true;
except
result:=false;
end;
SetLength(ATableInfo.FieldInfoList ,RecordCount);
R:=0;
while not eof do
begin
ATableInfo.FieldInfoList[R].FieldName:=Fieldbyname('Col_name').AsString;
ATableInfo.FieldInfoList[R].isPK:=false;
ATableInfo.FieldInfoList[R].FieldTypeName:=Fieldbyname('Col_Basetypename').AsString;
ATableInfo.FieldInfoList[R].Col_len :=Fieldbyname('Col_len').AsInteger ;
ATableInfo.FieldInfoList[R].Col_prec :=-1;
if Fieldbyname('Col_prec').IsNull =false then
ATableInfo.FieldInfoList[R].Col_prec := Fieldbyname('Col_prec').AsInteger ;
ATableInfo.FieldInfoList[R].Col_Scale :=-1;
if Fieldbyname('Col_Scale').IsNull =false then
ATableInfo.FieldInfoList[R].Col_Scale := Fieldbyname('Col_Scale').AsInteger ;
ATableInfo.FieldInfoList[R].Col_flages := Fieldbyname('Col_flags').AsInteger ;
if ATableInfo.FieldInfoList[R].Col_flages >=4 then//1024 如果是主键，则 0->4,1->5,2->6(测试出来的)
ATableInfo.FieldInfoList[R].Col_flages:=ATableInfo.FieldInfoList[R].Col_flages-4;
ATableInfo.FieldInfoList[R].FieldNull :=not Fieldbyname('Col_null').AsBoolean ;
ATableInfo.FieldInfoList[R].Col_Identity := Fieldbyname('Col_Identity').AsBoolean ;
TempS:= Fieldbyname('text').AsString ;
if TempS='' then
TempS:='null';
ATableInfo.FieldInfoList[R].Default := TempS;
inc(R);
next;
end;
//**************************
//PK
close; // OwnerName,ObjName :String;
SQLStr:='exec sp_pkeys @table_name =N'+QuotedStr(ObjName)+',@table_owner =N'''+OwnerName+'''';
SQL.Text :=SQLStr ;
open;
SetLength(ATableInfo.pkeysList ,RecordCount);
R:=0;
while not eof do
begin
ATableInfo.pkeysList[R].COLUMN_NAME:=FieldByName('COLUMN_NAME').AsString ;
for J:=0 to length(ATableInfo.FieldInfoList)-1 do
if ATableInfo.FieldInfoList[J].FieldName =ATableInfo.pkeysList[R].COLUMN_NAME then
begin
ATableInfo.FieldInfoList[J].isPK :=true;
break;
end ;
inc(R);
next;
end;
//PK end
SetLength(ATableInfo.fkeysList ,0);
SetLength(ATableInfo.IndexsList,0);
if fk_index then
begin
//FK
close; // OwnerName,ObjName :String;
//SQLStr:='exec sp_fkeys @table_name =N'''+ObjName+''',@table_owner =N'''+OwnerName+'''';
SQLStr:='exec sp_MStablerefs N'''+QuotedObjName (ATableName)+''', N''actualtables'', N''both'', null';
SQL.Text :=SQLStr ;
open;
SetLength(ATableInfo.fkeysList ,0);
R:=0;
while not eof do
begin
for I:=1 to 16 do
if FieldByName('cKeyCol' + inttostr(i)).IsNull =false then
begin
inc(R);
SetLength(ATableInfo.fkeysList ,R);
ATableInfo.fkeysList[R-1].pk_table:=FieldByName('pk_table').AsString ;
ATableInfo.fkeysList[R-1].fk_table:=FieldByName('fk_table').AsString ;
ATableInfo.fkeysList[R-1].cKeyCol:=FieldByName('cKeyCol' + inttostr(i)).AsString ;
ATableInfo.fkeysList[R-1].cRefCol:=FieldByName('cRefCol' + inttostr(i)).AsString ;
end
else
break;
next;
end;
//开始关联外键与字段
for I:=0 to length(ATableInfo.FieldInfoList)-1 do
begin
TempS:='';
for j:=0 to length(ATableInfo.fkeysList)-1 do
begin
if ATableInfo.fkeysList[j].pk_table = ATableInfo.TableName then //被引用 ,pk
if ATableInfo.FieldInfoList[i].FieldName = ATableInfo.fkeysList[j].cRefCol then
TempS:=TempS + ATableInfo.fkeysList[j].fk_table +'.' + ATableInfo.fkeysList[j].cKeyCol +'(PK),';
if ATableInfo.fkeysList[j].fk_table = ATableInfo.TableName then //被引用 ,pk
if ATableInfo.FieldInfoList[i].FieldName = ATableInfo.fkeysList[j].cKeyCol then
TempS:=TempS + ATableInfo.fkeysList[j].pk_table +'.' + ATableInfo.fkeysList[j].cRefCol +'(FK),';
end;
TempS:=copy(TempS,1,length(TempS)-1);
ATableInfo.FieldInfoList[i].Refs := TempS;
end;
//Indexs
if ATableInfo.XType='U' then
begin
close; // OwnerName,ObjName :String;
SQLStr:=' sp_MShelpindex N'''+ATableName +''', null, 1';
//判断是否有索引,如果没有 ,执行sp_helpindex会没有返回结果,报错
SQL.Text :=SQLStr ;
open;
if RecordCount>0 then
begin
close; // OwnerName,ObjName :String;
SQLStr:='exec sp_helpindex N'''+ATableName+'''';
SQL.Text :=SQLStr ;
open;
SetLength(ATableInfo.IndexsList ,RecordCount);
R:=0;
while not eof do
begin
ATableInfo.IndexsList[R].index_name :=FieldByName('index_name').AsString ;
ATableInfo.IndexsList[R].index_description :=FieldByName('index_description').AsString ;
ATableInfo.IndexsList[R].index_keys :=FieldByName('index_keys').AsString ;
inc(R);
next;
end;
end ;//if RecordCount>0 then
end; //if TempS='U' then
end; //if fk_index then
//Column
if ATableInfo.XType='U' then //表
for R:=0 to Length(ATableInfo.FieldInfoList) -1 do//得到数据表的字段描述
begin
SQLStr:=
'select cast(xp.[value] as nvarchar(4000)) from ::::fn_listextendedproperty '
+ ' (NULL, N''user'', N'''+OwnerName+''', N''table'', N'+QuotedStr(ObjName)+', N''column'', N' +
QuotedStr(ATableInfo.FieldInfoList[R].FieldName)
+' ) xp where xp.name'
+' = N''MS_Description''';
close;
SQL.Clear;
SQL.Add(SQLStr) ;
Parameters.Clear ;
open;
if RecordCount >0 then
ATableInfo.FieldInfoList[R].description :=Fields[0].AsString
else
ATableInfo.FieldInfoList[R].description :='';
end //if ATableInfo.XType='U' then //表
else
if ATableInfo.XType='V' then //View
begin //开始读视图对应的字段备注
ATableInfo.Table_Description :=GetViewComments(ADOConn,ATableInfo.TableName);//备注
ViewScript:=GetViewScript(ADOConn,ATableInfo.TableName);//试图脚本
AnalysisViewScript(ViewScript,ViewTables, ViewFields );
for i:=0 to length(ATableInfo.FieldInfoList)-1 do
begin
s:= ATableInfo.FieldInfoList[i].FieldName;
s:=pub.FindValueByCode(ViewFields,s,true); //找到实际字段名称
if not IsField(s) then continue; //数字常数
if pos('.',s)>0 then //带.的，完整数据字段名
ATableInfo.FieldInfoList[i].Description := GetCommentsByFullFieldName(ADOConn,s)
else
ATableInfo.FieldInfoList[i].Description := GetCommentsFromTables(ADOConn,s,ViewTables);
end;
end; //if ATableInfo.XType='V' then //View
end;//if(Database.OpenConn(ADOConn) then
finally
free;
end;
result:=true;
end;
//************************
function GetDataSetDef(ADOConn: TADOConnection;Const ASQL:string;
var ATableInfo: TTableInfo):boolean;//以字符串返回DS结构;结果在ATableInfo中
var R,I:integer;
S,ViewTables:String;
ViewFields:TCodeValueList;
begin
result:=false;
with TADOQuery.Create(nil) do
try
Connection :=ADOConn;
if Database.OpenConn(ADOConn) then
begin
SQL.Text :=ASQL;
open;
SetLength(ATableInfo.FieldInfoList , FieldCount);
//R:=0;
ATableInfo.TableName :='';
ATableInfo.Table_Description:='Created by SQL script';
ATableInfo.AllFieldSelected:=false;
for R:=0 to FieldCount-1 do
begin
ATableInfo.FieldInfoList[R].FieldName:=Fields[R].FieldName;
ATableInfo.FieldInfoList[R].FieldTypeName:=FieldTypeToSQLDataType(Fields[R].DataType);
ATableInfo.FieldInfoList[R].Selected :=true;
end;
result:=true;
end;
finally
free;
end;
if result then
begin
AnalysisViewScript(ASQL,ViewTables, ViewFields );
for i:=0 to length(ATableInfo.FieldInfoList)-1 do
begin
s:= ATableInfo.FieldInfoList[i].FieldName;
s:=pub.FindValueByCode(ViewFields,s,true); //找到实际字段名称
if not IsField(s) then continue; //数字常数
if pos('.',s)>0 then //带.的，完整数据字段名
ATableInfo.FieldInfoList[i].Description := GetCommentsByFullFieldName(ADOConn,s)
else
ATableInfo.FieldInfoList[i].Description := GetCommentsFromTables(ADOConn,s,ViewTables);
end;
end;//if result then
end;
//*******************************
function GetDataSetDef(ADOConn: TADOConnection;ATSP_Info:TSP_Info;
var ATableInfo: TTableInfo):boolean;//以字符串返回DS结构;结果在ATableInfo中
var I,R:integer;
S:String;
Parameter:TParameter;
begin
with TADOStoredProc.Create(nil) do
try
Connection :=ADOConn;
if not DataBase.OpenConn(ADOConn) then exit;
if ADOConn.InTransaction then
ADOConn.RollbackTrans ;
ADOConn.BeginTrans ;
ProcedureName:= ATSP_Info.SPName ;
for I:=0 to length(ATSP_Info.SP_Parameters)-1 do
begin
Parameter:=Parameters.AddParameter;
Parameter.Name :='@' + ATSP_Info.SP_Parameters[i].COLUMN_Name;
Parameter.DataType := ftString;
Parameter.Size :=4000;
Parameter.Value :=ATSP_Info.SP_Parameters[i].Value ;
if ATSP_Info.SP_Parameters[i].COLUMN_TYPE=pub.SQL_PARAM_TYPE_OUTPUT then
Parameter.Direction :=pdOutput
else
Parameter.Direction :=pdInput;
end;
open;
SetLength(ATableInfo.FieldInfoList , FieldCount);
//R:=0;
ATableInfo.TableName :='';
ATableInfo.Table_Description:='Created by SP ' + ATSP_Info.SPName ;
ATableInfo.AllFieldSelected:=false;
for R:=0 to FieldCount-1 do
begin
ATableInfo.FieldInfoList[R].FieldName:=Fields[R].FieldName;
ATableInfo.FieldInfoList[R].FieldTypeName:=FieldTypeToSQLDataType(Fields[R].DataType);
ATableInfo.FieldInfoList[R].Selected :=true;
end;
result:=true;
ADOConn.RollbackTrans ;
finally
close;
free;
end;
end;
function GetSPParameters(ADOConn: TADOConnection;Const ASPName:string; //
var ATSP_Info: TSP_Info):boolean;
//返回存储过程参数，结果存在 ATSP_Parameters
var R:integer;
S:String;
PRName:String;//存储过程名称
// Owner:String;//
P:integer;
SQLStr:String;
Const SQLB ='exec sp_sproc_columns N''%S'''; //%S为存储过程名称，不能带Owner
begin
result:=false;
P:=pos('.',ASPName);
PRName:=copy(ASPName,P+1,length(ASPName));
SQLStr:=Format(SQLB,[PRName]);
with TADOQuery.Create(nil) do
try
Connection :=ADOConn;
if Database.OpenConn(ADOConn) then
begin
SQL.Text :=SQLStr;
Open ;
result:=true;
R:=0;
SetLength(ATSP_Info.SP_Parameters,r);
ATSP_Info.SPName := ASPName;
while not eof do
begin
if FieldByName('COLUMN_TYPE').AsInteger =5 then
begin
Next ;
Continue ;
end;
setlength(ATSP_Info.SP_Parameters,R +1);
S:=FieldByName('COLUMN_Name').AsString;
S:=copy(S,2,length(S) ); //去@
ATSP_Info.SP_Parameters[R].COLUMN_Name := S;
S:=LowerCase(FieldByName('TYPE_NAME').AsString) ;
ATSP_Info.SP_Parameters[R].TYPE_NAME :=S ;
ATSP_Info.SP_Parameters[R].LENGTH:=FieldByName('LENGTH').AsInteger;
ATSP_Info.SP_Parameters[R].SCALE:=-1;
if FieldByName('SCALE').IsNull =false then
ATSP_Info.SP_Parameters[R].SCALE:=FieldByName('SCALE').AsInteger ;
ATSP_Info.SP_Parameters[R].CType_Name :=SQLDataToCDataType(S);
//ATSP_Parameters[R].CSQL_Type_Name :=ConvertToCSQLDataType(S);
ATSP_Info.SP_Parameters[R].COLUMN_TYPE := FieldByName('COLUMN_TYPE').AsInteger;
ATSP_Info.SP_Parameters[R].Selected:=true;
Next ;
inc(R);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -