程式碼生成器Sql Server 和 Mysql 資料庫指令碼

經常用到程式碼生成器，對於取資料指令碼做個記錄：

        #region SQL-SqlServer
        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";
        #endregion

        #region SQL-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,
                                                0 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