在Delphi中根据SQL Server表名和表描述生成SQL语句

在Delphi中根据SQL Server表名和表描述生成SQL语句

直接上代码吧：

 

 1 function GetSQLByTableName(sTableName:string):String;
 2 var
 3   aDataSet:TDataSet;
 4   s:String;
 5   aField:TField;
 6   aFieldList,aFieldDescriptionList:TList<string>;
 7   function GetFieldDescription(sTable,sField:string):String;
 8   var
 9     sSQL:String;
10   begin
11     sSQL:='select sys.extended_properties.value  from sys.extended_properties '
12      +' left join   syscolumns on (syscolumns.colid=sys.extended_properties.minor_id'
13      +' and syscolumns.id=sys.extended_properties.major_id)'
14      +' where syscolumns.name='''+sField+''' and syscolumns.id=object_id('''+sTable+''')';

15

16     Result:=aAccessDB.GetValue(sSQL);
17     //如果没有描述直接取字段名称
18     if Result='' then
19       Result:=sField;
20   end;
21 begin
22   try
23     vSQL:='select * from '+sTableName+' where 1=2';
24     aDataSet:=aAccessDB.GetNewDataSet(vSQL);
25     aFieldList:=TList<string>.Create;
26     aFieldDescriptionList:=TList<string>.Create;
27     for aField in aDataSet.Fields do
28     begin
29        aFieldList.Add(aField.FieldName);
30        aFieldDescriptionList.Add(GetFieldDescription(sTableName,aField.FieldName));
31     end;
32     Result:='select ';
33     for s in aFieldList do
34        Result:=Result+s+' as '+aFieldDescriptionList[aFieldList.IndexOf(s)]+',';
35     Result:=Copy(Result,1,Length(Result)-1)+' from '+sTableName;
36   finally
37     aDataSet.Free;
38     aFieldList.Free;
39     aFieldDescriptionList.Free;
40   end;
41 end;

 

 

上面代码在Delphi 2010、SQL Server 2008下调试通过，在SQL Server 2000下注意要使用：

Select o.name AS tableName, c.name AS columnName, p.[value] AS Description
FROM sysproperties p INNER JOIN
sysobjects o ON o.id = p.id INNER JOIN
syscolumns c ON p.id = c.id AND p.smallid = c.colid
Where (p.name = 'MS_Description') AND (c.name = 'tablename') AND (o.name = 'fieldname')
orDER BY o.name

 

即2000和2005之后系统表名称改变了

分类: Delphi