sql脚本查询数据库表,数据,结构,约束等操作的方法

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]