获取指定数据库表， 以及指定表的数据信息

代码

-- --获取指定用户表的字段信息


SELECT

( case when a.colorder = 1 then d.name else '' end ) N ' Table Name ' ,

a.colorder N ' Column SQ ' ,

a.name N ' Column Name ' ,

( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else '' end ) N ' Idnetity FG ' ,

( 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 ) N ' PK ' ,

b.name N ' Type ' ,

a.length N ' Bit Length ' ,

COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as N ' Length ' ,

isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as N ' DataScale ' ,

( case when a.isnullable = 1 then ' √ ' else '' end ) N ' Null ' ,

isnull (e. text , '' ) N ' Default '

FROM syscolumns a left join systypes b

on a.xtype = b.xusertype

inner join sysobjects d

on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties '

left join syscomments e

on a.cdefault = e.id
where d.name = ' Down ' -- 如果只查询指定表,加上此条件


order by object_name (a.id),a.colorder




-- --获取指定数据库中用户表

SELECT name FROM sysobjects WHERE xtype = ' U '

转载于:https://www.cnblogs.com/group/archive/2010/04/23/1718436.html