欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-11-24 09:32:10
1.查询当前数据库所有表 复制代码 代码如下:select     o.object_id as tableid,  &...

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]