1.查询当前数据库所有表
复制代码 代码如下:
select
o.object_id as tableid,
tablename=o.name ,
tabledesc= o.type
from sys.columns c
inner join sys.objects o
on c.[object_id]=o.[object_id]
and o.type=’u’
and o.is_ms_shipped=0
inner join sys.types t
on c.user_type_id=t.user_type_id
left join sys.extended_properties ptb
on ptb.class=1
and ptb.minor_id=0
and c.[object_id]=ptb.major_id
where c.column_id=1
order by tablename
2.查询当前表所有字段，数据，约束
复制代码 代码如下:
select
tabname=o.name,
columnline=c.column_id,
columnname=c.name,
typenum=t.name,
typelength=c.max_length,
fstate=isnull(g.value,n”),
isablenull=case when c.is_nullable=1 then n’√’else n” end,
defaultdata=isnull(d.definition,n”),
isidentity=case when c.is_identity=1 then n’√’else n” end,
isprimary=case when exists(select 1 from sysobjects where xtype=’pk’ and parent_obj=c.[object_id] and name in (
select name from sysindexes where indid in( select indid from sysindexkeys where id = c.[object_id] and colid=c.column_id))) then ‘√’ else ” end,
isforeign=case when exists(select * from sysforeignkeys fk where c.[object_id]=fk.fkeyid and c.column_id=fk.fkey)then ‘√’ else ” end,
tabforeignname=isnull(idx.fkname,n”),
outnamecol=isnull(idx.ns,n”)
from sys.columns c
inner join sys.objects o
on c.[object_id]=o.[object_id]
and o.type=’u’
and o.is_ms_shipped=0
inner join sys.types t
on c.user_type_id=t.user_type_id
left join sys.extended_properties g
on c.[object_id]=g.major_id and c.column_id=g.minor_id
left join sys.default_constraints d
on c.[object_id]=d.parent_object_id
and c.column_id=d.parent_column_id
and c.default_object_id=d.[object_id]
left join sysforeignkeys fk
on c.[object_id]=fk.fkeyid
and c.column_id=fk.fkey
left join — 索引及主键信息
(
select
idx.fkeyid,
idx.fkey,
fkname=o.name,
ns=ss.name
from sysforeignkeys idx
inner join sys.objects o
on idx.rkeyid=o.[object_id]
and o.type=’u’
and o.is_ms_shipped=0
left join syscolumns ss
on idx.rkeyid=ss.id
and idx.rkey=ss.colid
)idx
on c.[object_id]=idx.fkeyid
and c.column_id=idx.fkey
where o.name=n'{0}’ ——要查询的表名
order by o.name,c.column_id
3.字段
要加单引号varchar,char,nvarchar,nchar,text,ntext,datetime
不需要加int,numeric,bit 不需要加
带长度：[binary],[char],[decimal],[nchar],[numeric],[nvarchar],[varbinary][varchar]
不用带：[bigint],[bit],[datetime],[float],[image],[int],[xml],[timestamp],[tinyint],
[uniqueidentifier],[money],[ntext],[real],[smalldatetime],[smallint],[smallmoney],
[sql_variant],[text]