代碼產生器Sql Server 和 Mysql 資料庫指令碼

標籤：

#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

代碼產生器Sql Server 和 Mysql 資料庫指令碼