Saturday, July 16, 2011

How to get all the stored procedures in SQL Server in F#

Be very careful about the last List.map, if you change it to Seq.map, the lazy evaluation will catch you later on. Because the connection is closed when you actually leave this function, but the value is still on the server side if you use Seq.map. When you try to reference those values, you are screwed.

let getStoredProcedureParameters (connectionString, includeSystemSP) =
    use con = new SqlConnection (connectionString)      
    con.Open()

    let getSPParameters storedProcedureName =
        let cmd = con.CreateCommand()
        cmd.CommandType <- CommandType.StoredProcedure
        cmd.CommandText <- storedProcedureName

        SqlCommandBuilder.DeriveParameters(cmd);
        seq {
            for i in [0..cmd.Parameters.Count-1] do
            let p = cmd.Parameters.[i]
            yield (p.ParameterName, (p.DbType, p.IsNullable, p.Direction) )
        }

    let getAllStoredProcedureNames isIncludeSystemSP =                      
        let userSPNames =
            let query = @"SELECT * from SYS.PROCEDURES"
            use userSPCmd = con.CreateCommand()
            userSPCmd.CommandText <- query
            use reader = userSPCmd.ExecuteReader()
            [
                while reader.Read() do
                    yield reader.[0].ToString()
            ]
        if isIncludeSystemSP then
            let query = @"select name from sys.all_objects where is_ms_shipped = 1 and type = 'P' order by name"
            let cmd = con.CreateCommand()
            cmd.CommandText <- query;
            use reader = cmd.ExecuteReader()
            let result =
                [
                    while reader.Read() do
                        yield reader.[0].ToString()
                ]
            userSPNames @ result
        else
            userSPNames
   
    includeSystemSP |> getAllStoredProcedureNames |> List.map getSPParameters

No comments: