namespace SqlDynamite.Common

open System
open System.Data
open System.Data.Odbc
open System.Data.OleDb
open System.Data.SqlClient
open System.Xml

type MsSqlMetadataFinder() =
    inherit MetadataFinder()

    static let system_schemas = "' '"(*"'sys'"*)
    
    [<DefaultValue(false)>]
    val mutable _serverVersion:int

    override this.EstablishConnection(connectionString:string, connectionType:ConnectionType, database:string) =
        if connectionType = ConnectionType.ODBC then
            this._connection <- new OdbcConnection(connectionString)
            this._connection.Open()
            let _ = Int32.TryParse(this._connection.ServerVersion.Split('.').[0], &this._serverVersion)
            if String.IsNullOrWhiteSpace(database) = false then
                let cmd = new OdbcCommand("use " + database, this._connection :?> OdbcConnection)
                ignore(cmd.ExecuteNonQuery())
        else if connectionType = ConnectionType.OLEDB then
            this._connection <- new OleDbConnection(connectionString)
            this._connection.Open()
            let _ = Int32.TryParse(this._connection.ServerVersion.Split('.').[0], &this._serverVersion)
            if String.IsNullOrWhiteSpace(database) = false then
                let cmd = new OleDbCommand("use " + database, this._connection :?> OleDbConnection)
                ignore(cmd.ExecuteNonQuery())
        else
            this._connection <- new SqlConnection(connectionString)
            this._connection.Open()
            let _ = Int32.TryParse(this._connection.ServerVersion.Split('.').[0], &this._serverVersion)
            if String.IsNullOrWhiteSpace(database) = false then
                let cmd = new SqlCommand("use \"" + database + "\"", this._connection :?> SqlConnection)
                ignore(cmd.ExecuteNonQuery())

    override this.Compose(tablename:string, fields:DataTable, keys:DataTable, checks:DataTable) : string =
        let result = "CREATE TABLE " + tablename + " ("
        let rowdef (record:DataRow) =
            let colname = "\n\t" + record.Item("col_name").ToString() +  " " + record.Item("col_typename").ToString() + " "
            let rtn = record.Item("col_typename").ToString()
            let col_len = record.Item("col_len").ToString().Trim()
            let typedef =
                match rtn.ToLower() with
                | "char" | "varchar"| "nchar" | "nvarchar" | "binary" | "varbinary" ->
                    "(" + (if col_len <> "-1" && col_len <> "0" then col_len else "max") + ") "
                | "decimal" | "numeric" ->
                    "(" + record.Item("col_prec").ToString().Trim() + ", " + record.Item("col_scale").ToString().Trim() + ") "
                | _ -> ""
            let collation = record.Item("collation")
            let colldef =
                match collation with
                | :? string ->
                    "COLLATE " + record.Item("collation").ToString() + " "
                | _ -> ""
            let identity = record.Item("col_identity") :?> bool
            let identdef =
                if identity then
                    "IDENTITY(" + record.Item("col_seed").ToString() + ", " + record.Item("col_increment").ToString() + ") "
                else ""
            let nullable = record.Item("col_null") :?> bool
            let nulldef =
                if nullable then
                    "NULL "
                else
                    "NOT NULL "
            let iscomputed = record.Item("col_iscomputed") :?> bool
            let compdef =
                if iscomputed then
                    "AS " + record.Item("comptext").ToString().Trim()
                else
                    ""
            let hasdefault = record.Item("deftext") :? string
            let defvalue =
                if hasdefault then
                    "DEFAULT " + record.Item("deftext").ToString().Trim()
                else
                    ""
            colname + (if iscomputed then compdef else typedef + colldef + identdef + nulldef + defvalue)
        let keydef (record:DataRow) =
            let cons = "\n\t CONSTRAINT [" + record.Item("cName").ToString() + "] "
            let cGroupName = record.Item("cGroupName")
            let cRefTable = record.Item("cRefTable")
            let gr =
                match cGroupName with
                | :? string -> true
                | _ -> false
            let re =
                match cRefTable with
                | :? string -> true
                | _ -> false
            let grdef3 = if record.Item("cType").ToString() = "1" then "PRIMARY KEY CLUSTERED\n\t" else "UNIQUE\n\t"
            let grdef1 = if gr then grdef3 else ""
            let redef1 = if re then "FOREIGN KEY\n\t" else ""
            let count = record.Item("cColCount") :?> int
            let rec keyloop n acc =
                if n > 0 then
                    let item = "\n\t\t[" + record.Item("cKeyCol" + n.ToString()).ToString() + "]" + if n < count then "," else ""
                    keyloop (n - 1) (item :: acc)
                else
                    acc
            let rec refloop n acc =
                if n > 0 then
                    let item = "\n\t\t[" + record.Item("cRefCol" + n.ToString()).ToString() + "]" + if n < count then "," else ""
                    refloop (n - 1) (item :: acc)
                else
                    acc
            let grdef2 = if gr then "ON [" + record.Item("cGroupName").ToString() + "]" else ""
            let refTableName = if re then record.Item("cRefTable").ToString().Substring(7, (record.Item("cRefTable").ToString()).Length - 8) else ""
            let redef2 = if re then "REFERENCES [" + refTableName + "] \n\t(" else ""
            let keydefs = if gr then "(" + String.Join("",keyloop count []) + "\n\t)" else ""
            let refdefs = if re then String.Join("",refloop count []) else ""
            cons + grdef1 + redef1 + keydefs + "\n\t" + grdef2 + redef2 + refdefs + if redef2 <> "" then "\n\t)" else ""
        let checkdef (record:DataRow) = "\n\tCONSTRAINT [" + record.Item(0).ToString() + "] CHECK " + record.Item(1).ToString()
        let rec rowloop n acc =
            if n > 0 then
                let item = rowdef(fields.Rows.Item(n - 1)) + if n < fields.Rows.Count then "," else ""
                rowloop (n - 1) (item :: acc)
            else
                acc
        let rec keyloop n acc =
            if n > 0 then
                let item = keydef(keys.Rows.Item(n - 1)) + if n < keys.Rows.Count || checks.Rows.Count <> 0 then "," else ""
                keyloop (n - 1) (item :: acc)
            else
                acc
        let rec checkloop n acc =
            if n > 0 then
                let item = checkdef(checks.Rows.Item(n - 1)) + if n < checks.Rows.Count then "," else ""
                checkloop (n - 1) (item :: acc)
            else
                acc
        let part1 = String.Join("",rowloop fields.Rows.Count [])
        let part2 = if keys.Rows.Count > 0 then String.Join("",keyloop keys.Rows.Count []) else ""
        let part3 = if checks.Rows.Count > 0 then String.Join("",checkloop checks.Rows.Count []) else ""
        let part4 =
            if this._serverVersion > 13 then
                let is_node = fields.Rows.Item(0).Item("is_node").ToString()
                let is_edge = fields.Rows.Item(0).Item("is_edge").ToString()
                if is_node = "True" then " AS NODE " elif is_edge = "True" then " AS EDGE " else " "
            else
                " "
        result + part1 + part2 + part3 + "\n)" + part4 +  "\n"

    member this.ComposeTableType(typename:string, fields:DataTable) : string =
        let result = "CREATE TYPE " + typename + " AS TABLE ("
        let rowdef (record:DataRow) =
            let colname = "\n\t" + record.Item("col_name").ToString() +  " " + record.Item("col_typename").ToString() + " "
            let rtn = record.Item("col_typename").ToString()
            let col_len = record.Item("col_len").ToString().Trim()
            let typedef =
                match rtn.ToLower() with
                | "char" | "varchar"| "nchar" | "nvarchar" | "binary" | "varbinary" ->
                    "(" + (if col_len <> "-1" then col_len else "max") + ") "
                | "decimal" | "numeric" ->
                    "(" + record.Item("col_prec").ToString().Trim() + ", " + record.Item("col_scale").ToString().Trim() + ") "
                | _ -> ""
            let identity = record.Item("col_identity") :?> bool
            let identdef =
                if identity then
                    " IDENTITY(" + record.Item("col_seed").ToString() + ", " + record.Item("col_increment").ToString() + ") "
                else ""
            let nullable = record.Item("col_null") :?> bool
            let nulldef =
                if nullable then
                    "NULL"
                else
                    "NOT NULL"
            colname + typedef.Replace("-1", "max") + identdef + nulldef
        let rec rowloop n acc =
            if n > 0 then
                let item = rowdef(fields.Rows.Item(n - 1)) + if n < fields.Rows.Count then "," else ""
                rowloop (n - 1) (item :: acc)
            else
                acc
        let rec keyloop n acc =
            if n > 0 then
                if fields.Rows.Item(n - 1).Item("InPrimaryKey") :?> bool then
                    let item = fields.Rows.Item(n - 1).Item("col_name").ToString() + if n < fields.Rows.Count then "," else ""
                    keyloop (n - 1) (item :: acc)
                else
                    keyloop (n - 1) acc
            else
                acc
        let part1 = String.Join("",rowloop fields.Rows.Count [])
        let primaryKeys = String.Join("",keyloop fields.Rows.Count [])
        let part2 = if primaryKeys.Length = 0 then "" else "\n\tPRIMARY KEY CLUSTERED\n\t(\n\t\t" + String.Join("",primaryKeys).TrimEnd(',') + "\n\t)\n"
        result + part1 + part2 + ")"

    member this.ComposeTableFunction(fields:DataTable) : string =
        let result = "TABLE ("
        let rowdef (record:DataRow) =
            let colname = "\n\t" + record.Item("col_name").ToString() +  " " + record.Item("col_typename").ToString() + " "
            let rtn = record.Item("col_typename").ToString()
            let col_len = record.Item("col_len").ToString().Trim()
            let typedef =
                match rtn.ToLower() with
                | "char" | "varchar"| "nchar" | "nvarchar" | "binary" | "varbinary" ->
                    "(" + (if col_len <> "-1" then col_len else "max") + ") "
                | "decimal" | "numeric" ->
                    "(" + record.Item("col_prec").ToString().Trim() + ", " + record.Item("col_scale").ToString().Trim() + ") "
                | _ -> ""
            let nullable = record.Item("col_null") :?> bool
            let nulldef =
                if nullable then
                    "NULL"
                else
                    "NOT NULL"
            colname + typedef.Replace("-1", "max") + nulldef
        let rec rowloop n acc =
            if n > 0 then
                let item = rowdef(fields.Rows.Item(n - 1)) + if n < fields.Rows.Count then "," else ""
                rowloop (n - 1) (item :: acc)
            else
                acc
        let part1 = String.Join("",rowloop fields.Rows.Count [])
        result + part1 + ")"

    override this.GetDefColumns(name:string, objtype:ObjectType) : DataTable list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let tableName = parts.[1]
        let query = String.Format("select c.name as col_name, CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND c.max_length <> -1 THEN c.max_length/2 ELSE c.max_length END AS int) as col_len,
       c.precision as col_prec, c.scale as col_scale,
       cc.definition as comptext, t.name as col_typename, c.is_nullable as col_null,
       c.is_identity as col_identity, ic.seed_value as col_seed, ic.increment_value as col_increment,
       c.is_computed as col_iscomputed, c.collation_name as collation, dc.definition as deftext
        from sys.tables AS o
        INNER JOIN sys.schemas AS s on s.schema_id = o.schema_id
        INNER JOIN sys.all_columns AS c ON c.object_id = o.object_id
        LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = c.object_id and cc.column_id = c.column_id
        LEFT OUTER JOIN sys.types AS t ON t.user_type_id = c.user_type_id
        LEFT OUTER JOIN sys.objects AS d ON d.object_id = c.default_object_id
        LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = c.object_id and ic.column_id = c.column_id
        LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = c.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = c.system_type_id) and (baset.user_type_id = c.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
        LEFT OUTER JOIN sys.default_constraints AS dc ON dc.object_id = c.default_object_id
        where s.name = '{0}'
        and o.name = '{1}'
        and o.type = 'U'
        order by c.column_id", schema, tableName)
        let columns = this.CreateDataSet(query).Tables.Item(0)
        let keys:DataTable =
            if columns.Rows.Count > 1 then
                this.CreateDataSet(String.Format("exec sp_MStablekeys N'[{0}].[{1}]', null, 14", schema, tableName)).Tables.Item(0)
            else
                new DataTable()
        let checks:DataTable =
            if columns.Rows.Count > 1 then
                this.CreateDataSet(String.Format("exec sp_MStablechecks N'[{0}].[{1}]'", schema, tableName)).Tables.Item(0)
            else
                new DataTable()
        let _ = columns.Columns.Add("is_node", typeof<string>)
        let _ = columns.Columns.Add("is_edge", typeof<string>)
        if this._serverVersion > 13 then
            let ds = this.CreateDataSet(String.Format("select is_node, is_edge from sys.tables as t inner join sys.schemas as s on t.schema_id = s.schema_id where s.name = '{0}' and t.name = '{1}'", schema, tableName))
            columns.Rows.Item(0).Item("is_node") <- ds.Tables.Item(0).Rows.Item(0).Item("is_node")
            columns.Rows.Item(0).Item("is_edge") <- ds.Tables.Item(0).Rows.Item(0).Item("is_edge")
        [columns; keys; checks]

    override this.GetTextColumns(name:string, objtype:ObjectType) : string list =
        let parts = name.Split('.')
        let schema = parts.[0]
        let objName = parts.[1]
        let query = "select definition from sys.sql_modules c, sys.objects o, sys.schemas s where o.schema_id = s.schema_id and s.name = '" + schema + "' and o.name = '" + objName + "' and o.object_id = c.object_id"
        [this.CreateDataSet(query.ToString()).Tables.Item(0).Rows.Item(0).Item(0).ToString()]

    override this.GetNameByTextColumns(searchStr:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select s.name + '.' + o.name as name, o.type from sys.sql_modules c, sys.objects o, sys.schemas s where o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and definition like '%" + searchStr
            else
                "select s.name + '.' + o.name as name, o.type from sys.sql_modules c, sys.objects o, sys.schemas s where o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and lower(definition) like '%" + searchStr.ToLower()
        let cond = "%' and o.object_id = c.object_id and o.type in (null"
        let jobExists = List.exists(fun x -> x = ObjectType.JOB) types
        let ssrs = this.CreateDataSet("SELECT name FROM master.dbo.sysdatabases WHERE name = 'ReportServer'").Tables.Item(0).Rows.Count > 0
        let reportExists = ssrs && List.exists(fun x -> x = ObjectType.REPORT) types
        let ccExists = List.exists(fun x -> x = ObjectType.COMPUTED_COLUMN) types
        let rec loop n (acc:string list) =
            if n > 0 then
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) (",'P','X','PC'" :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) (",'TR','TA'" :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) (",'V'" :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) (",'AF','FS','FT','FN','TF','IF'" :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let jobcond = 
            if jobExists then 
                if caseSensitive then
                    " union all select name COLLATE DATABASE_DEFAULT, 'JOB' as type from msdb.dbo.sysjobs as j inner join msdb.dbo.sysjobsteps as s on s.job_id = j.job_id where command like '%" + searchStr + "%'" 
                else
                    " union all select name COLLATE DATABASE_DEFAULT, 'JOB' as type from msdb.dbo.sysjobs as j inner join msdb.dbo.sysjobsteps as s on s.job_id = j.job_id where lower(command) like '%" + searchStr.ToLower() + "%'" 
            else 
                ""
        let reportcond = 
            if reportExists then 
                if caseSensitive then
                    " union all select name COLLATE DATABASE_DEFAULT, 'REPORT' as type from ReportServer.dbo.Catalog where CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),Content))) like '%" + searchStr + "%'" 
                else
                    " union all select name COLLATE DATABASE_DEFAULT, 'REPORT' as type from ReportServer.dbo.Catalog where lower(CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),Content)))) like '%" + searchStr.ToLower() + "%'" 
            else 
                ""
        let cccond = 
            if ccExists then 
                if caseSensitive then
                    " union all select s.name + '.' + o.name as name, 'TABLE' as type from sys.computed_columns c, sys.objects o, sys.schemas s where c.object_id = o.object_id and o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and (definition like '%" + searchStr + "%' or c.name like '%" + searchStr + "%')" 
                else
                    " union all select s.name + '.' + o.name as name, 'TABLE' as type from sys.computed_columns c, sys.objects o, sys.schemas s where c.object_id = o.object_id and o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and (lower(definition) like '%" + searchStr.ToLower() + "%' or lower(c.name) like '%" + searchStr.ToLower() + "%')" 
            else 
                ""
        query + cond + String.Join("",loop types.Length []) + ")" + jobcond + reportcond + cccond + " order by type, name"

    override this.GenerateNameScript(search:string, types:ObjectType list, caseSensitive:bool) : string =
        let query =
            if caseSensitive then
                "select s.name + '.' + o.name as name, type from sys.objects o, sys.schemas s where o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and o.name like '%" + search
            else
                "select s.name + '.' + o.name as name, type from sys.objects o, sys.schemas s where o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and lower(o.name) like '%" + search.ToLower()
        let cond = "%' and (type is NULL"
        let indexExists = List.exists(fun x -> x = ObjectType.INDEX) types
        let typeExists = List.exists(fun x -> x = ObjectType.TYPE) types
        let jobExists = List.exists(fun x -> x = ObjectType.JOB) types
        let sqExists = List.exists(fun x -> x = ObjectType.SERVICE_QUEUE) types
        let ssrs = this.CreateDataSet("SELECT name FROM master.dbo.sysdatabases WHERE name = 'ReportServer'").Tables.Item(0).Rows.Count > 0
        let reportExists = ssrs && List.exists(fun x -> x = ObjectType.REPORT) types
        let rec loop n (acc:string list) =
            if n > 0 then
                if types.Item(n - 1) = ObjectType.PROCEDURE then
                    loop (n - 1) (" or type = 'P' or type = 'X' or type = 'PC'" :: acc)
                elif types.Item(n - 1) = ObjectType.TRIGGER then
                    loop (n - 1) (" or type = 'TR' or type = 'TA'" :: acc)
                elif types.Item(n - 1) = ObjectType.VIEW then
                    loop (n - 1) (" or type = 'V'" :: acc)
                elif types.Item(n - 1) = ObjectType.FUNCTION then
                    loop (n - 1) (" or type = 'AF' or type = 'FS' or type = 'FT' or type = 'FN' or type = 'TF' or type = 'IF'" :: acc)
                elif types.Item(n - 1) = ObjectType.TABLE then
                    loop (n - 1) (" or type = 'U'" :: acc)
                elif types.Item(n - 1) = ObjectType.FOREIGN_KEY then
                    loop (n - 1) (" or type = 'FK' or type = 'F'" :: acc)
                elif types.Item(n - 1) = ObjectType.PRIMARY_KEY then
                    loop (n - 1) (" or type = 'PK'" :: acc)
                elif types.Item(n - 1) = ObjectType.INDEX then
                    loop (n - 1) (" or type = 'UQ'" :: acc)
                elif types.Item(n - 1) = ObjectType.SEQUENCE then
                    loop (n - 1) (" or type = 'SO'" :: acc)
                elif types.Item(n - 1) = ObjectType.SYNONYM then
                    loop (n - 1) (" or type = 'SN'" :: acc)
                elif types.Item(n - 1) = ObjectType.SERVICE_QUEUE then
                    loop (n - 1) (" or type = 'SQ'" :: acc)
                else
                    loop (n - 1) acc
            else
                acc
        let indices =
            if indexExists then
                if caseSensitive then
                    String.Format(" union all select s.name + '.' + i.name as name, 'INDEX' as type from sys.indexes as i, sys.objects as o, sys.schemas as s where o.object_id = i.object_id and o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and o.type <> 'S' and i.name like '%{0}%' and is_primary_key = 0 and not exists (select null from sys.objects where name = i.name and (type = 'FK' or type = 'F'))", search)
                else
                    String.Format(" union all select s.name + '.' + i.name as name, 'INDEX' as type from sys.indexes as i, sys.objects as o, sys.schemas as s where o.object_id = i.object_id and o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and o.type <> 'S' and lower(i.name) like '%{0}%' and is_primary_key = 0 and not exists (select null from sys.objects where name = i.name and (type = 'FK' or type = 'F'))", search.ToLower())
            else ""
        let tps =
            if typeExists then
                if caseSensitive then
                    String.Format(" union all select s.name + '.' + t.name as name, 'TYPE' as type from sys.types t, sys.schemas s where t.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and is_user_defined = 1 and t.name like '%{0}%'", search)
                else
                    String.Format(" union all select s.name + '.' + t.name as name, 'TYPE' as type from sys.types t, sys.schemas s where t.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and is_user_defined = 1 and lower(t.name) like '%{0}%'", search.ToLower())
            else ""
        let jobs =
            if jobExists then
                if caseSensitive then
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'JOB' as type from msdb.dbo.sysjobs where name like '%{0}%'", search)
                else
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'JOB' as type from msdb.dbo.sysjobs where lower(name) like '%{0}%'", search.ToLower())
            else ""
        let reports =
            if reportExists then
                if caseSensitive then
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'REPORT' as type from ReportServer.dbo.Catalog where name like '%{0}%' and content is not null and type = 2", search)
                else
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'REPORT' as type from ReportServer.dbo.Catalog where lower(name) like '%{0}%' and content is not null and type = 2", search.ToLower())
            else ""
        let serviceQueues =
            if sqExists then
                if caseSensitive then
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE' as type from sys.services where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_MESSAGE_TYPE' as type from sys.service_message_types where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_CONTRACT' as type from sys.service_contracts where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'ROUTE' as type from sys.routes where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'REMOTE_SERVICE_BINDING' as type from sys.remote_service_bindings where name like '%{0}%'", search) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_BROKER_PRIORITY' as type from sys.conversation_priorities where name like '%{0}%'", search)
                else
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE' as type from sys.services where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_MESSAGE_TYPE' as type from sys.service_message_types where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_CONTRACT' as type from sys.service_contracts where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'ROUTE' as type from sys.routes where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'REMOTE_SERVICE_BINDING' as type from sys.remote_service_bindings where lower(name) like '%{0}%'", search.ToLower()) +
                    String.Format(" union all select name COLLATE DATABASE_DEFAULT, 'SERVICE_BROKER_PRIORITY' as type from sys.conversation_priorities where lower(name) like '%{0}%'", search.ToLower())
            else ""
        query + cond + String.Join("",loop types.Length []) + ")" + indices + tps + jobs + reports + serviceQueues + " order by type, name"

    override this.GenerateSearchScript(search:string, caseSensitive:bool) : string =
        let query =
            if this._serverVersion > 13 then
                "select distinct s.name + '.' + o.name as name, o.type from sys.columns c, sys.objects o, sys.schemas s where c.graph_type is null and o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and c.object_id = o.object_id and (o.type = 'U' or o.type = 'PK' or o.type = 'F' or o.type = 'FK' or o.type = 'UQ')"
            else
                "select distinct s.name + '.' + o.name as name, o.type from sys.columns c, sys.objects o, sys.schemas s where o.schema_id = s.schema_id and s.name not in (" + system_schemas + ") and c.object_id = o.object_id and (o.type = 'U' or o.type = 'PK' or o.type = 'F' or o.type = 'FK' or o.type = 'UQ')"
        let cond =
            if caseSensitive then
                " and c.name like '%" + search
            else
                " and lower(c.name) like '%" + search.ToLower()
        query + cond + "%' order by 2, 1"

    override this.GetForeignKeyDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let dataSet = this.CreateDataSet(String.Format("select o2.name from sys.objects as o1, sysconstraints as c, sys.objects as o2, sys.schemas as s where s.schema_id = o1.schema_id and o1.object_id = c.constid and c.id = o2.object_id and s.name = '{0}' and o1.name = '{1}'", schema, name))
        let tableName = dataSet.Tables.Item(0).Rows.Item(0).Item(0).ToString().Trim()
        let row = this.CreateDataSet(String.Format("exec sp_MStablekeys @keyname='[{0}].[{1}]'", schema, name)).Tables.Item(0).Rows.Item(0)
        let count = row.Item("cColCount") :?> int
        let rec loop (colkind:string) n acc =
            if n > 0 then
                let item = row.Item(colkind + n.ToString()).ToString() + if n < count then "," else ""
                loop colkind (n - 1) (item :: acc)
            else
                acc
        let keyCols = loop "cKeyCol" count []
        let refCols = loop "cRefCol" count []
        let keyColsStr = keyCols.ToString().Replace("[", "").Replace("]", "").Replace(";", "")
        let refColsStr = refCols.ToString().Replace("[", "").Replace("]", "").Replace(";", "")
        "ALTER TABLE " + schema + "." + tableName + " ADD CONSTRAINT " + name + " FOREIGN KEY (" + keyColsStr + ") REFERENCES " + row.Item("cRefTable").ToString().Trim() + " (" + refColsStr + ")"

    override this.GetPrimaryKeyDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select o.name from sys.objects as o, sys.indexes as i, sys.schemas as s where s.schema_id = o.schema_id and i.object_id = o.object_id and s.name = '{0}' and i.name = '{1}'", schema, name)
        let tableName = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0).Item(0).ToString().Trim()
        let constraints = this.CreateDataSet(String.Format("exec sp_helpindex '[{0}].[{1}]'", schema, tableName)).Tables.Item(0).Rows
        let count = constraints.Count
        let rec loop n =
            if n > 0 then
                let index_name = constraints.Item(n - 1).Item("index_name").ToString().Trim()
                if index_name = name then
                    constraints.Item(n - 1).Item("index_keys").ToString().Trim()
                else
                    loop (n - 1)
            else
                null
        let columns = loop count
        "ALTER TABLE " + schema + "." + tableName + " ADD CONSTRAINT " + name + " PRIMARY KEY (" + columns + ")"

    override this.GetIndexDefinition(constraintName:string) : string =
        let parts = constraintName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select o.object_id, o.name, i.type, i.type_desc, i.is_unique, x.xml_index_type, x.xml_index_type_description
            from sys.objects as o
            inner join sys.schemas as s on s.schema_id = o.schema_id
            inner join sys.tables  as t on t.schema_id = s.schema_id
            inner join sys.indexes as i on i.object_id = o.object_id
            left join  sys.xml_indexes as x on x.object_id = i.object_id and x.index_id = i.index_id
            where s.name = '{0}' and i.name = '{1}'", schema, name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let tableName = row.Item("name").ToString().Trim()
        let indexType = row.Item("type") :?> byte
        let index_desc =
            if indexType <> 3uy then
                (if (row.Item("is_unique") :?> bool) then "UNIQUE "  else "") + row.Item("type_desc").ToString().Trim()
            else
                let xmlIndexType = row.Item("xml_index_type") :?> byte
                if xmlIndexType = 0uy then "PRIMARY XML" elif xmlIndexType = 2uy then "SELECTIVE XML" else "XML"
        let script = String.Format("SELECT
            (case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
            clmns.name AS column_name
            FROM sys.tables AS tbl
            INNER JOIN sys.indexes AS i ON i.index_id > 0 and i.is_hypothetical = 0 and i.object_id = tbl.object_id
            INNER JOIN sys.index_columns AS ic ON ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0) and ic.index_id = CAST(i.index_id AS int) and ic.object_id = i.object_id
            INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
            WHERE SCHEMA_NAME(tbl.schema_id) = '{0}' and tbl.name = '{1}' and i.name = '{2}'
            ORDER BY [ID]", schema, tableName, name)
        let constraints = this.CreateDataSet(script).Tables.Item(0).Rows
        let count = constraints.Count
        let rec loop n acc =
            if n > 0 then
                let column_name = constraints.Item(n - 1).Item("column_name").ToString().Trim()
                loop (n - 1) (column_name :: acc)
            else
                acc
        let columns = loop count []
        let spatial_index_desc =
            if indexType <> 4uy then
                ""
            else
                let spatialIndexScript = String.Format("SELECT si.tessellation_scheme, si.cells_per_object,
                    si.bounding_box_xmin, si.bounding_box_ymin, si.bounding_box_xmax, si.bounding_box_ymax,
                    si.level_1_grid_desc, si.level_2_grid_desc, si.level_3_grid_desc, si.level_4_grid_desc
                    FROM sys.tables AS tbl
                    INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
                    LEFT OUTER JOIN sys.spatial_indexes AS spi ON i.object_id = spi.object_id and i.index_id = spi.index_id
                    LEFT OUTER JOIN sys.spatial_index_tessellations as si ON i.object_id = si.object_id and i.index_id = si.index_id
                    WHERE SCHEMA_NAME(tbl.schema_id)='{0}' and tbl.name='{1}' and i.name='{2}'", schema, tableName, name)
                let data = this.CreateDataSet(spatialIndexScript).Tables.Item(0).Rows.Item(0)
                let tessellation_scheme = data.Item("tessellation_scheme").ToString().Trim()
                let bounding_box_xmin = data.Item("bounding_box_xmin")
                let bounding_box_ymin = data.Item("bounding_box_ymin")
                let bounding_box_xmax = data.Item("bounding_box_xmax")
                let bounding_box_ymax = data.Item("bounding_box_ymax")
                let level_1_grid_desc = data.Item("level_1_grid_desc").ToString().Trim()
                let level_2_grid_desc = data.Item("level_2_grid_desc").ToString().Trim()
                let level_3_grid_desc = data.Item("level_3_grid_desc").ToString().Trim()
                let level_4_grid_desc = data.Item("level_4_grid_desc").ToString().Trim()
                let grids =
                    if level_1_grid_desc <> "" then
                        String.Format("\nGRIDS = (LEVEL_1 = {0}, LEVEL_2 = {1}, LEVEL_3 = {2}, LEVEL_4 = {3}), ", level_1_grid_desc, level_2_grid_desc, level_3_grid_desc, level_4_grid_desc)
                    else
                        ""
                let cells_per_object = data.Item("cells_per_object")
                String.Format("\nUSING {0} WITH (BOUNDING_BOX = ({1}, {2}, {3}, {4}), {5}\nCELLS_PER_OBJECT = {6})",
                    tessellation_scheme, bounding_box_xmin, bounding_box_ymin, bounding_box_xmax, bounding_box_ymax, grids, cells_per_object)
        let xml_index_desc =
            if indexType <> 3uy then
                ""
            else
                let xmlIndexType = row.Item("xml_index_type") :?> byte
                if xmlIndexType = 1uy then
                    let xmlScript = String.Format("SELECT xi2.name AS [ParentXmlIndex]
                        FROM sys.tables AS tbl
                        INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
                        LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id
                        LEFT OUTER JOIN sys.xml_indexes AS xi2 ON xi2.object_id = xi.object_id AND xi2.index_id = xi.using_xml_index_id
                        WHERE SCHEMA_NAME(tbl.schema_id)='{0}' and tbl.name='{1}' and i.name='{2}'", schema, tableName, name)
                    String.Format("\nUSING XML INDEX {0} FOR PATH", this.CreateDataSet(xmlScript).Tables.Item(0).Rows.Item(0).Item("ParentXmlIndex").ToString().Trim())
                elif xmlIndexType = 2uy then
                    let xmlScript = String.Format("SELECT usrt.name AS [DataType],
                        CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND xmlTable.max_length <> -1 THEN xmlTable.max_length/2 ELSE xmlTable.max_length END AS int) AS [Length],
                        CAST(xmlTable.precision AS int) AS [NumericPrecision],
                        CAST(xmlTable.scale AS int) AS [NumericScale],
                        xmlTable.name AS [Name],
                        xmlTable.path_id AS [ID],
                        xmlTable.path AS [Path],
                        CAST(xmlTable.path_type AS tinyint) AS [PathType],
                        xmlTable.path_type_desc AS [PathTypeDesc],
                        xmlTable.xquery_type_description  AS [XQueryTypeDescription],
                        xmlTable.xquery_max_length AS [XQueryMaxLength],
                        xmlTable.is_node AS [IsNode],
                        xmlTable.is_singleton  AS [IsSingleton]
                        FROM sys.tables AS tbl
                        INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
                        INNER JOIN sys.selective_xml_index_paths as xmlTable ON xmlTable.index_id=CAST(i.index_id AS int) AND xmlTable.object_id=i.object_id
                        LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = xmlTable.user_type_id
                        LEFT OUTER JOIN sys.schemas AS s1xmlTable ON s1xmlTable.schema_id = usrt.schema_id
                        LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = xmlTable.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = xmlTable.system_type_id) and (baset.user_type_id = xmlTable.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1))
                        WHERE SCHEMA_NAME(tbl.schema_id)='{0}' and tbl.name='{1}' and i.name='{2}' ORDER BY [ID]", schema, tableName, name)
                    let namespaces = this.CreateDataSet(String.Format("SELECT uri, prefix FROM sys.selective_xml_index_namespaces WHERE object_id = {0}", row.Item("object_id"))).Tables.Item(0).Rows
                    let nsDesc =
                        if namespaces.Count = 0 then
                            ""
                        else
                            let rec loop n acc =
                                if n > 0 then
                                    let uri = namespaces.Item(n - 1).Item("uri").ToString().Trim()
                                    let prefix = namespaces.Item(n - 1).Item("prefix").ToString().Trim()
                                    let item  = String.Format("'{0}' as {1}", uri, prefix)
                                    loop (n - 1) (item :: acc)
                                else
                                    acc
                            "\nWITH XMLNAMESPACES\n(\n" + String.Join(",\n", loop namespaces.Count []) + "\n)"
                    let rows = this.CreateDataSet(xmlScript).Tables.Item(0).Rows
                    let count = rows.Count
                    let rec loop n acc =
                        if n > 0 then
                            let name = rows.Item(n - 1).Item("Name").ToString().Trim()
                            let path = rows.Item(n - 1).Item("Path").ToString().Trim()
                            let pathTypeDesc = rows.Item(n - 1).Item("PathTypeDesc").ToString().Trim()
                            let dataType = rows.Item(n - 1).Item("DataType").ToString().Trim()
                            let length = rows.Item(n - 1).Item("Length").ToString().Trim()
                            let precision = rows.Item(n - 1).Item("NumericPrecision").ToString().Trim()
                            let scale = rows.Item(n - 1).Item("NumericScale").ToString().Trim()
                            let xqTypeDesc = rows.Item(n - 1).Item("XQueryTypeDescription").ToString().Trim()
                            let xqMaxLength = rows.Item(n - 1).Item("XQueryMaxLength").ToString().Trim()
                            let isNode = rows.Item(n - 1).Item("IsNode") :?> bool
                            let isSingleton = rows.Item(n - 1).Item("IsSingleton") :?> bool
                            let typedef =
                                match dataType with
                                | "" -> ""
                                | "char" | "varchar"| "nchar" | "nvarchar" | "binary" | "varbinary" ->
                                    dataType + "(" + length + ")"
                                | "decimal" | "numeric" ->
                                    dataType + "(" + precision + ", " + scale + ")"
                                | _ -> dataType
                            let xqtypedef =
                                if xqTypeDesc <> "" then String.Format("'{0}'", xqTypeDesc) + (if xqMaxLength <> "" then String.Format(" MAXLENGTH ({0})", xqMaxLength) else "") + (if isSingleton then " SINGLETON" else "")
                                elif isNode then "'node()'"
                                else ""
                            let item  = name + " = '" + path + "' as " + pathTypeDesc + " " + typedef + xqtypedef
                            loop (n - 1) (item :: acc)
                        else
                            acc
                    let paths = loop count []
                    nsDesc + String.Format("\nFOR\n(\n{0}\n)", String.Join(",\n", paths))
                elif xmlIndexType = 3uy then
                    let xmlScript = String.Format("SELECT xi2.name AS [ParentXmlIndex], indexedpaths.name AS [IndexedXmlPathName]
                        FROM sys.tables AS tbl
                        INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
                        LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id
                        LEFT OUTER JOIN sys.xml_indexes AS xi2 ON xi2.object_id = xi.object_id AND xi2.index_id = xi.using_xml_index_id
                        LEFT OUTER JOIN sys.selective_xml_index_paths AS indexedpaths ON xi.object_id = indexedpaths.object_id AND xi.using_xml_index_id = indexedpaths.index_id AND xi.path_id = indexedpaths.path_id
                        WHERE SCHEMA_NAME(tbl.schema_id)='{0}' and tbl.name='{1}' and i.name='{2}'", schema, tableName, name)
                    let row = this.CreateDataSet(xmlScript).Tables.Item(0).Rows.Item(0)
                    String.Format("\nUSING XML INDEX {0} FOR ({1})", row.Item("ParentXmlIndex").ToString().Trim(), row.Item("IndexedXmlPathName").ToString().Trim())
                else
                    ""
        "CREATE " + index_desc + " INDEX " + name + " ON " + schema + "." + tableName + " (" + String.Join(",", columns) + ")" + spatial_index_desc + xml_index_desc

    override this.GetSequenceDefinition(sequenceName:string) : string =
        let parts = sequenceName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select start_value,increment,minimum_value,maximum_value,precision,scale,is_cached from sys.sequences s, sys.schemas u where s.schema_id = u.schema_id and u.name = '" + schema + "' and s.name='{0}'", name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let part1 = "CREATE SEQUENCE " + sequenceName + " AS [numeric] (" + row.Item("precision").ToString() + "," + row.Item("scale").ToString() + ") START WITH " + row.Item("start_value").ToString()
        let part2 = " INCREMENT BY " + row.Item("increment").ToString() + " MINVALUE " + row.Item("minimum_value").ToString() + " MAXVALUE " + row.Item("maximum_value").ToString()
        let part3 = if (row.Item("is_cached") :?> bool) = false then " NO CACHE" else ""
        part1 + part2 + part3

    override this.GetSynonymDefinition(synonymName:string) : string =
        let parts = synonymName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("select s.name, base_object_name from sys.synonyms s, sys.schemas u where s.schema_id = u.schema_id and u.name = '" + schema + "' and s.name='{0}'", name)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        "CREATE SYNONYM " + synonymName + " FOR " + row.Item("base_object_name").ToString()

    override this.GetTypeDefinition(typeName:string) : string =
        let parts = typeName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let row = this.CreateDataSet(String.Format("select * from sys.schemas s inner join sys.types t on t.schema_id = s.schema_id where s.name = '{0}' and t.name = '{1}'", schema, name)).Tables.Item(0).Rows.Item(0)
        let is_assembly_type = row.Item("is_assembly_type") :?> bool
        let is_table_type = if row.Table.Columns.Contains("is_table_type") then row.Item("is_table_type") :?> bool else false
        if is_table_type then
            let query = String.Format("SELECT
                clmns.name AS col_name,
                CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS col_len,
                CAST(clmns.precision AS int) AS col_prec,
                CAST(clmns.scale AS int) AS col_scale,
                clmns.is_nullable AS col_null,
                ISNULL(baset.name, N'') AS col_typename,
                CAST(ISNULL(cik.index_column_id, 0) AS bit) AS [InPrimaryKey],
                clmns.is_identity AS col_identity,
                CAST(ISNULL(ic.seed_value,0) AS bigint) AS col_seed,
                CAST(ISNULL(ic.increment_value,0) AS bigint) AS col_increment
                FROM sys.table_types AS tt
                INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
                INNER JOIN sys.schemas AS sc ON sc.schema_id = tt.schema_id
                LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
                LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column
                LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
                LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
                WHERE sc.name = '{0}' AND tt.name = '{1}'", schema, name)
            let columns = this.CreateDataSet(query).Tables.Item(0)
            this.ComposeTableType(typeName, columns)
        elif is_assembly_type then
            let query = String.Format("SELECT
                atypes.name AS name,
                asmbl.name AS AssemblyName,
                ISNULL(atypes.assembly_class,N'') AS ClassName
                FROM sys.assembly_types AS atypes
                INNER JOIN sys.assemblies AS asmbl ON (asmbl.assembly_id = atypes.assembly_id) and (atypes.is_user_defined = 1)
                INNER JOIN sys.schemas AS satypes ON satypes.schema_id = atypes.schema_id
                WHERE satypes.name = '{0}' and atypes.name = '{1}'", schema, name)
            if this.CreateDataSet(query).Tables.Item(0).Rows.Count > 0 then
                let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
                let className = if String.IsNullOrWhiteSpace(row.Item("ClassName").ToString()) then "" else ".[" + row.Item("ClassName").ToString() + "]"
                "CREATE TYPE " + typeName + Environment.NewLine + "EXTERNAL NAME [" + row.Item("AssemblyName").ToString() + "]" + className
            else ""
        else
            let query = String.Format("SELECT
                st.name AS col_name,
                CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND st.max_length <> -1 THEN st.max_length/2 ELSE st.max_length END AS int) AS col_len,
                CAST(st.precision AS int) AS col_prec,
                CAST(st.scale AS int) AS col_scale,
                st.is_nullable AS col_null,
                baset.name AS col_typename
                FROM sys.types AS st
                INNER JOIN sys.schemas AS sst ON sst.schema_id = st.schema_id
                LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = st.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = st.system_type_id) and (baset.user_type_id = st.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
                WHERE
                (st.schema_id!=4 and st.system_type_id!=240 and st.user_type_id != st.system_type_id) and sst.name = '{0}' and st.name = '{1}'", schema, name)
            if this.CreateDataSet(query).Tables.Item(0).Rows.Count > 0 then
                let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
                let rtn = row.Item("col_typename").ToString()
                let col_len = row.Item("col_len").ToString().Trim()
                let typedef =
                    match rtn.ToLower() with
                    | "char" | "varchar"| "nchar" | "nvarchar" | "binary" | "varbinary" ->
                        " (" + (if col_len <> "-1" then col_len else "max") + ") "
                    | "decimal" | "numeric" ->
                        " (" + row.Item("col_prec").ToString().Trim() + ", " + row.Item("col_scale").ToString().Trim() + ") "
                    | _ -> ""
                let nulls = if row.Item("col_null") :?> bool then " NULL" else " NOT NULL"
                "CREATE " + typeName + " FROM " + row.Item("col_typename").ToString() + typedef.Replace("-1", "max") + nulls
            else ""

    override this.GetJobDefinition(jobName:string) : string =
        let query = String.Format("select job_id from msdb.dbo.sysjobs where name='{0}'", jobName)
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let query2 = String.Format("select step_name, subsystem, command from msdb.dbo.sysjobsteps where job_id = '{0}'", row.Item("job_id"))
        let rows = this.CreateDataSet(query2).Tables.Item(0).Rows
        let rec loop n acc =
            if n > 0 then
                let item = rows.Item(n - 1).Item("command").ToString()
                let stepName = "STEP #" + n.ToString() + " - " + rows.Item(n - 1).Item("step_name").ToString() + "(" + rows.Item(n - 1).Item("subsystem").ToString() + "):"
                let stepDescription = stepName + Environment.NewLine + Environment.NewLine + item + Environment.NewLine
                loop (n - 1) (stepDescription :: acc)
            else
                acc
        let steps = loop rows.Count []
        String.Join(Environment.NewLine, steps)

    override this.GetReportDefinition(reportName:string) : string =
        let xml = this.CreateDataSet(String.Format("select CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),Content))) from ReportServer.dbo.Catalog where name='{0}'", reportName)).Tables.Item(0).Rows.Item(0).Item(0).ToString()
        let xmlDoc = XmlDocument()
        xmlDoc.LoadXml(xml)
        let reportDefinition = xmlDoc.DocumentElement.GetAttribute("xmlns")
        let query = "WITH XMLNAMESPACES ( DEFAULT '" + reportDefinition + "', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ) SELECT DISTINCT CommandText = x.value('(Query/CommandText)[1]','VARCHAR(4000)') FROM ( SELECT CONVERT(XML,CONVERT(VARBINARY(MAX),content)) AS reportXML FROM ReportServer.dbo.Catalog WHERE content is not null and type = 2 and name = '" + reportName + "') a CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )"
        let rows = this.CreateDataSet(query).Tables.Item(0).Rows
        let rec loop n acc =
            if n > 0 then
                let str = n.ToString() + ". " + rows.Item(n - 1).Item(0).ToString() + Environment.NewLine
                loop (n - 1) (str :: acc)
            else
                acc
        "********** " + reportName + " **********" + Environment.NewLine + Environment.NewLine + String.Join(Environment.NewLine, loop rows.Count [])

    member this.GetParamInfo(udfName:string) : string =
        let parts = udfName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let paramInfo = this.CreateDataSet(String.Format("SELECT param.*, usrt.name AS [DataType], ISNULL(baset.name, N'') AS [SystemType],
            CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length]
            FROM sys.all_objects AS udf
            INNER JOIN sys.schemas AS s ON s.schema_id = udf.schema_id
            INNER JOIN sys.all_parameters AS param ON (param.is_output = 0) AND (param.object_id=udf.object_id)
            LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
            LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
            WHERE s.name='{0}' AND udf.name='{1}'", schema, name)).Tables.Item(0).Rows
        let rec loop n acc =
            if n > 0 then
                let readOnly = if paramInfo.Item(n - 1).Table.Columns.Contains("is_readonly") then paramInfo.Item(n - 1).Item("is_readonly").ToString() else ""
                let name = paramInfo.Item(n - 1).Item("name").ToString()
                let defaultValue = paramInfo.Item(n - 1).Item("default_value").ToString()
                let hasDefaultValue = paramInfo.Item(n - 1).Item("has_default_value").ToString()
                let dataType = paramInfo.Item(n - 1).Item("DataType").ToString()
                let length = paramInfo.Item(n - 1).Item("Length").ToString()
                let numericPrecision = paramInfo.Item(n - 1).Item("precision").ToString()
                let numericScale = paramInfo.Item(n - 1).Item("scale").ToString()
                let typeDesc = if dataType = "decimal" || dataType = "numeric" then "(" + numericPrecision + ", " + numericScale + ")" elif dataType = "char" || dataType = "nchar" || dataType = "varchar" || dataType = "nvarchar" || dataType = "binary" || dataType = "varbinary" then "(" + (if length = "-1" then "max" else length) + ")" else ""
                let addStr = (if hasDefaultValue = "1" then " DEFAULT " + defaultValue + " " else "") + (if readOnly = "1" then " READONLY " else "")
                let str = name + " " + dataType + typeDesc + addStr + if n = paramInfo.Count then "" else ","
                loop (n - 1) (str :: acc)
            else
                acc
        let parameters = loop paramInfo.Count []
        String.Join(Environment.NewLine, parameters)

    override this.GetExtendedProcedureDefinition(procName:string) : string =
        let parts = procName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("SELECT
            udf.name AS [Name],
            usrt.name AS [DataType],
            CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND ret_param.max_length <> -1 THEN ret_param.max_length/2 ELSE ret_param.max_length END AS int) AS [Length],
            CAST(ret_param.precision AS int) AS [NumericPrecision],
            CAST(ret_param.scale AS int) AS [NumericScale],
            case when amudf.object_id is null then N'' else asmbludf.name end AS [AssemblyName],
            case when amudf.object_id is null then N'' else amudf.assembly_class end AS [ClassName],
            case when amudf.object_id is null then N'' else amudf.assembly_method end AS [MethodName]
            FROM sys.all_objects AS udf
            INNER JOIN sys.schemas AS s ON s.schema_id = udf.schema_id
            LEFT OUTER JOIN sys.all_parameters AS ret_param ON ret_param.object_id = udf.object_id and ret_param.is_output = 1
            LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = ret_param.user_type_id
            LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = ret_param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = ret_param.system_type_id) and (baset.user_type_id = ret_param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
            LEFT OUTER JOIN sys.assembly_modules AS amudf ON amudf.object_id = udf.object_id
            LEFT OUTER JOIN sys.assemblies AS asmbludf ON asmbludf.assembly_id = amudf.assembly_id
            WHERE s.name='{0}' AND udf.name='{1}'", schema, name)
        let procInfo = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let paramInfo = this.GetParamInfo(procName)
        let dataType = procInfo.Item("DataType").ToString()
        let columns = this.CreateDataSet(String.Format("SELECT clmns.name AS col_name,
            usrt.name AS col_typename,
            CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS col_len,
            clmns.precision col_prec, clmns.scale col_scale, clmns.is_nullable col_null
            FROM sys.all_objects AS udf
            INNER JOIN sys.schemas AS s ON s.schema_id = udf.schema_id
            INNER JOIN sys.all_columns AS clmns ON clmns.object_id = udf.object_id
            LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
            LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
            WHERE s.name='{0}' AND udf.name='{1}'", schema, name)).Tables.Item(0)
        let assemblyName = procInfo.Item("AssemblyName").ToString()
        let className = procInfo.Item("ClassName").ToString()
        let methodName = procInfo.Item("MethodName").ToString()
        let length = procInfo.Item("Length").ToString()
        let numericPrecision = procInfo.Item("NumericPrecision").ToString()
        let numericScale = procInfo.Item("NumericScale").ToString()
        let typ = if String.IsNullOrWhiteSpace(methodName) then "AGGREGATE" else if String.IsNullOrWhiteSpace(dataType) && columns.Rows.Count = 0 then "PROCEDURE" else "FUNCTION"
        let typeDesc = if columns.Rows.Count = 0 then (if dataType = "decimal" || dataType = "numeric" then "(" + numericPrecision + ", " + numericScale + ")" elif dataType = "char" || dataType = "nchar" || dataType = "varchar" || dataType = "nvarchar" || dataType = "binary" || dataType = "varbinary" then "(" + (if length = "-1" then "max" else length) + ")" else "") else this.ComposeTableFunction(columns)
        let typeStr = if typ = "PROCEDURE" then "" else Environment.NewLine + "RETURNS " + dataType + " " + typeDesc
        let fullName = assemblyName + "." + className + if String.IsNullOrWhiteSpace(methodName) then "" else "." + methodName
        let paramStr = (if typ = "PROCEDURE" then "" else "(") + paramInfo + (if typ = "PROCEDURE" then "" else ")")
        "CREATE " + typ + " " + procName + Environment.NewLine + paramStr + typeStr + Environment.NewLine + (if typ = "AGGREGATE" then "" else "WITH EXECUTE AS CALLER AS ") + "EXTERNAL NAME " + fullName

    override this.GetExtendedTriggerDefinition(triggerName:string) : string =
        let parts = triggerName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = String.Format("SELECT
            tr.name AS [Name],
            tbl.name AS [TableName],
            case when amtr.object_id is null then N'' else asmbltr.name end AS [AssemblyName],
            case when amtr.object_id is null then N'' else amtr.assembly_class end AS [ClassName],
            case when amtr.object_id is null then N'' else amtr.assembly_method end AS [MethodName],
            trr.is_instead_of_trigger AS [InsteadOf],
            CAST(ISNULL(tei.object_id,0) AS bit) AS [Insert],
            CAST(ISNULL(teu.object_id,0) AS bit) AS [Update],
            CAST(ISNULL(ted.object_id,0) AS bit) AS [Delete]
            FROM sys.tables AS tbl
            INNER JOIN sys.objects AS tr ON tr.parent_object_id=tbl.object_id
            INNER JOIN sys.schemas AS s ON s.schema_id = tr.schema_id
            LEFT OUTER JOIN sys.assembly_modules AS amtr ON amtr.object_id = tr.object_id
            LEFT OUTER JOIN sys.assemblies AS asmbltr ON asmbltr.assembly_id = amtr.assembly_id
            INNER JOIN sys.triggers AS trr ON trr.object_id = tr.object_id
            LEFT OUTER JOIN sys.trigger_events AS tei ON tei.object_id = tr.object_id and tei.type=1
            LEFT OUTER JOIN sys.trigger_events AS teu ON teu.object_id = tr.object_id and teu.type=2
            LEFT OUTER JOIN sys.trigger_events AS ted ON ted.object_id = tr.object_id and ted.type=3
            WHERE s.name = '{0}' AND tr.name='{1}'", schema, name)
        let triggerInfo = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let part1 = "CREATE TRIGGER " + triggerName + " ON " + triggerInfo.Item("TableName").ToString() + " WITH EXECUTE AS CALLER "
        let part2 = if triggerInfo.Item("InsteadOf").ToString() = "1" then "INSTEAD OF " else "AFTER "
        let part3 = if triggerInfo.Item("Insert").ToString() = "True" then "INSERT" elif triggerInfo.Item("Update").ToString() = "True" then "UPDATE" elif triggerInfo.Item("Delete").ToString() = "True" then "DELETE" else "??????"
        let assemblyName = triggerInfo.Item("AssemblyName").ToString()
        let className = triggerInfo.Item("ClassName").ToString()
        let methodName = triggerInfo.Item("MethodName").ToString()
        let part4 = Environment.NewLine + "AS EXTERNAL NAME " + assemblyName + "." + className + "." + methodName
        part1 + part2 + part3 + part4

    member this.GetServiceMessageTypeDefinition(serviceMessageTypeName:string) : string =
        let query = "SELECT name, validation FROM sys.service_message_types WHERE name = '" + serviceMessageTypeName + "'"
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let v = row.Item("validation").ToString().Trim()
        "CREATE MESSAGE TYPE [" + serviceMessageTypeName + "] VALIDATION " + if v = "X" then "WELL_FORMED_XML" elif v = "E" then "EMPTY" else "NONE"

    member this.GetServiceContractDefinition(serviceContractName:string) : string =
        let query = "SELECT c.name, t.name as message_type_name, u.is_sent_by_initiator, u.is_sent_by_target FROM sys.service_contracts AS c INNER JOIN sys.service_contract_message_usages AS u ON u.service_contract_id = c.service_contract_id	INNER JOIN sys.service_message_types AS t ON t.message_type_id = u.message_type_id WHERE c.name = '" + serviceContractName + "'"
        let rows = this.CreateDataSet(query).Tables.Item(0).Rows
        let rec loop n acc =
            if n > 0 then
                let message_type_name = rows.Item(n - 1).Item("message_type_name").ToString().Trim()
                let is_sent_by_initiator = rows.Item(n - 1).Item("is_sent_by_initiator") :?> bool
                let is_sent_by_target = rows.Item(n - 1).Item("is_sent_by_target") :?> bool
                let sentBy = if is_sent_by_initiator && is_sent_by_target then "ANY" elif is_sent_by_initiator then "INITIATOR" else "TARGET"
                let str = message_type_name + " SENT BY " + sentBy
                loop (n - 1) (str :: acc)
            else
                acc
        let messages = loop rows.Count []
        "CREATE CONTRACT [" + serviceContractName + "] (" + String.Join("," + Environment.NewLine, messages) + ")"

    member this.GetServiceQueueDefinition(serviceQueueName:string) : string =
        let parts = serviceQueueName.Split('.')
        let schema = parts.[0]
        let name = parts.[1]
        let query = "SELECT q.name, max_readers, activation_procedure, u.name AS user_name, is_activation_enabled, is_receive_enabled, is_enqueue_enabled, is_retention_enabled, is_poison_message_handling_enabled FROM sys.service_queues AS q INNER JOIN sys.schemas AS s ON s.schema_id = q.schema_id LEFT OUTER JOIN sys.sysusers AS u ON u.uid = q.execute_as_principal_id WHERE s.name = '" + schema + "' AND q.name = '" + name + "'"
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let max_readers = row.Item("max_readers") :?> int16
        let activation_procedure = row.Item("activation_procedure").ToString().Trim()
        let user_name = row.Item("user_name")
        let is_activation_enabled = row.Item("is_activation_enabled") :?> bool
        let is_receive_enabled = row.Item("is_receive_enabled") :?> bool
        let is_enqueue_enabled = row.Item("is_enqueue_enabled") :?> bool
        let is_retention_enabled = row.Item("is_retention_enabled") :?> bool
        let is_poison_message_handling_enabled = row.Item("is_poison_message_handling_enabled") :?> bool
        let activation = if user_name :? DBNull then "" else ", ACTIVATION (STATUS = " + (if is_activation_enabled then "ON" else "OFF") + ", PROCEDURE_NAME = " + activation_procedure + ", MAX_QUEUE_READERS = " + max_readers.ToString() + ", EXECUTE AS N'" + user_name.ToString().Trim() + "')"
        "CREATE QUEUE [" + serviceQueueName + "] WITH STATUS = " + (if is_receive_enabled || is_enqueue_enabled then "ON" else "OFF") + ", RETENTION = " + (if is_retention_enabled then "ON" else "OFF") + activation + ", POISON_MESSAGE_HANDLING (STATUS = " + (if is_poison_message_handling_enabled then "ON" else "OFF") + ")"

    member this.GetServiceDefinition(serviceName:string) : string =
        let query = "SELECT s.name, q.name AS queue_name, c.name AS contract_name FROM sys.services AS s INNER JOIN sys.service_queue_usages AS U ON u.service_id = s.service_id INNER JOIN sys.service_queues AS q ON q.object_id = u.service_queue_id LEFT OUTER JOIN sys.service_contract_usages AS cu ON cu.service_id = u.service_id LEFT OUTER JOIN sys.service_contracts AS c ON c.service_contract_id = cu.service_contract_id WHERE s.name = '" + serviceName + "'"
        let rows = this.CreateDataSet(query).Tables.Item(0).Rows
        let queue_name = rows.Item(0).Item("queue_name").ToString().Trim()
        let rec loop n acc =
            if n > 0 then
                let contract_name = rows.Item(n - 1).Item("contract_name").ToString().Trim()
                loop (n - 1) (contract_name :: acc)
            else
                acc
        let messages = loop rows.Count []
        "CREATE SERVICE [" + serviceName + "] ON QUEUE " + queue_name + if rows.Item(0).Item("contract_name") :? DBNull then "" else " (" + String.Join("," + Environment.NewLine, messages) + ")"

    member this.GetServiceRouteDefinition(serviceRouteName:string) : string =
        let query = "SELECT name, remote_service_name, broker_instance, datediff(second, getutcdate(), lifetime) AS lifetime, address, mirror_address FROM sys.routes WHERE name = '" + serviceRouteName + "'"
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let remote_service_name = row.Item("remote_service_name")
        let broker_instance = row.Item("broker_instance")
        let lifetime = row.Item("lifetime")
        let address = row.Item("address")
        let mirror_address = row.Item("mirror_address")
        let remote_service_name_str = (if remote_service_name :? DBNull then "" else "SERVICE_NAME = N'" + remote_service_name.ToString().Trim() + "', ")
        let broker_instance_str = (if broker_instance :? DBNull then "" else "BROKER_INSTANCE = N'" + broker_instance.ToString().Trim() + "', ")
        let lifetime_str = if row.Item("lifetime") :? DBNull then "" else "LIFETIME = " + lifetime.ToString() + ", "
        let address_str = (if address :? DBNull then "" else "ADDRESS = N'" + address.ToString().Trim() + "', ")
        let mirror_address_str = (if mirror_address :? DBNull then "" else "MIRROR_ADDRESS = N'" + mirror_address.ToString().Trim() + "'")
        "CREATE ROUTE [" + serviceRouteName + "] WITH " + remote_service_name_str + broker_instance_str + lifetime_str + address_str + mirror_address_str

    member this.GetRemoteServiceBinding(remoteServiceBindingName:string) : string =
        let query = "SELECT b.name, u.name AS user_name, remote_service_name, is_anonymous_on FROM sys.remote_service_bindings AS b INNER JOIN sys.sysusers AS u ON u.uid = b.principal_id WHERE b.name = '" + remoteServiceBindingName + "'"
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let remote_service_name = row.Item("remote_service_name").ToString().Trim()
        let user_name = row.Item("user_name").ToString().Trim()
        let is_anonymous_on = row.Item("is_anonymous_on") :?> bool
        "CREATE REMOTE SERVICE BINDING [" + remoteServiceBindingName + "] TO SERVICE N'" + remote_service_name + "' WITH USER = [" + user_name + "],  ANONYMOUS = " + if is_anonymous_on then "ON" else "OFF"

    member this.GetBrokerPriority(brokerPriorityName:string) : string =
        let query = "SELECT cp.name, c.name AS contract_name, s.name AS service_name, remote_service_name, priority FROM sys.conversation_priorities AS cp LEFT OUTER JOIN sys.service_contracts AS c ON c.service_contract_id = cp.service_contract_id LEFT OUTER JOIN sys.services AS s ON s.service_id = cp.local_service_id WHERE cp.name = '" + brokerPriorityName + "'"
        let row = this.CreateDataSet(query).Tables.Item(0).Rows.Item(0)
        let remote_service_name = row.Item("remote_service_name")
        let service_name = row.Item("service_name")
        let contract_name = row.Item("contract_name")
        let remote_service_name_str = (if remote_service_name :? DBNull then "" else "\n  REMOTE_SERVICE_NAME = N'" + remote_service_name.ToString().Trim() + "', ")
        let service_name_str = (if service_name :? DBNull then "" else "\n  LOCAL_SERVICE_NAME = N'" + service_name.ToString().Trim() + "', ")
        let contract_name_str = (if contract_name :? DBNull then "" else "\n  CONTRACT_NAME = N'" + contract_name.ToString().Trim() + "', ")
        let priority = row.Item("priority") :?> byte
        "CREATE BROKER PRIORITY [" + brokerPriorityName + "] FOR CONVERSATION SET(" + contract_name_str + service_name_str + remote_service_name_str + "\n  PRIORITY_LEVEL = " + priority.ToString() + ")"
