Create Procedure [dbo].[Test_DB]
@tablename varchar(30)=''
as

Begin
declare @str varchar(3000)

select @str='
--select ''表名'' AS 表名,''0字段序号'' AS 字段序号,''字段名'' AS 字段名,''标识'' AS 标识,''主键'' AS 主键,''类型'' AS 类型,''占用字节数'' AS 占用字节数,''长度'' AS 长度,''小数位数'' AS 小数位数,''允许空'' AS 允许空,''默认值'' AS 默认值,''字段说明'' AS 字段说明

SELECT     TOP (100) PERCENT (CASE WHEN a.colorder = 1 THEN d .name ELSE '''' END) AS 表名, a.colorder AS 字段序号, a.name AS 字段名,
                      (CASE WHEN COLUMNPROPERTY(a.id, a.name, ''IsIdentity'') = 1 THEN ''√'' ELSE '''' END) AS 标识, (CASE WHEN
                          (SELECT     COUNT(*)
                            FROM          sysobjects
                            WHERE      (name IN
                                                       (SELECT     name
                                                         FROM          sysindexes
                                                         WHERE      (id = a.id) AND (indid in(SELECT indid'+'
                                                                                      FROM sysindexkeys
                                                                                      WHERE (id = a.id) AND (colid'+' in(SELECT colid
                                                                                                                   FROM          syscolumns
                                                                                                                   WHERE      (id = a.id) AND (name = a.name))))))) AND (xtype = ''PK'')) > 0 THEN ''√'' ELSE '''' END) AS 主键,
                      b.name AS 类型, a.length AS 占用字节数, COLUMNPROPERTY(a.id, a.name, ''PRECISION'') AS 长度, ISNULL(COLUMNPROPERTY(a.id, a.name, ''Scale''),
                      0) AS 小数位数, (CASE WHEN a.isnullable = 1 THEN ''√'' ELSE '''' END) AS 允许空, ISNULL(e.text, '''') AS 默认值, ISNULL(g.value, '''') AS 字段说明
FROM         sys.syscolumns AS a LEFT OUTER JOIN
                      sys.systypes AS b ON a.xtype = b.xusertype INNER JOIN
                      sys.sysobjects AS d ON a.id = d.id AND d.xtype = ''U'' AND d.name <> ''dtproperties'' LEFT OUTER JOIN
                      sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
                      sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id
'
if(@tablename!='')
 Begin
  --select @tablename=Replace(@tablename,'dbo.','')
  select @str=@str+'where d.name='''+@tablename+ ''''
 End

select @str=@str+'ORDER BY a.id, 字段序号'
exec(@str)
End

上面直接 复制

调用： Test_DB '表名'

http://www.cnblogs.com/downmoon/archive/2007/12/30/1021131.html