文档章节

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

淡看江湖
 淡看江湖
发布于 2015/05/31 21:17
字数 266
阅读 260
收藏 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
浦东
后端工程师
(喷血分享)利用.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
8个不错的随机生成数据库测试数据的利器

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

刘学炜
2013/05/10
0
1
PHP 对 mysql 的事务处理

mysql>mysql事务处理php代码实现事务的处理可以通过PHP预定义类mysqli的以下方法实现。 autocommit(boolean):该方法用于限定查询结果是否自动提交,如果该方法的参数为true则自动提交,如果...

罗春宗
2012/10/31
0
0
jmeter(八)-JDBC请求(sqlserver)

做JDBC请求,首先要了解这个JDBC对象是什么,然后寻找响应的数据库连接URL和数据库驱动。 数据库URL:jdbc:sqlserver://200.99.197.190:1433;databaseName=ebank 数据库驱动:com.microsoft...

劲风online
2015/01/08
0
0

没有更多内容

加载失败,请刷新页面

加载更多

区块链100讲:盘点那些常用的加密算法原理

在开发过程中,常常用到各种加密方法和算法,本文总结了几种常用加密方法的原理。 1 对称加密 原理:加密和解密数据使用同一个密钥,适合对大量数据进行加解密 安全性:关键是密钥的保存方式...

HiBlock
24分钟前
0
0
zookeeper基本常识

一、Zookeeper基础知识 1 zookeeper是一个类似hdfs的树形文件结构,zookeeper可以用来保证数据在(zk)集群之间的数据的事务性一致。2 zookeeper有watch事件,是一次性触发的,当watch监视的数...

啃不动地大坚果
29分钟前
0
0
Forrester企业级容器平台权威排行出炉,小初创Rancher缘何成为领导者?

全球著名的调研机构Forrester Research近日发布了《The Forrester New Wave: Enterprise Container Platform Software Suites, Q4 2018》报告,对企业级容器平台(ECP)市场进行全面评估,希...

RancherLabs
33分钟前
0
0
【三 异步HTTP编程】 2. 流式HTTP响应

标准响应及Content-Length头 自HTTP1.1以来,服务器为了在一个链接中处理多个HTTP请求及响应,必须随response一起返回合适的Content-Length值。 默认情况下,对于简单请求你无需返回 Conten...

Landas
今天
0
0
Java后端技术栈,到底如何深入学习?

Java,是现阶段中国互联网公司中,覆盖度最广的研发语言。有不少朋友问,如何深入学习Java后端技术栈,今天分享一个,互联网牛人整理出来的Java深入学习路线图,以及免费学习资料。 一 。性能...

别打我会飞
今天
2
1

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部