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

将数据库结构导出表格

程序员文章站 2022-05-01 20:20:43
1:数据库查询命令:SELECT TOP 1000 columnName = a.name, isPK = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( SELECT name FROM sysindexes WHE...

1:数据库查询命令:

SELECT TOP 1000
                columnName     = a.name,
                isPK       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                                 SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end,
                dataType       = b.name,
                isNull     = case when a.isnullable=1 then 1 else 0 end,
                columnCommnet   = isnull(g.[value],'')
            FROM 
                syscolumns a
            left join 
                systypes b 
            on 
                a.xusertype=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
            left join 
            sys.extended_properties   g 
            on 
                a.id=G.major_id and a.colid=g.minor_id  
            left join
            sys.extended_properties f
            on 
                d.id=f.major_id and f.minor_id=0
            where 
                d.name='{0}'    --如果只查询指定表,加上此where条件,tablename是要查询的表名;去除where条件查询所有的表信息
            order by 
                a.id,a.colorder

2:将查出来的数据,复制到Excel中即可。

将数据库结构导出表格

本文地址:https://blog.csdn.net/a123_z/article/details/107325096