文档章节

代码生成器Sql Server 和 Mysql 数据库脚本

淡看江湖
 淡看江湖
发布于 2015/05/31 21:17
字数 266
阅读 263
收藏 10

#SQL SERVER
        private string SqlTableList = @"SELECT
                                                    so.name,
                                                    Convert( VARCHAR(10), ep.[value]) AS [description]
                                                FROM
                                                    sysobjects so(NOLOCK)
                                                    LEFT JOIN sys.extended_properties ep(NOLOCK) ON ep.major_id=so.id AND ep.minor_id=0
                                                WHERE 
                                                    so.[type]='U' AND so.name<>'sysdiagrams'
                                                ORDER BY
                                                    so.name";

        private string SqlFieldList = @"SELECT 
                                            c.name,
                                            t.name AS [type],
                                            c.length AS maxLength,
                                            c.isnullable AS isNullable,
                                            (
                                                SELECT COUNT(1) FROM sys.identity_columns ic(NOLOCK) WHERE ic.[object_id]=c.id AND ic.column_id=c.colid
                                            ) AS isIdentity,
                                            (
                                                SELECT VALUE FROM   sys.extended_properties ep(NOLOCK) WHERE  ep.major_id = c.id AND ep.minor_id=c.colid
                                            ) AS [description],
                                            [IsPk]=CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype='PK' and parent_obj=c.id and name IN (
                                                SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) THEN 1 ELSE 0 END
                                        FROM
                                            syscolumns c(NOLOCK)
                                            INNER JOIN sys.tables ts(NOLOCK) ON ts.[object_id] = c.id
                                            INNER JOIN sys.types t(NOLOCK) ON t.system_type_id=c.xtype
                                            INNER JOIN systypes st(NOLOCK) ON st.name=t.name AND st.name<>'sysname'
                                            INNER JOIN sysusers su(NOLOCK) ON st.uid=su.uid AND su.name='sys'
                                            --INNER JOIN syscolumns s(NOLOCK) ON c.[object_id]=s.id
                                        WHERE
                                            ts.name='{0}'
                                        ORDER BY
                                            c.id ASC";
     

#MySQL
        private string SqlTableList_MySql = @"SELECT
                                                TABLE_NAME as name,
                                                TABLE_COMMENT as description
                                            FROM
                                                information_schema.TABLES
                                            WHERE
                                                TABLE_SCHEMA='{0}'";

        private string SqlFieldList_MySql = @"SELECT
                                                COLUMN_NAME as name,
                                                DATA_TYPE as type,
                                                IFNULL(CHARACTER_MAXIMUM_LENGTH,0) as maxLength,
                                                (CASE IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END) as isNullable,
as isIdentity,
                                                COLUMN_COMMENT as description,
                                                (CASE COLUMN_KEY WHEN 'PRI' THEN 1 ELSE 0 END) as IsPk
                                            FROM
                                                information_schema.COLUMNS 
                                            WHERE
                                                TABLE_SCHEMA='{0}'
                                                AND TABLE_NAME='{1}'
                                            ORDER BY
                                                ORDINAL_POSITION ASC";
        #endregion

© 著作权归作者所有

共有 人打赏支持
淡看江湖
粉丝 35
博文 82
码字总数 92173
作品 0
浦东
后端工程师
私信 提问
DBeaver 2.0.3 发布,数据库管理工具

DBeaver 2.0.3 发布,该版本增加为通用 JDBC 驱动的 DDL 生成器,修复了 MySQL MSSQL 和 Informix 的 SQL 格式化和自动完成的 bug ,修复了一些 UI 上的小问题。 DBeaver 是一个通用的数据库...

oschina
2012/11/29
930
2
(喷血分享)利用.NET生成数据库表的创建脚本,类似SqlServer编写表的CREATE语句

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/chinahuyong/article/details/9469661 (喷血分享)利用.NET生成数据库表的创建脚本,类似SqlServer编写表的C...

rdiframework
2013/07/25
0
0
手机游戏服务器引擎Scut免费开源

scut 官网:http://www.scutgame.com/ Scut是一个开源、免费、稳定、快速开发的手机游戏服务器引擎,支持开发人员使用Python脚本语言或C#语言开发;底层采用C#编写,基于MVC框架思想设计, ...

shezjl
2015/06/16
0
0
.NET Core实战项目之CMS 第十一章 开发篇-数据库生成及实体代码生成器开发

上篇给大家从零开始搭建了一个我们的ASP.NET Core CMS系统的开发框架,具体为什么那样设计我也已经在第十篇文章中进行了说明。不过文章发布后很多人都说了这样的分层不是很合理,什么数据库实...

依乐祝
12/13
0
0
8个不错的随机生成数据库测试数据的利器

样品/测试数据,是一种测试网络工程与应用的有效方式。除了毫无意义的数据,真实姓名,地址,邮递区号,可以使整个测试阶段比较容易。 webresourcesdepot搜集了8个很不错的免费测试数据生成器...

刘学炜
2013/05/10
0
1

没有更多内容

加载失败,请刷新页面

加载更多

border实现等高布局

效果图 实现上图效果的全部html+css代码 <div class="box"> <nav> <h3 class="nav">导航1</h3> <h3 class="nav">导航2</h3> </nav> <section> <div cla......

呵呵闯
13分钟前
0
0
MaxCompute 表(Table)设计规范

表的限制项 表(Table)设计规范 表设计主要目标 表设计的影响 表设计步骤 表数据存储规范 按数据分层规范数据生命周期 按数据的变更和历史规范数据的保存 数据导入通道与表设计 分区设计与逻辑...

阿里云云栖社区
19分钟前
0
0
局域网共享文件读写的实现方式

代码片段 首先是设置共享目录,支持用户和密码等权限控制 然后我们可以使用Windows资源管理器操作共享目录下的文件 这中间隐藏了资源管理器帮我们建立目录映射和连接的过程,如果设置了用户名...

夏至如沫
28分钟前
2
0
Elasticsearch安装与配置

一、Docker安装ES 开发模式 可以使用以下命令快速启动Elasticsearch以进行开发或测试: $ docker run -p 9200:9200 -p 9300:9300 -d --name es -e "discovery.type=single-node" docker.ela...

吴伟祥
35分钟前
1
0
移动页面滚动穿透解决方案(荐)

移动页面滚动穿透解决方法目前有多种解决方案,我介绍下几种方案: 解决方案1:阻止冒泡。 //关键代码$(".sliders,.modals").on("touchmove",function(event){    event.preventDefau...

壹峰
35分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部