在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之后系统表名称改变了