<--动态获取数据库字段-->
declare @str varchar(600)
declare @sql varchar(6000)
set @str=''
select @str =(@str +cast(name as varchar(30)) +',' ) from syscolumns where id = object_id( 'tblSex ')
select @sql='select ' + substring( @str,0,len(@str)) +' from tblSex'
select @sql
exec(@sql)
<--end-->
<--动态获取数据库字段增加条件后的方法-->
declare @str varchar(600)
declare @sql varchar(6000)
set @str=''
select @str =(@str +cast(name as varchar(30)) +',' ) from syscolumns where id = object_id( 'tblSex ') and name <> 'S_Id'
select @sql='select ' + substring( @str,0,len(@str)) +' from tblSex'
select @sql
exec(@sql)
<--end-->
<--SQL获取表注释-->
SELECT B.name as 字段名,A.[value] as 注释
FROM sysproperties A INNER JOIN
syscolumns B ON b.colid = A.smallid AND A.id = B.id
WHERE (B.id = OBJECT_ID('tblsex'))
<--end-->
SQL2005:
select * from fn_listextendedproperty('MS_Description', 'user', 'dbo', 'table', '表名称'', 'column', default)
注:次方法只能取出有注释的字段,如果指定表没有注释,则此方法不能获取到任何记录