Saturday, July 16, 2011

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

Be very careful about the last, if you change it to, 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 When you try to reference those values, you are screwed.

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

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

        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
    includeSystemSP |> getAllStoredProcedureNames |> getSPParameters

No comments: