将数据库结构导出表格

最新推荐文章于 2024-08-30 14:44:43 发布

a123_z

最新推荐文章于 2024-08-30 14:44:43 发布

阅读量727

点赞数 1

分类专栏：数据库文章标签： sql 数据库 mysql sqlserver

本文链接：https://blog.csdn.net/a123_z/article/details/107325096

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

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中即可。