直接上代码吧:
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+''')';
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;
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之后系统表名称改变了